Updating and Merging with Order Details

[DesCF]

My question is do merges always take place using the original values of
primary key fields ?

For example using the Northwind Order Details table:

1. The primary key is OrderID and ProductID.
2. The user can change the ProductID which changes the primary key.
3. Both the original ProductID and the current ProductID must be returned
to SQL Server to enable it to identify the row affected using the the
OrderID and the original ProductID.
4. The ProductID for the affected record is updated to the new value.
5. A select query then returns the other fields in the record to the
GetChanges DataTable using the OrderID and the now current (NOT the
original) ProductID in the WHERE clause.
6. The GetChanges DataTable is then merged with the Order Details
DataTable in the DataSet.

The above scenario produces an error.
In order to prevent the error and get the correct result, i.e. what is
effectively a row being overwritten by identical data in this instance, I
have to do the following on the RowUpdated event:

' Suppress AcceptChanges for updated rows to preserve the original ID
values.
If e.StatementType = StatementType.Update Then
e.Status = UpdateStatus.SkipCurrentRow
End If

I can only assume this is necessary because merges on primary key fields
match up records using the original values of the primary key fields and
not the current values?



Des

 

[Earl]

I don't have Northwind attached to my server right now, but your ProductID
is surely a foreign key instead of a primary.

You didn't say what type of exception you were getting, but I am guessing
you are getting a FK constraint exception if the user changes the FK without
the parent Products table having a corresponding entry.


My question is do merges always take place using the original values of
primary key fields ?

For example using the Northwind Order Details table:

1. The primary key is OrderID and ProductID.
2. The user can change the ProductID which changes the primary key.
3. Both the original ProductID and the current ProductID must be returned
to SQL Server to enable it to identify the row affected using the the
OrderID and the original ProductID.
4. The ProductID for the affected record is updated to the new value.
5. A select query then returns the other fields in the record to the
GetChanges DataTable using the OrderID and the now current (NOT the
original) ProductID in the WHERE clause.
6. The GetChanges DataTable is then merged with the Order Details
DataTable in the DataSet.

The above scenario produces an error.
In order to prevent the error and get the correct result, i.e. what is
effectively a row being overwritten by identical data in this instance, I
have to do the following on the RowUpdated event:

' Suppress AcceptChanges for updated rows to preserve the original ID
values.
If e.StatementType = StatementType.Update Then
e.Status = UpdateStatus.SkipCurrentRow
End If

I can only assume this is necessary because merges on primary key fields
match up records using the original values of the primary key fields and
not the current values?



Des

 

[DesCF]

i am using a stand-alone Order Details table, there are no other tables or
relationships in the DataSet. The error occurs when a merge is attempted
between the GetChanges and Orders Details tables:

"Failed to enable constraints. One or more rows contain values violating
non-null, unique, or foreign-key constraints."

A primary key is set on the OrderID and ProductID fields (or columns in
ADO)

If the ProductID is changed in a record it will have both an original and
a current value. The original value is then used to identify the record
back in the SQL Server table and it is then updated to the new value. An
immediately following select statement retrives the entire updated record
using the OrderID and the ProductID (NOT the original) into the GetChanges
DataTable. If the scenario is as follows:

Order Details DataTable: ProductID.Original = 30, ProductID.Current =40
GetChanges DataTable: ProductID.Current = 40
(I am assuming that the original ProductID value in the DataTable is now
either the smae as the current or does not exist - I have not checked
which)

The merge fails, however if I suppress 'AcceptChanges' in the RowUpdated
event for updated records so that the scenario is as follows:

Order Details DataTable: ProductID.Original = 30, ProductID.Current =40
GetChanges DataTable: ProductID.Original = 30, ProductID.Current = 40

The merge succeeds.

Therefore I am assuming that on primary key fields the merge operation
matches up records using the original values because I cannot think of
another reason why the merge would fail. Tricky stuff this updating and
merging business.


Des

 

[DesCF]

I've been looking around in books online:

"If the table receiving new data and schema from a merge has a primary
key, new rows from the incoming data are matched with existing rows that
have the same Original primary key values as those in the incoming data."

"Consider as an example a case where an existing row in a DataSet is an
Unchanged row with a primary key value of 1. During a merge operation with
a Modified incoming row with an Original primary key value of 2 and a
Current primary key value of 1, the existing row and the incoming row are
not considered matching because the Original primary key values differ.
However, when the merge is completed and constraints are checked, an
exception will be thrown because the Current primary key values violate
the unique constraint for the primary key column."