Updating and Merging with Order Details

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 returne=
d =

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 =3D StatementType.Update Then
e.Status =3D 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/

Earl
Sat Aug 04 07:29:09 CDT 2007

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" <descf@aol.com> 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/

DesCF
Sat Aug 04 09:13:08 CDT 2007

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 attempte=
d =

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 an=
d =

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 recor=
d =

using the OrderID and the ProductID (NOT the original) into the GetChang=
es =

DataTable. If the scenario is as follows:

Order Details DataTable: ProductID.Original =3D 30, ProductID.Current =3D=
40
GetChanges DataTable: ProductID.Current =3D 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 =3D 30, ProductID.Current =3D=
40
GetChanges DataTable: ProductID.Original =3D 30, ProductID.Current =3D 4=
0

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






On Sat, 04 Aug 2007 13:29:09 +0100, 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 guess=
ing
> you are getting a FK constraint exception if the user changes the FK =

> without
> the parent Products table having a corresponding entry.
>
> "DesCF" <descf@aol.com> wrote in message =

> news:op.twi8qrevupgxg0@descstar...
>
> My question is do merges always take place using the original values o=
f
> 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 retur=
ned
> 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 origina=
l =

> ID
> values.
> If e.StatementType =3D StatementType.Update Then
> e.Status =3D UpdateStatus.SkipCurrentRow
> End If
>
> I can only assume this is necessary because merges on primary key fiel=
ds
> match up records using the original values of the primary key fields a=
nd
> not the current values?
>
>
>
> Des
>
>



-- =

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

DesCF
Sat Aug 04 09:42:00 CDT 2007


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 wi=
th =

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 ar=
e =

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 +0100, DesCF <descf@aol.com> wrote:

>
> My question is do merges always take place using the original values o=
f =

> 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 origina=
l =

> ID values.
> If e.StatementType =3D StatementType.Update Then
> e.Status =3D UpdateStatus.SkipCurrentRow
> End If
>
> I can only assume this is necessary because merges on primary key fiel=
ds =

> match up records using the original values of the primary key fields a=
nd =

> not the current values?
>
>
>
> Des
>
>



-- =

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