Hi Geoff,

You're right, the keys aren't the same. The question is, "how do you get the
dataset keys to sync up with the real keys in the database and how do you
maintain the parent/child relationship at the same time?

Let's assume the database issues the primary keys as autoincrement values.

On the dataset side:

In the xsd designer, for the primary key in each table, set AutoIncrement to
true, AutoIncrementSeed to -1 and AutoIncrementStep to -1.  Each row added
will have a unique primary value. It won't be "right", but at least it won't
conflict with the database or with any other user.

Edit each relationship between each table; in this case the relationship
between parent table "A" and child table "B".
Set the update rule to "cascade".  As a result of this setting, a change in
the value of parent table "A's" primary key will cascade down to child table
"B's" foreign key referencing parent table "A".  Thus, if something changes
the primary key in "A" from -3 to 35736328, the value of the foreign key in
table "B" will also change from -3 to 35736328 preserving the parent/child
relationship.

Moving on to the data adapter:

In the INSERT statement, have something that brings the SCOPE_IDENTITY()
from the database with the real primary key.  For example, the INSERT
statement for table "A" would be:

INSERT INTO A ( SomeString )
VALUES ( @SomeString ); SELECT A_ID WHERE A_ID =SCOPE_IDENTITY()

Notice two important points. First, we don't insert the primary key (-3)
that was generated by the dataset. Second, we bring back the real primary
key (35736328) issued by the database.  Through Microsoft magic, the data
adapter's Update() method will change the primary key value in table "A" and
will cascade the new foreign key values to table "B".

Now for the code:

If you've used the data adapter's Update() method on the original dataset,
you're done.  However, if you created a small dataset with only changes to
send back to the database using the dataset's GetChanges() method and
subsequent Merge() method, you will discover that Microsoft's magic left
your original (-3) row intact and added a new row (35736328).

To get around this behavior, add the following event handler to the data
adapter:

 private void daTblA_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
   {
       if ( e.StatementType == StatementType.Insert) e.Status =
UpdateStatus.SkipCurrentRow;
   }

Note, with the dataset's WriteXml() method, examine the GetChanges dataset's
diffgram after the update to the database but prior to the Merge() operation
back to the original dataset.

Jim

Your assumption is correct if you are assigning the key value to the
new rows.

If you use identity columns the new identities will become part of the
relationship when inserted into the DB if you have set the DataSet up
properly.

There is a good explanation of this in Sceppa's book "ADO.NET" (MS
Press).

HTH

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com