Dataset Merge Update help!

[Guest]

I have reached my wits end with this and am in need of help!

I have a webservice that returns a dataset. I want to take this incoming
dataset, add a field to it (storeID), then merge this dataset with a seperate
dataset from a master table that contains the exact same fields, with an
additional MasterID (autoinc) field. I have the following code that doesn't
throw an error, but no update occurs in the master database.
========================================
CODE:
========================================
For iTableCount = 0 To fn_IncomingDS.Tables.Count - 1
fn_LocalTable = fn_IncomingDS.Tables(iTableCount).TableName

Dim da_LocalData As New SqlDataAdapter("SELECT * FROM " &
fn_LocalTable, objConn)

' Create an instance of a DataSet, and add data to the
Incoming table.
Dim dsLocal As New DataSet(fn_LocalTable)
da_LocalData.FillSchema(dsLocal, SchemaType.Mapped,
fn_LocalTable)
da_LocalData.Fill(dsLocal, fn_LocalTable)

Dim colInt32 As DataColumn = New DataColumn("TheatreID")
colInt32.DataType = System.Type.GetType("System.Int32")
fn_IncomingDS.Tables(iTableCount).Columns.Add(colInt32)

For Each row In fn_IncomingDS.Tables(iTableCount).Rows
row("StoreID") = fn_StoreID
Next
fn_IncomingDS.Tables(iTableCount).AcceptChanges()

'This is just for debugging/visual purposes
fn_Grid.DataSource = fn_IncomingDS
'When I display the resulting Dataset in a datagrid, it looks
PERFECT, with MasterID's, etc...


dsLocal.Merge(fn_IncomingDS.Tables(iTableCount), True,
MissingSchemaAction.AddWithKey)
If iTableCount = 0 Then
fn_Local.DataSource = dsLocal
End If
dsLocal.AcceptChanges()
Dim objCommandBuilder As New SqlCommandBuilder(da_LocalData)
da_LocalData.Update(dsLocal, fn_LocalTable)
'I get NO errors here, but the update doesn't occur.
Next
objConn.Close()
======================================
END CODE
======================================
Now, in testing, I've replaced the DS.MERGE with code that manually adds a
row to DSLOCAL, and the update runs fine.

Any help or pointers would be greatly appreciated!

-Ray!!!

 

[Andrew Conrad]

Try not accepting changes on the source DataTable (take out
fn_IncomingDS.Tables(iTableCount).AcceptChanges()).

Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/

 

[Guest]

Andrew,

Thanks for the response.

I tried commenting out the accept changes line and still had no luck.

However, after tons of digging and even purchasing a ADO.NET book, I
discovered what the problem is. The Dataset.Merge command sets all rows to a
rowstate of "unchanged", so that when you call datadapter.Update(dataset),
there are no changes in the dataset to update. I verified this by loading an
empty dataset from an empty table in SQL Server, then loading data into a 2nd
dataset from an identical database & table that had 30 records in it and
merging the 30 record dataset into the empty one. (I could bind this dataset
to a datagrid and see that I had 30 rows of data in it.) However, calling
the dataadapter.Update(Dataset1) still yeiled no updates to the database and
no errors, either.

In the end, I rolled my own "merge" routine by looking at the incoming
dataset and selecting data from the local dataset based on PKeys and
comparing rows & columns. It works great, but seems silly that the .Merge
wouldn't let me do the same thing.

I'd be interested in hearing from someone at microsoft if this is a bug, an
expected behavior, or if I'm trying to use .merge in a way that it wasn't
intended.

The end goal of my project was to have a generic class that I could use to
take data from webservices at many different stores (with identical
databases) and add/update the data in a corporate database without having to
have specific stored procs or routines for each table. I think I have
accomplished this goal, but it isn't nearly as elegant or easy as I thought
it would be with dataset.merge.

Thanks again for your response.

-Ray!!!

 

[Andrew Conrad]

Hi Ray -

Try also taking out:

dsLocal.AcceptChanges();

You assumption about Dataset.Merge is incorrect, it does not set the
RowState to unchanged during merge. If you are seeing different, please
post a simple repro and we can take a look.

Andrew Conrad
Microsoft Corp