Dataset and identity seed columns

[Stephen Ritchie]

I have a dataset that contains two tables - Parent and Child. The key of the
Parent is called ParentID and is an identity column in SQL Server. The key of
the Child is two columns called ParentID - foreign key defined as INT to
Parent table - and ChildID that in itself is an identity column. I have used
the designer to create a relationship within the dataset for the two tables.

I have set the dataset so the ParentID column starts at -1 and increments
which works fine as in code I can make sure that the FK on the Child table is
updated correctly.

The problem is when I try to update the datasource with the dataset. What
happens is the ParentID column on the Parent table is changed by the
database, say from -1 to 101 (which is the next seeded value for that table)
but this change is not cascaded down to the Child table and this trys to add
its records where the FK column still has the value -1, hence SQL Server then
kicks off with constraint errors.

If I use the dataset Edit Relation form so the update rule etc. is changed
from Default to Cascade then the error doesn't happen as the data from the
Child table is not inserted into the database (and no errors in .Net to tell
me it didn't do the insert!)

What am I doing wrong as I can't believe it won't be allowed ?

[Cor Ligthert]

Stephen,

If I was you, I would ask this question in the newsgroup

Adonet
news://msnews.microsoft.com/microsof...amework.adonet

Web interface:
http://communities2.microsoft.com/co...amework.adonet

There William(Bill)Vaughn loves to answer this kind of questions. (Or better
he does not, however he investigate this problem very well)

You can as well look at this page from him
http://msdn.microsoft.com/library/de...anidcrisis.asp

I hope this helps,

Cor