Read Dataset from webservice and update local sql server!!

[Jay Balapa]

Hello,

Iam trouble updating my local version sql server database from a webservice
which returns a dataset. I call update but no rows are added any help will
greatly be appreciated. I have already verified that the webservice dataset
already has valid data.


Here is the code snippet.
Public Shared Sub SetSyncDataset(ByVal myOldDataset As DataSet)

Try

Dim myDataset As DataSet = New DataSet()

Dim cn As System.Data.SqlServerCe.SqlCeConnection

Dim myAdapter As System.Data.SqlServerCe.SqlCeDataAdapter

Dim connStr As String = "Data Source = " &
SQLDatabase.NewRootDir & "vvvv.sdf" & "; Password =xyxx"

cn = New System.Data.SqlServerCe.SqlCeConnection(connStr)

cn.Open()

myAdapter = New System.Data.SqlServerCe.SqlCeDataAdapter("SELECT
* FROM MGUSERS WHERE 1=0", cn)

myAdapter.FillSchema(myDataset, SchemaType.Source, "MGUSERS")

myAdapter.Fill(myDataset, "MGUSERS")



Dim cb As SqlCeCommandBuilder = New SqlCeCommandBuilder(myAdapter)

myAdapter.MissingMappingAction =
MissingMappingAction.Passthrough

myAdapter.InsertCommand = cb.GetInsertCommand()

myAdapter.Update(myOldDataset, "MGUSERS")

myAdapter.Dispose()

' Close the database.

cn.Close()





Catch e As SqlCeException



MessageBox.Show(e.Message)

End Try







End Sub

 

[W.G. Ryan - MVP]

The problem is most likely with Rowstate on the dataset that's returned.
Check the HasChanges property of the dataset when it returns, if there are
no changes present, then Update isn't going to work. You can set the
AcceptChangesDuringFill property to false on the adapter that fills it, that
will ensure every row has a rowstate of Added and then when you call Update,
the insert command should fire for each row. Or you can just loop through
each row and fire an update on your own.

Check the getchanges with
System.Diagnostics.Debug.Assert(myOldDataset.HasChanges); and see what
happens. If the assertion fails then the above will work. If not, let me
know and we'll take it from there.

Cheers,
Bill