I can select the tables and fields, but when I click on Advanced the Check Box to Create the Commands is not available. they are dimmed out.
Why is this happening, is it a setting that I have missed. Any help is appreciated.
does anybody know where I can learn how to add, update, delete using SQL datasource control? I have a couple of books that shows me how to SELECT but nothing shows me how to update, add new record or delete with the SQL data source control. Even this website doesn't have any tutorial. Does any body know where I can read up on how to use all features of this control? Or where that have a couple of examples on how to use it?
View 5 Replies View RelatedI've just finished configuring the SELECT command for the SqlDataSource in my ASP.NET 2.0 web app. It works fine and runs against a SQL Server 2005 database, using a stored procedure that I've written.
So, then I went to configure the SqlDataSource for the UPDATE and INSERT commands, and I've written two SP's for those as well. In the designer the second form of the wizard asks for the Select statement. I've already given that for the SELECT statement in the third form, and I also select the INSERT tab to specify the SP I want to use for inserting data and the UPDATE tab to specify the SP I want to use for updating data. However, there appears to be no way that I can specify what the parameters are supposed to be for anything other than the SELECT command, through the designer. Is that correct, or have I missed something?
I have 2 Gridviews and a DetailsView for each GridView. The first Gridview and DetailsView work fine and I can Insert, Delete and Update the DetailsView just fine. However the second Gridview/DetailsView will only let me Insert but not Delete or Update. When I click on the "Delete" button it just ignores me. If I do an "Edit", when I try to click on the "Update" button it is ignored again and I have to click on "Cancel". I don't get any error messages...
Anyone have an idea what might be wrong?
Hello Guys,
I’m trying to create a sqlDataSource using the wizard, I can get the select statement, the here clause and the order by clause but when I click on the Advanced button the options to generate the insert, update and delete statements are not available. Does anyone know what am I doing wrong?
i have default.aspx file and i put SqlDataSource into my designer form.when i want to configure SqlDataSource it not allowed my to insert, update and delete only insert is allowed.what is the problem and the solution for this case ...thx
View 1 Replies View Relatedhi
i have form view that retrieves a single row
i dont want to use SQLDataSource default Select/Update/Insert/Delete buttons
i am using Stored Procedures
I want to have my own buttons, the select has parameters, how to retrieve data for that parameter?
Hi,
I'm reasonably new to ASP.NET 2.0
I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure. I also want the stored procedure to return an output value. I do not need to perform a select or a delete.
For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control. I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures).
I desperately need the syntax to:
a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need).
b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters).
c) syntax on how to set the values for these parameters (again...coming from form controls and session variables)
d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource.
If anybody has sample code or a link or two, I would be most appreciative.
Thank you in advance for any help!
I have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
View 2 Replies View RelatedHi,I'm new to ASP.NET and having a problem configuring the SqlDataSource control. I am using the standard ASP.NET 2.0 "aspnetdb" database to manage user accounts. The problem is this:When using the wizard to configure my SqlDataSource control, the option to auto-generate the Insert/Update/Delete SQL statements are grayed out. I've searched this forum and found that this can be a symptom of no primary keys in the tables. However, there are primary keys (UserId), which is the default schema as generated by asp.net (aspnet_regsql.exe). When I use the wizard, I make the following choices:How would you like to retrieve data from your database?-> Select "Specify columns from a table or view"-> Select the "vw_aspnet_MembershipUsers" view from the "Name:" drop-down list-> Select "UserId", "Email", "UserName" from "Columns:"After this, still no option to auto-generate I/U/D statements. Any thoughts on why this isn't working??? Thanks,Leah.
View 1 Replies View Relatedhi,
I have a dataadaptor which i use to fill a dataset. I can do this no problems. But if I delete a row from the dataset and update the adaptor again, nothing gets changed in my source database. Does anyone know why this is?
here's my code for my dataadaptor:
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("select * from tblExtRef", conn);
adapter.SelectCommand.Connection = conn;
SqlParameter parm;
SqlCommand cmd;
cmd = new SqlCommand("DELETE FROM tblextref WHERE extrefid = @extrefid",conn);
parm = cmd.Parameters.Add("@extrefid", SqlDbType.Int , 40, "extrefid");
parm.SourceColumn = "ExtRefid";
//parm.SourceVersion = DataRowVersion.Original;
//parm.Direction = ParameterDirection.InputOutput;
//SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand = cmd;
Ok, this seems really straightforward, but I'm completely lost. I created a sqldatasource control that uses an INSERT stored procedure. I can click on test it tests just fine, inserts everything into the database.Now, I want this sqldatasource control to fire when a button is clicked, but I have no idea how to do this, I looked at the parameters and cant find an execute or anything like that to use. Let me know how to execute this sqldatasource, it'd really help me out. I know you can do EVERYTHING in the code behind file, but I'm rather new to VB and I'm probably gonna save that for another day. Here's what I have now:basic sqldatasource : <asp:SqlDataSource ID="SqlProcDS" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>" InsertCommand="ins_test" InsertCommandType="StoredProcedure" SelectCommand="ins_test" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="a" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBox2" Name="b" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBox3" Name="c" PropertyName="Text" Type="String" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="a" Type="String" /> <asp:Parameter Name="b" Type="String" /> <asp:Parameter Name="c" Type="String" /> </InsertParameters> </asp:SqlDataSource>in the vb file : Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click 'Somehow execute sqlProcDS here?!End Sub
View 1 Replies View RelatedHi,
I need to UPDATE the IP Address of a newly created user into a table (the value is currently set to default - " Not Available"), and I really dont know the syntax required to do this. So far I've derived all the variables needed using the following code:
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)
MembershipUser _membershipUser = Membership.GetUser();
Guid UserId = (Guid)_membershipUser.ProviderUserKey;<--------------------------------------------------------------I can see the UserId here if I pause the prog
SqlDataSource customerDataSource = new SqlDataSource();
customerDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
customerDataSource.UpdateParameters.Add("IPAddress", Request.UserHostAddress.ToString());<---------------------------------------I can see the IPAddress here
customerDataSource.UpdateCommandType = SqlDataSourceCommandType.Text;
what next
I've not got a clue as to what to write next. There is a try / catch statement after this using :
rowsAffected = customerDataSource.Update();
which remains at 0 no matter what I try.
Any help greatly appreciated.
Hi, I am a newbie in using ASP.NET 2.0 and ADO.NET. I wrote a hangman game and want to record statistics at the end of each game. I will create and update records in the database for each authenticated user as well as a record for the Anonymous, unauthenticated user. After a win or loss has occurred, I want to programmatically use the SQLDataSource control to increment the statistics counters for the appropriate record in the database (note I don't want to show anything or get user input for this function).
I need a VB.NET codebehind example that will show me how I should set up the parameters and update the appropriate record in the database. Below is my code. What happens now is that the program chugs along happily (no errors), but the database record does not actually get updated. I have done many searches on this forum and on the general Internet for programmatic examples of an update sequence of code. If there is a tutorial for this online or a book, I'm happy to check it out.
Any help will be greatly appreciated.
Lambanlaa
CODE - Hangman.aspx.vb
1 Protected Sub UpdateStats()2 Dim playeridString As String3 Dim gamesplayedInteger, gameswonInteger, _4 easygamesplayedInteger, easygameswonInteger, _5 mediumgamesplayedInteger, mediumgameswonInteger, _6 hardgamesplayedInteger, hardgameswonInteger As Int327 8 ' determine whether player is named or anonymous9 If User.Identity.IsAuthenticated Then10 Profile.Item("hangmanplayeridString") = User.Identity.Name11 Else12 Profile.Item("hangmanplayeridString") = "Anonymous"13 End If14 15 playeridString = Profile.Item("hangmanplayeridString")16 17 ' look up record in stats database18 Dim hangmanstatsDataView As System.Data.DataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)19 20 gamesplayedInteger = 021 gameswonInteger = 022 easygamesplayedInteger = 023 easygameswonInteger = 024 mediumgamesplayedInteger = 025 mediumgameswonInteger = 026 hardgamesplayedInteger = 027 hardgameswonInteger = 028 29 If hangmanstatsDataView.Table.Rows.Count = 0 Then30 31 ' then create record with 0 values32 statsSqlDataSource.InsertParameters.Clear() ' don't really know what Clear does33 statsSqlDataSource.InsertParameters("playerid").DefaultValue = playeridString34 statsSqlDataSource.InsertParameters("GamesPlayed").DefaultValue = gamesplayedInteger35 statsSqlDataSource.InsertParameters("GamesWon").DefaultValue = gameswonInteger36 statsSqlDataSource.InsertParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger37 statsSqlDataSource.InsertParameters("EasyGamesWon").DefaultValue = easygameswonInteger38 statsSqlDataSource.InsertParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger39 statsSqlDataSource.InsertParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger40 statsSqlDataSource.InsertParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger41 statsSqlDataSource.InsertParameters("HardGamesWon").DefaultValue = hardgameswonInteger42 43 statsSqlDataSource.Insert()44 End If45 46 ' reread the record to get current values47 hangmanstatsDataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)48 Dim hangmanstatsDataRow As System.Data.DataRow = hangmanstatsDataView.Table.Rows.Item(0)49 50 ' set temp variables to database values51 gamesplayedInteger = hangmanstatsDataRow("GamesPlayed")52 gameswonInteger = hangmanstatsDataRow("GamesWon")53 easygamesplayedInteger = hangmanstatsDataRow("EasyGamesPlayed")54 easygameswonInteger = hangmanstatsDataRow("EasyGamesWon")55 mediumgamesplayedInteger = hangmanstatsDataRow("MediumGamesPlayed")56 mediumgameswonInteger = hangmanstatsDataRow("MediumGamesWon")57 hardgamesplayedInteger = hangmanstatsDataRow("HardGamesPlayed")58 hardgameswonInteger = hangmanstatsDataRow("HardGamesWon")59 60 ' update stats record61 'statsSqlDataSource.UpdateParameters.Clear()62 'statsSqlDataSource.UpdateParameters("playerid").DefaultValue = playeridString63 64 If Profile.Item("hangmanwinorloseString") = "win" Then65 66 statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 167 statsSqlDataSource.UpdateParameters("GamesWon").DefaultValue = gameswonInteger + 168 Select Case Profile.Item("hangmandifficultyInteger")69 Case 170 statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 171 statsSqlDataSource.UpdateParameters("EasyGamesWon").DefaultValue = easygameswonInteger + 172 Case 273 statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 174 statsSqlDataSource.UpdateParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger + 175 Case 376 statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 177 statsSqlDataSource.UpdateParameters("HardGamesWon").DefaultValue = hardgameswonInteger + 178 End Select79 80 81 ElseIf Profile.Item("hangmanwinorloseString") = "lose" Then82 83 statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 184 Select Case Profile.Item("hangmandifficultyInteger")85 Case 186 statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 187 Case 288 statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 189 Case 390 statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 191 End Select92 End If93 94 statsSqlDataSource.Update()95 96 End Sub97
CODE - Hangman.aspx 1 <asp:SqlDataSource ID="statsSqlDataSource" runat="server" ConflictDetection="overwritechanges"
2 ConnectionString="<%$ ConnectionStrings:lambanConnectionString %>" DeleteCommand="DELETE FROM [Hangman_Stats] WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon"
3 InsertCommand="INSERT INTO [Hangman_Stats] ([PlayerID], [GamesPlayed], [GamesWon], [EasyGamesPlayed], [EasyGamesWon], [MediumGamesPlayed], [MediumGamesWon], [HardGamesPlayed], [HardGamesWon]) VALUES (@PlayerID, @GamesPlayed, @GamesWon, @EasyGamesPlayed, @EasyGamesWon, @MediumGamesPlayed, @MediumGamesWon, @HardGamesPlayed, @HardGamesWon)"
4 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT PlayerID, GamesPlayed, GamesWon, EasyGamesPlayed, EasyGamesWon, MediumGamesPlayed, MediumGamesWon, HardGamesPlayed, HardGamesWon FROM Hangman_Stats WHERE (PlayerID = @playerid)"
5 UpdateCommand="UPDATE [Hangman_Stats] SET [GamesPlayed] = @GamesPlayed, [GamesWon] = @GamesWon, [EasyGamesPlayed] = @EasyGamesPlayed, [EasyGamesWon] = @EasyGamesWon, [MediumGamesPlayed] = @MediumGamesPlayed, [MediumGamesWon] = @MediumGamesWon, [HardGamesPlayed] = @HardGamesPlayed, [HardGamesWon] = @HardGamesWon WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon">
6 <DeleteParameters>
7 <asp:Parameter Name="original_PlayerID" Type="String" />
8 <asp:Parameter Name="original_GamesPlayed" Type="Int32" />
9 <asp:Parameter Name="original_GamesWon" Type="Int32" />
10 <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" />
11 <asp:Parameter Name="original_EasyGamesWon" Type="Int32" />
12 <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" />
13 <asp:Parameter Name="original_MediumGamesWon" Type="Int32" />
14 <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" />
15 <asp:Parameter Name="original_HardGamesWon" Type="Int32" />
16 </DeleteParameters>
17 <UpdateParameters>
18 <asp:Parameter Name="GamesPlayed" Type="Int32" />
19 <asp:Parameter Name="GamesWon" Type="Int32" />
20 <asp:Parameter Name="EasyGamesPlayed" Type="Int32" />
21 <asp:Parameter Name="EasyGamesWon" Type="Int32" />
22 <asp:Parameter Name="MediumGamesPlayed" Type="Int32" />
23 <asp:Parameter Name="MediumGamesWon" Type="Int32" />
24 <asp:Parameter Name="HardGamesPlayed" Type="Int32" />
25 <asp:Parameter Name="HardGamesWon" Type="Int32" />
26 <asp:Parameter Name="original_PlayerID" Type="String" />
27 <asp:Parameter Name="original_GamesPlayed" Type="Int32" />
28 <asp:Parameter Name="original_GamesWon" Type="Int32" />
29 <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" />
30 <asp:Parameter Name="original_EasyGamesWon" Type="Int32" />
31 <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" />
32 <asp:Parameter Name="original_MediumGamesWon" Type="Int32" />
33 <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" />
34 <asp:Parameter Name="original_HardGamesWon" Type="Int32" />
35 </UpdateParameters>
36 <InsertParameters>
37 <asp:Parameter Name="PlayerID" Type="String" />
38 <asp:Parameter Name="GamesPlayed" Type="Int32" />
39 <asp:Parameter Name="GamesWon" Type="Int32" />
40 <asp:Parameter Name="EasyGamesPlayed" Type="Int32" />
41 <asp:Parameter Name="EasyGamesWon" Type="Int32" />
42 <asp:Parameter Name="MediumGamesPlayed" Type="Int32" />
43 <asp:Parameter Name="MediumGamesWon" Type="Int32" />
44 <asp:Parameter Name="HardGamesPlayed" Type="Int32" />
45 <asp:Parameter Name="HardGamesWon" Type="Int32" />
46 </InsertParameters>
47 <SelectParameters>
48 <asp:ProfileParameter Name="playerid" PropertyName="hangmanplayeridString" />
49 </SelectParameters>
50 </asp:SqlDataSource>
Hi all,
I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:
<asp:GridView ID="GridEditSettlement" runat="server" AutoGenerateColumns="False" BackColor="Navy"
BorderColor="IndianRed" BorderStyle="Solid" Font-Names="Verdana" Font-Size="X-Small" DataSourceID="SqlDataSource_grid" AllowPaging="True" AllowSorting="True" ForeColor="White" DataKeyNames="legid">
<Columns>
<asp:CommandField ShowEditButton="True" CancelImageUrl="~/App_Graphics/quit.gif" CancelText="" EditImageUrl="~/App_Graphics/EditGrid.GIF" EditText="" UpdateImageUrl="~/App_Graphics/save.gif" UpdateText="" ButtonType="Image" />
<asp:BoundField DataField="StartDate" HeaderText="Start Date" ReadOnly="True" />
<asp:BoundField DataField="EndDate" HeaderText="End Date" ReadOnly="True" />
<asp:BoundField DataField="CounterpartDealRef" HeaderText="CP Deal Ref" ReadOnly="True" />
<asp:TemplateField HeaderText="Preliminary Settlement Price" ><ItemTemplate>
<asp:Label ID=lblPreliminary runat=server Text='<%# Bind("PrimarySettlementPrice") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtPrimaryPrice Text='<%# Bind("PrimarySettlementPrice") %>'></asp:TextBox>
</EditItemTemplate></asp:TemplateField>
<asp:TemplateField HeaderText="Agreed Settlement Price"><ItemTemplate>
<asp:Label ID=lblAgreed runat=server Text='<%# Bind("AgreedSettlementPrice") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtAgreedPrice Text='<%# Bind("AgreedSettlementPrice") %>'></asp:TextBox>
</EditItemTemplate></asp:TemplateField>
<asp:BoundField DataField="Volume" HeaderText="Volume" ReadOnly="True" />
<asp:BoundField DataField="Price" HeaderText="Price" ReadOnly="True" />
<asp:BoundField DataField="TotalVolume" HeaderText="Total Volume" ReadOnly="True" />
<asp:BoundField DataField="InstrumentName" HeaderText="Instrument" ReadOnly="True" />
<asp:BoundField DataField="NominalValue" HeaderText="Nominal Value" ReadOnly="True" />
<asp:BoundField DataField="Strike" HeaderText="Strike" ReadOnly="True" />
<asp:BoundField DataField="DeliveryDate" HeaderText="Delivery Date" ReadOnly="True" />
<asp:TemplateField HeaderText="LegId" SortExpression="LegId">
<ItemTemplate>
<asp:Label ID="lblLegID" runat="server" Text='<%# Bind("LegId") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtLegID Text='<%# Bind("LegId") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle BackColor="#FFFF66" ForeColor="#333333" />
<EditRowStyle BackColor="#FFFF66" Font-Names="Verdana" Font-Size="X-Small" ForeColor="#333333" />
<PagerStyle ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#333333" />
</asp:GridView>
<br />
<asp:SqlDataSource ID="SqlDataSource_grid" runat="server" ConnectionString="<%$ ConnectionStrings:DealCaptureDev %>" SelectCommand="sp_get_single_deal" SelectCommandType="StoredProcedure" UpdateCommand="Update trDealLeg Set PrimarySettlementPrice=@primarysettlement, AgreedSettlementprice=@agreedsettlement, LastUpdate=GetDate(), LastUpdateBy=Session('userid') Where LegID=@legid" EnableCaching="True" ConflictDetection="CompareAllValues" ProviderName="System.Data.SqlClient">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="" Name="dealnum" QueryStringField="deal"
Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:ControlParameter ControlID="txtLegId" PropertyName="Text" Name="legId" />
<asp:ControlParameter ControlID="txtPrimarySettlement" Name="primarysettlement" PropertyName="Text" />
<asp:ControlParameter ControlID="txtAgreedSettlement" Name="agreedsettlement" PropertyName="Text"/>
<asp:SessionParameter DefaultValue="" Name="userid" SessionField="userid" />
</UpdateParameters>
</asp:SqlDataSource>
As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work).
So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event.
What is the best approach? Anyone come up against this issue before?
Thanks,
KB
I'm referring to this bug, which I've seen a lot of people (including me) run into:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93937
Has anyone heard if MS plans to fix this soon?
Thanks
Hello,
I am trying to use the forms view control to do a simple web app. My issue is, I cant get the connection to automatically generate insert, update and delete statements. I tried to do this manually but I always get an error. I have read that I need all the keys selected--this still didnt work. I have about 6 tables picked in my view, and if I pick one of them then this advanced feature works. I have the primary keys selected.
here is my saved view. this is in SQL 2000.
SELECT TOP 100 PERCENT dbo.ADDRESS.EMAIL, dbo.ADDRESS.FIRST_NAME AS [first name], dbo.ADDRESS.LAST_NAME AS [last name], dbo.ADDRESS.STATE, dbo.ADDRESS.TEL1 AS phone, dbo.INVOICES.QUOTE_NO AS [SALES QUOTE], dbo.CUST.NAME AS Company, dbo.ITEMS.ITEMNO AS [Course Number], dbo.ITEMS.DESCRIPT AS S_Descript, dbo.ADDRESS.JOB_TITLE AS [Job Title], dbo.TRAINING_SCHEDULE.[MONTH], dbo.TRAINING_SCHEDULE.S_DATE, dbo.TRAINING_SCHEDULE.END_DATE, dbo.TRAINING_SCHEDULE.IS_CONFIRMED, dbo.TRAINING_SCHEDULE.IS_PAID, dbo.TRAINING_SCHEDULE.CUST_CODE AS Account, dbo.TRAINING_SCHEDULE.SCHEDULE_ID, dbo.CUST.CUST_CODE, dbo.INVOICES.INVOICES_ID, dbo.X_INVOIC.X_INVOICE_ID, dbo.ADDRESS.ADDR_CODEFROM dbo.TRAINING_SCHEDULE INNER JOIN dbo.CUST ON dbo.TRAINING_SCHEDULE.CUST_CODE = dbo.CUST.CUST_CODE RIGHT OUTER JOIN dbo.X_INVOIC RIGHT OUTER JOIN dbo.INVOICES ON dbo.X_INVOIC.ORDER_NO = dbo.INVOICES.DOC_NO LEFT OUTER JOIN dbo.ADDRESS ON dbo.INVOICES.CUST_CODE = dbo.ADDRESS.CUST_CODE LEFT OUTER JOIN dbo.ITEMS ON dbo.ITEMS.ITEMNO = dbo.X_INVOIC.ITEM_CODE ON dbo.CUST.CUST_CODE = dbo.ADDRESS.CUST_CODEWHERE (dbo.X_INVOIC.ITEM_CODE LIKE 'FOT-%') AND (dbo.X_INVOIC.STATUS = 7) AND (dbo.ADDRESS.TYPE IN (4, 5, 6)) AND (dbo.ADDRESS.EMAIL <> '') AND (dbo.ADDRESS.COUNTRY = 'UNITED STATES') AND (dbo.ITEMS.CATEGORY = 'TRAININGCLASSES') AND (dbo.TRAINING_SCHEDULE.CUST_CODE = 'joe')ORDER BY dbo.ADDRESS.STATE
I am new to this so I am not sure what to include.
thanks,
yellier
I've created a SSIS package with a DataReaderDestination and a SSRS report that points to it.
I referenced these links during development and I have everything working as expected locally. **I changed the config files as stated on both my local machine and our server. My config matches the example exactly.
http://msdn.microsoft.com/en-us/library/ms345250.aspx
http://msdn.microsoft.com/en-us/library/ms159215.aspx
When I attempt to deploy my report project to the server I get this error message:
"An attempt has been made to use a data extension 'SSIS' that is not registered for this report server."
In the report manager data source properties page it reads:
"The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly."
In SQL Server Management Studio data source properties page SSIS is not a data source type option.
I've tried restarting both the Reporting Services and Integration Services on the server to no avail.
Any suggests? My problem is I can't get the SSIS to be recognized source on the server.
Hi All,
I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient?
I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it.
We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on...
Thank you in advance!
Tatyana Hughes
I am trying to update a SQL database with data from a Wang system. The Wang data is dumped to a txt file. I then import it into an update table in SQL via Access. Some of the data is new and some of the data is updated records. At this point I have been trying to create a script to update and add data to a table via the query tool in SQL Then delete data from the update table.
I was able to get the UPDATE and DELETE to work but I have not figured out how to insert new records at the same time? Can I use an IF statement? I will apreciate any help or sample code, Thanks.
UPDATE MemberList
Set Name = NameUpd, Address1 = Address1Upd, Address2 = Address2Upd, City = CityUpd, State = StateUpd, ZipCode = ZipCodeUpd, MemberStatus = MemberstatusUpd
FROM MemberList, MemberListUpd
WHERE MemberList.MemberNumber = MemberListUpd.MemberNumberUpd
INSERT ?
DELETE MemberListUpd
Im trying to keep a mirror image of some data Im getting from Quickbooks.
As the records are inserted into the database I need to check if a record exists and either update or insert a new one.
it seems easier just to delete using the tnxid and reinsert vs updating
my question is if I go
begin
INSERT INTO QBInvoicesQue(100s of feilds)
end
begin
delete from QBInvoices where txnid = @TxnID
end
and there is not matching txnid to delete from will it cause any problems? before going to the insert statement?
begin
INSERT INTO QBInvoices(100s of feilds)
end
I vaguely recall reading an article that I can no longer find that an update statement is executed as a combination of a Delete and an Insert by SQL server. Does anyone know if this a still a true statement in SQL Server 2005?
Thanks,
-shl
for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...
View 2 Replies View RelatedI have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.
Hi,I just upgraded my ASP.NET 2.0 BETA 2 environment to the final release of ASP.NET 2.0 VWD.Once the update was finished, I could open my website without any problems..... Now, I noticed that in the final release, some modifications have been included in the Membership Stored Procedure and other stored procedures. So I created a new database (SQL Express) and added my data again.After re-creating my SQLDataSources, I tryed to enable the Editing and Deleting option in VWD and once I run my web application, it seems when selecting editing and then update, it doesn't work anymore....This is my code :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMMOASPNETDBConnectionString %>"
DeleteCommand="DELETE FROM aspnet_test WHERE (testID = @Original_testID)" SelectCommand="SELECT BuyID, BuyNL, BuyFR, Lastupdated FROM aspnet_Buy"
UpdateCommand="UPDATE aspnet_Buy SET BuyNL = @BuyNL, BuyFR = @BuyFR WHERE (BuyNL = @original_BuyID)">
<DeleteParameters>
<asp:Parameter Name="Original_testID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="BuyNL" />
<asp:Parameter Name="BuyFR" />
<asp:Parameter Name="original_BuyID" />
</UpdateParameters>
</asp:SqlDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataKeyNames="BuyID" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="BuyID" HeaderText="BuyID" InsertVisible="False" ReadOnly="True"
SortExpression="BuyID" />
<asp:BoundField DataField="BuyNL" HeaderText="BuyNL" SortExpression="BuyNL" />
<asp:BoundField DataField="BuyFR" HeaderText="BuyFR" SortExpression="BuyFR" />
<asp:BoundField DataField="Lastupdated" HeaderText="Lastupdated" SortExpression="Lastupdated" />
</Columns>
</asp:GridView>Can someone help me with this ? What is wrong with the Update command ?Thanks to all,Bart
I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc.
How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4.
Maybe I didn't explain it good enough for you, please tell me then!!
Thanks!!
I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.
DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?
PLEASE SUGGEST ME..ITS URGENT.
THANKS IN ADVANCE
HARISH
Hello,
What is faster / better for performance?
Check if something already excist and update the changed values? or delete the whole table and then insert everything?
Thanks!
SQL2K sp4
Howdy all. I opened a 200 mb. file in Query Analyzer that is full of Inserts/ Updates/ and Deletes. I tried just to parse it, and killed it after 18 hours. There is no blocking. All of the appropriate indexes exist. I even removed them and retried JIC. The box is plenty powerful for this task. Does anyone have any ideas?
I've tried several times with no luck. At the top of the file is SET IMPLICIT_TRANSACTIONS ON and then every 10,000 statements is COMMIT WORK. I've tried adjusting the number of commits to a lower number with no luck. This works fine on smaller files (3 - 20 mb).
How to Insert,Update and delete through script without using merge.I have simple requirement of Deleting,Inserting and Update from one table to other table...Here is the Sample Data
CREATE TABLE #Table1
(ID INT ,Name VARCHAR(30),DATEKEY INT)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (1,'Mohan',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (2,'Raj',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (3,'Majja',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (4,'Majjaa',20131231)
[code]...
So now i need to update 1st record and add another new record..So i need to update as well as delete the existing data in Target table.
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (5,'Macha',20131231)
My output should come like this one :
IDNameDATEKEY
1Mohan20131231
5Macha 20131231
with out using merge how can i handle Update,insert and Delete through TSQL
I have to create a trigger that will log who changed information on a table and when (NOT what they have changed).
My idea is to get the users name and see if it is in a table if not create it and get the associated ID, also get the ID of table that was accessed along with the ID of the type of task that was performed. Take this data and insert it into a table.
Here is the SQL I have so far.
-- Primary Database Tables --
CREATE TABLE Physician (
Physician_ID int not null identity(1,1) primary key,
First_Name varchar(100),
Last_Name varchar(100),
Mobile_Number varchar(15),
Pager_Number varchar(15)
[code].....
I am still learning SSIS.
What is the best way to do delete/insert/update in a database in SSIS. I am looking to achieve something similar to what was there in a Data Driven Query in SQL Server 2000. I would like to delete/insert/update the destination table based on the condition that my lookuptable returns
For ex
If "the user in my source table has changed the status" Then
Update the user row in the destination table
Else skip the row
If "the user in my source table has delete flag =1 " Then
Delete the user in the destination table
Else skip the row
If "the user in my source table is new(has a new ID) " Then
Insert the user in the destination table
Else skip the row
Any suggestions ....
Cheers
Siaj
hi,
I have a table which contained 5 columns and with 2 primary keys
Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |
ab | 1 | abc | null | null
ab | 2 | def | null | null
Col 1 and Col 2 both are primary keys.
How do I update Col2 from 1 to 2 and from 2 to 1 in a single transaction statement and commit it?
Thanks