DataSet Update() Method...has anyone seen it work?

DataSet Update() Method...has anyone seen it work?

• Vipul123

  Ok...here's how I got it working. Thanks Badri Narayanan for pointing me in the right direction.

  The problem was indeed with the rowstate. After the Merge() method was called, all the rowstates were "Unchanged {2}" which means any call to the Update() method would result in no change in the database what so ever.

  Instead of using the Merge() method, I programatically read the rows from the server dataset and added those rows to my ds_remote dataset using a for loop. the update method worked then. I would think the Merge() function should set the rowstate to "added" or "changed" for any row that wasn't previously there, but It doesn't seem to. Oh well...it works now. Thanks guys!

  Here's the code if you want to take a look:

  Public Shared Function SynchronizeUsers() As Boolean

  Dim ds_Server As DataSet = GetUsersFromServer()

  Dim cn As New SqlConnection(Utility.cn_Remote)

  Dim cm As New SqlCommand

  With cm

  .Connection = cn

  .CommandText = "spUser_Get_Users_Synchronize"

  .CommandType = CommandType.StoredProcedure

  End With

  Dim da As New SqlDataAdapter(cm)

  Dim ds_Remote As New DataSet

  Dim ComBuilder As New SqlCommandBuilder(da)

  da.UpdateCommand = ComBuilder.GetUpdateCommand

  Try

  da.Fill(ds_Remote, "Remote")

  ds_Remote.Clear()

  Dim i As Integer

  Dim row As DataRow

  For i = 0 To ds_Server.Tables("Server").Rows.Count - 1

  row = ds_Remote.Tables("Remote").NewRow()

  row("UserID") = ds_Server.Tables("Server").Rows(i).Item("UserID")

  row("UserLogin") = ds_Server.Tables("Server").Rows(i).Item("UserLogin")

  row("MD5Password") = ds_Server.Tables("Server").Rows(i).Item("MD5Password")

  row("RoleID") = ds_Server.Tables("Server").Rows(i).Item("RoleID")

  ds_Remote.Tables("Remote").Rows.Add(row)

  Next

  da.Update(ds_Remote, "Remote")

  Catch ex As Exception

  Return Nothing

  End Try

  End Function

  John

  
  

• Test 007

  No execeptions...the SQLExpress DB is never updated.

  Yes...I'm clearing the local dataset before merging. Basically, I just want to duplicate the users table from the server database on the client database. The Clear() method will empy the local dataset...then the Merge() method will populate it with data from the server dataset. Without first clearing the local dataset, I'll end up with duplicate records.

  
  

• Constantin Mihai - MSFT

  What is the problem you are facing Getting any exception

  I notice that you clear the the local data before merging. As far as I know, this would have removed all your rows in the local data table. And merging with another datatable (remote in ur case) would have added the remote rows to local data table. This would result in setting the row state of new rows in local data table to "Added" mode. Which means that there is no diff gram for update command to work. I would advise you to just check the row states of the rows in local data table after merging. Most probably that will be "Added". Try commenting the .Clear statement and then merge it and update the local database using the Update method.

  HTH

  
  

• Jeff

  Are you sure the RowStates were set to Unchanged immediately after the Merge It's definitely possible, but I noticed your original code calls AcceptChanges before it calls Update. If there were any Added rows after the Merge, AcceptChanges changes Added rows to Unchanged.

  So, if your rows weren't Added in the ds_Server table, they won't be Added after the Merge, and even if they were Added before and after the Merge, AcceptChanges will set them back to Unchanged. Instead of having to create a new Row like you are doing, you could also use the SetAdded method in ADO.NET 2.0. This will only work if the row is in an Added or Unchanged state, otherwise it throws an exception. If you know all of your rows are coming in as Unchanged, you could just change them all to Added before calling Update.

  Another option is to use the DataTable.Load method (again, this is new in 2.0). If you use the LoadOption Upsert, if there are rows being loaded that don't exist in the destination DataTable, it will set the RowState to Added. Load takes a DataReader as input for the source, but if your data is already in a DataTable, you can use the CreateDataReader method to create a reader over the table.

  ds.Tables["Product"].Load(newTable.CreateDataReader(), LoadOption.Upsert);

  This doc has a chart that shows all of the combinations you could get using Load: http://msdn2.microsoft.com/en-us/library/4e06d41f.aspx.

  None of this may work for you any better than what you are already doing, but I just wanted to throw these things out there.

  Thanks,

  Sarah

  
  
  

• Ben Morris

  GetUsersFromServer() does indeed return the appropriate data.

  As for not needing "da.updateCommand = ComBuilder.GetUpdateCommand...I didn't think so either, but if I don't include it then da.updateCommand evaluates to nothing.

  I didn't think this was expected behavior, but after setting a break point and stepping through the code and querying da.Updatecommand, I found that it was nothing...regardless that's not the issue at hand. It seems ALOT of people are having problems with the Update() method of the dataset object.

  Thanks for the post!

  
  

• ZGutt

  Thanks, Sarah!
  

• SaliDM

  Have you ever checked if "GetUsersFromServer()" does return data

  Moreover, you do not need

  da.UpdateCommand = ComBuilder.GetUpdateCommand

  after

  Dim ComBuilder As New SqlCommandBuilder(da)

  Since, when you create a command builder objects with dataadapter passed to it, during construction of CommandBuilder object, command builder already fills DataAdapater with appropriate DELETE/UPDATE/INSERT commands.

  
  
• DataSet Update() Method...has anyone seen it work?