DataSet.Merge / auto number on PK -> duplicated rows problem

B

Benjamin

Hi!

I create a new row in dataset "ds" and press "save" where following is done:

ds = the full dataset, that was loaded at the start up of the form
dsChanges = that is simply ds.GetChanges(). this dataset is the one sent
down to "data layer"

Step 1.

'We can see that the new row is created - note the status "Added":
ds.tables("PriceLists").rows.count ---> 21 Integer
ds.Tables("PriceLists").Rows(20).Rowstate ---> Added
System.Data.DataRowState

'Note that I have not entered ID for PK column - that is automaticly
generated in "data layer":
ds.Tables("PriceLists").Rows(20).Item("PriceListID") {System.DBNull} Object

'We can also see that the "difference dataset" contains the new row:
dsChanges.Tables("PriceLists").Rows.Count ---> 1 Integer
'...and of course is the PK column still correctly null:
dschanges.tables("PriceLists").Rows(0).Item("PriceListID") {System.DBNull}
Object

Step 2.

'I send down dsChanges to data layer. The data layer returns a dsChanges
that update is done on (using stored procedures...).
'We can no see that the row has successfully been added to the database,
'with a "Identity" (MSSQL) value as PK. Perfect!:
dschanges.tables("PriceLists").Rows(0).Item("PriceListID") 1635 {Integer}
Object

Step 3.

'Let me now simply do a "ds.Merge(dsChanges)" and have a new look at the
values.
'What! - Merge has added a NEW row instead of updating the newly created
one.
ds.Tables("PriceLists").Rows.Count ---> 22 Integer

'The row that was added by me was not touched by the Merge (status still
"Added") - see:
ds.Tables("PriceLists").Rows(20).Rowstate ---> Added
System.Data.DataRowState
ds.Tables("PriceLists").Rows(20).Item("PriceListID") {System.DBNull} Object

'But the - NOT WANTED - new row that Merge created has the new PK value.
ds.Tables("PriceLists").Rows(21).Item("PriceListID") 1635 {Integer} Object

Merge fails to understand how to merge the newly created row that was
bounced to data layer and complemented with the "automaticly created unique
primary key value" (quote from help file above).

Can you understand my problem?
Working with the combination of "GetChanges" / "Merge" / "MSSQL - Identity
column" must be a common scenario.
The scenario should really work - MS is describing it in the help file
referred to above ;-)

I really hope I am doing something wrong - but what ?

Can it be the lack of .PrimaryKey constraint on the DataTable that makes it
impossible for Merge to find the row to update?
BUT I can NOT have a .PrimaryKey constraint - as I have understood - because
the client dataset MUST ALLOW the PriceListID column to be NULL until
complemented with "Identity generated PK" by data layer.

Coding in VisualBasic.

Best regards
Benjamin Tengelin, Sweden
 
B

Benjamin

Hi!

I looked at the article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp
and followed it by hooked on PrimaryKey and AutoIncrements settings. But...
This article is a nice suggestion if your are satisfied with the fact that you, after saving, continue (!) to work with the client side (!) generated AutoIncrements (-1,-2,-3 ...) until you do a fresh reload of the DataSet (e g restarts the form). It is first when you do a complete DataSet reload that you get the real server (!) side AutoIncrements.

But MSDN documentation "promises" more...
http://msdn.microsoft.com/library/d...ml/frlrfsystemdatadatasetclassmergetopic2.asp

It says (and that is exactly what I want to achive):

In a client application, it is usual to have a single button that the user can click that gathers the changed data and validates it before sending it back to a middle tier component. In this scenario, the GetChanges method is first invoked. That method returns a second DataSet optimized for validating and merging. This second DataSet object contains only the DataTable and DataRow objects that were changed, resulting in a subset of the original DataSet. This subset is generally smaller, and thus more efficiently passed back to a middle tier component. The middle tier component will then update the original data source with the changes through stored procedures. The middle tier can then send back either a new DataSet that includes original data and the latest data from the data source (by running the original query again), or it can send back the subset with any changes that have been made to it from the data source. (For example, if the data source automatically creates unique primary key values, these values can be propagated back to the client application.) In either case, the returned DataSet can be merged back into the client application's original DataSet with the Merge method.

This documentation doesn't say that you have to do a total reload of the DataSet to get the server side generated AutoIncrements.
The new row should simple be bounced down to data layer and the new row in the client dataset should be "replaced"/"complemented" by using the ..Merge-method.
Not added !!!

Anyone that has implemented the above nice description by Microsoft?

Best regards
Benjamin Tengelin, Sweden
 
J

Jay B. Harlow [MVP - Outlook]

Benjamin,
In addition to Bill Vaughn's article, David Sceppa explains what is
happening and gives an example of using GetChanges & Merge (as you quoted in
your other post) in his book "Microsoft ADO.NET - Core Reference" from MS
Press.

If you are doing a lot with ADO.NET I strongly recommend Sceppa's book,
which is a good tutorial on ADO.NET as well as a good desk reference once
you know ADO.NET.

Hope this helps
Jay
 
B

Benjamin

Thanks for the book tip of the day.

It is not easy for us - faitful "MSDN Universal" developers - to accept that
a suggestion that is _described in the MSDN help_, as it would be the most
easy thing to achive, can not be completed without buying complementary
articles by MS Press...

Let me repeat myself: "Anyone that has implemented the above nice
description by Microsoft?" (<- I refer to my previous posting).

Best regards
Benjamin Tengelin, Sweden
 
B

Benjamin

I searched Internet for posts from the author David Sceppa, and found
http://www.dotnet247.com/247reference/msgs/7/37008.aspx

He sais there: "If I'm working with a multi-tiered application, I submit the
changes, return the DataSet with the new auto-increment values and then
purge the pending inserts from the original DataSet prior to merging in the
DataSet that the middle tier returned. This solution leaves a bad taste in
my mouth but has proven effective."

That "purge work around" was what we thought of initially when hitting this
problem.
But as he sais "it leaves a bad tast in my mouth" too.

If anyone has solved this issue in a more standard/clean way, without
manually purge, then please post it.

Best regards
Benjamin Tengelin, Sweden
 
J

Jay B. Harlow [MVP - Outlook]

Benjamin,
I'm really not sure what the problem is with using the method Sceppa
describes or the description in MSDN.

The way I read the description in MSDN, along with my understanding of
databases & datasets, leads me to the method that Sceppa describes...
If anyone has solved this issue in a more standard/clean way, without
manually purge, then please post it.
Click to expand...
Do you mean with avoiding AutoIncrement fields? You could always use GUID
fields and have the client assign the value that the database will use (in
other words, don't let the database assign the GUID...)

Hope this helps
Jay
 
G

Guest

If you simple delete the rows all child rows would be set to null,deleted, or an exception would be raises. If not a matter of just deleting. It's a matter of writing a new merge method that works.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Accessing deleted rows problem 2
DataTable, Delete, GetChanges, Merge....What's wrong? 2
Using the dataSet.Merge method 2
Dataset.Merge - troubles 1
NewRow, RowState, and Detached with a CommandBuilder 1
DataRow primary key is not updated although the DataSet's PK is 1
No rows returned with OleDbParameter.Value = DBNull.Value 0
can anyone help me with this simple XML example? 3

Top