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 fieldsmatch up records using the original values of the primary key fields andnot the current values?



Des


-- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

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.

"DesCF"  wrote in message news:op.twi8qrevupgxg0@descstar...

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


-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

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 violatingnon-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 recordback in the SQL Server table and it is then updated to the new value. Animmediately 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 noweither the smae as the current or does not exist - I have not checked  which)

The merge fails, however if I suppress 'AcceptChanges' in the RowUpdatedevent 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 andmerging business.


Des






On Sat, 04 Aug 2007 13:29:09 퍝, Earl <brikshoe@newsgroups.nospam>  wrote:


> 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.
>
> "DesCF"  wrote in message  > news:op.twi8qrevupgxg0@descstar...
>
> 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
>
>




-- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

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 thathave 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."





On Sat, 04 Aug 2007 12:26:37 퍝, DesCF  wrote:


>
> 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
>
>




-- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/