index > .NET Framework Data Access and Storage > GetChanges - Update - Merge result in redudant row

GetChanges - Update - Merge result in redudant row

I would like some assistance in the following situation.

A table has 0 rows
Row is added to table (table.rows.add)
Table has 1 row
GetChanges is called
Update is called
Merge is called
Table has 2 rows (!)

I suppose that this has to do with the primary key of table being autoincrement (both in database and in typed table). What's the procedure to do this right?

Dimitris Papadimitriou

papadi

Anybody?

Dimitris Papadimitriou

papadi

Do you have the complete code sample?

From your description, it's hard to tell something like which tale to merge which table.

This posting is provided "AS IS" with no warranties, and confers no rights.

Bill Lin - MSFT

It's not much. Here:

1. Dim changedData As DataSet = data.GetChanges()
2. changedData = WebServiceProxy.Update(changedData)
3. data.Merge(changedData)

Line 1 get changes, line 2 call a web service that does the update using common data adapters and line 3 merges the returned dataset. Web Service call does not accept dataset by reference so it returns it as a return value also. Autoinrement values are updated using an update command with this format:

"INSERT INTO [TABLE] (........) VALUES (.............); SELECT [PKID] FROM [TABLE] WHERE [pkID]=SCOPE_IDENTITY()"

Original dataset in line 1 has one added row. And finally dataset after line 3 has on added row and on unchanged row, which is the one inserted to the database.

Thanks for helping me out!

Dimitris Papadimitriou

papadi

I am doing something very similar with my datasets/web services.

The problem is that the returned rows have different ID values than the local dataset rows. When you try to merge in the changes then the dataset has no idea how to resolve them as the same row, so it just adds them. So what you have to do is to get rid of the changes in the local dataset and replace them with the changed values.

This is how I was able to resolve it:
All of my datasets are typed datasets and I added a new method called Meld to each one. I then Cal GetCHanges, pass it to the webmethod and get the updated changes. I then pass it to the Meld method for removing the local changes and merge in the new.

Private Sub Meld(ByVal changes As TypedDataSet)
'first loop through and accept all deletes
For Each dt As DataTable In Me.Tables
For i As Integer = dt.Rows.Count - 1 To 0 Step -1
If dt.Rows(i).RowState = DataRowState.Deleted Then
dt.Rows(i).AcceptChanges()
End If
Next
Next
'stop enforce constraints while we perform the merge
Me.EnforceConstraints = False

'then reject all additions
Me.RejectChanges()

'then merge in all additions and modifications that we got returned
Me.Merge(changes)

Me.EnforceConstraints = True

'now accept all changes
Me.AcceptChanges()
End Sub

kbradl1

I think I found the solution!

http://msdn2.microsoft.com/en-us/library/ks9f57t0.aspx

Search for "Merging" in this article. I haven't tried it yet but it describes this exact situation!

Dimitris Papadimitriou

papadi

And... it works!

I always thought that listening to Queen can be inspiring!

Dimitris Papadimitriou

papadi

papadi wrote:

I think I found the solution!

http://msdn2.microsoft.com/en-us/library/ks9f57t0.aspx

Search for "Merging" in this article. I haven't tried it yet but it describes this exact situation!