DataAdapter Update Problems

Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows"

I've looked and I can see what the problem is, can anybody help please?

Private Sub FillData()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String If Request("action") ="gen"Then sql ="select item from tblPickList where GroupName ='xxx'AND Category = '" & Category2 & "' ORDER BY item ASC" ElseIf Request("action") = "fav" Then sql = "select identifiertext as item from tblfavourites where HRID =" & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC" ElseIf Request("action") = "spec" Then sql = "select item from tblPickList where GroupName ='" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC"End If ds =New DataSet da =New SqlClient.SqlDataAdapter(sql, sqlconnection)Try sqlconnection.Open() da.Fill(ds,"tblPickListTemp") sqlconnection.Close()Catch exAs Exception sqlconnection.Close()End Try ds.Tables(0).Columns.Add("HRID") ds.Tables(0).Columns.Add("Flag")'may possibly add a favourites column here at a later date to ensure no duplicationDim drAs DataRowFor Each drIn ds.Tables("tblPickListTemp").Rows dr("HRID") = Session("hrid") dr("Flag") = 0Next DgrdFavourites.DataSource = ds DgrdFavourites.DataBind()End Sub Public Sub CreateTempRecords()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlcommand =New System.Data.SqlClient.SqlCommandMe.da =New SqlClient.SqlDataAdapterMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String sql ="Insert into tblPickListTemp (Item, HRID, Flag) values (@.Item, @.HRID, @.Flag)" sqlcommand.CommandText = sql sqlcommand.Connection = sqlconnection sqlcommand.Parameters.Add("@.Item","Item") sqlcommand.Parameters.Add("@.HRID","HRID") sqlcommand.Parameters.Add("@.Flag","Flag") da.InsertCommand = sqlcommand da.Update(ds.Tables(0))End Sub

Hello Naimh,

Your program retrieves records from the database, you modify them and try to update the records in the database.

In that case you need to add an UpdateCommand to your SqlDataAdapter.

The Update statement will than handle new rows and modified rows.

Jeroen Molenaar.

|||I thought that was what I was doing with the da.InsertCommand on the 2nd to last line.I'm not sure I've really got to grips with the dataadapter syntax, could you show me please?|||

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

sql = "Update tblPickList Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

// Add the parameters here ...

sqlcommand.CommandText = sql

da.UpdateCommand = sqlcommand

// etc.

Hope you get it working now.

Jeroen Molenaar.

|||

jeroenm:

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

Okay really confused now, as that is exactly what I want to do, I populate the dataset from one table, manipulate it in the dataset and then Insert entirely new row's into a completely different table, erk! I shall give your suggestion a try but I have to fess up to being none the wiser.|||

jeroenm:

sql?= "Update tblPickListTemp Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

See now this is never going to work for me, tblPickListTemp ( the table I want to insert rows into) at the start of this process is empty, I cannot update and set fields to new values when no records currently exist.Ah well back to the drawing board, thanks anyway.|||

Do you have an AcceptChanges statement somewhere before the data is saved to the database?

When you modify the dataset after AcceptChanges, the tableadapter thinks it needs an Update statement in stead of an Insert statement.

Jeroen Molenaar.

|||No I am afraid I don't, all the code is in the snippet above. It's most confusing. I have also ensured that the primary key is set in the recipient table, which is another common problem which throws this error. I am at a real loss and almost at the point of scrapping the whole thing and taking a different approach, it's just that if I could have got this to work it would have been less trips to the Db and a lot less spaghetti code.|||

You can add some ASP.NET code. Perhaps this gives another clue.

|||

<form id="Form1" method="post" runat="server"><TABLE id="Table2" cellSpacing="0" cellPadding="0" width="280" align="center" border="0"><TR height="24"><TD class="fntmed" style="WIDTH: 280px" background="../images/header.jpg">         PickList</TD></TR><TR><TD class="fntnorm" style="WIDTH: 280px; HEIGHT: 36px"><IMG height="6" src="../images/6pixspacer.gif"><br><asp:image id="imgLine" runat="server" Width="260px" ImageUrl="../Images/line.jpg"></asp:image></TD></TR><TR><TD><div style="OVERFLOW: auto; WIDTH: 270px; POSITION: static; HEIGHT: 190px"><asp:DataGrid id="DgrdFavourites" autogeneratecolumns="False" BackColor="#ffffff" showheader="False"OnItemCommand="DgrdFavourites_ItemCommand" runat="server" Width="256px" Height="25px" BorderWidth="0"CssClass="fntsmall" AlternatingItemStyle-CssClass="fntsmall"><AlternatingItemStyle BackColor="#e2eaee" /><Columns><asp:BoundColumn DataField="Item" /><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Select" commandname="Select" runat="server" ID="btnSelect" /></ItemTemplate></asp:templatecolumn><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Favourite" runat="server" commandname="Add" ID="btnAdd" /></ItemTemplate></asp:templatecolumn></Columns></asp:DataGrid></div></TD></TR></TABLE><asp:Label ID="oops" Runat="server"></asp:Label></form></body>

That the entirety of the front end code, basically at page load inside a "If not Is Postback" the Filldata() method is invoked followed by the CreateTempData() method. Not a lot to it really.|||

Hello Naimh,

Sorry, but I've been moving you into the wrong direction. My first idea was correct, but I didn't read your code properly.

When you call FillData the dataset ds is filled from the table tblPickList. In FillData you add 2 columns and modify all rows with the session HRID. At this stage your rows have the status Modified in the dataset ds. (This generates the error message when calling da.Update())

Before "DgrdFavourites.DataSource = ds" you can enter the command ds.AcceptChanges(). All modifications are accepted and no update statement will be generated by the dataadapter.

The second thing is make some changes to CreateTempRecords. Create a new dataset dstemp as a copy of ds. After that insert the rows from ds into the new dataset dstemp, for example with a foreach construction. Now the new dataset is ready for the insert statement that you have written in CreateTempRecords(). Change da.Update(ds.Tables(0)) into da.Update(dstemp.Tables(0)).

Now your code should do the job!

Jeroen Molenaar.