Parent/Child inserts with ForeignKeyConstraint

[SRoberts_]

A common problem people seem to have (using the Microsoft DAAB or raw ADO.NET) is how to automatically insert records in a parent table and then automatically have related child table records updated with the primary key from the parent.

There seem to be many ways to do this in raw ADO.NET if you're happy to write all the code yourself and there are a myriad of issues along the way. However I'm a great believer in less code is better and therefore struggled on to get to where I'm at now

============ THE BIT THAT DOES WORK - READ IF YOU'RE STUCK ================
I have two data tables with a 'ForeignKeyConstraint' (created in code) between them, the parent column is the primary key field from the parent table and the child column is the foreign key field from the child table.

The constraint has both 'DeleteRule' and 'UpdateRule' set to 'Rule.Cascade' to ensure that record updates/deletions that occur in the parent cause the primary key field to automatically cascade across to the child table.

The constraint also has 'AcceptRejectRule' set to AcceptRejectRule.None, this will prevent the AcceptChanges call on the parent table from cascading down to the child table (I have applied the bug fix whereby the AcceptChanges was originally firing for ALL tables in the D/A)

I have created an INSERT command ready to pass to UpdateDataSet, this command had an OUTPUT parameter provided by my INSERT Stored Proc that is mapped to the parent table primary key field to ensure that when the S/Proc gets run, the new record identity (returned using SCOPE_IDENTITY) is automatically pushed back into the parent datarow causing the ForeignKeyContraint update to fire...phew!

When I call the UpdateDataSet method on the parent table, everything works as expected, I can see by my SQL Profiler trace that a new parent record gets inserted and the new IDENTITY_SCOPE() value is returned, if I debug and watch the parent datatable row, I can see the identity has been automatically mapped back into the data row for me, if I then check the related records in the child table I can see that they also now have the (new) correct ID value in the foreign key field, due to the foreignkeyconstraint having updated them for me, yipee I cry !

So everything is working great so far....now for the bit I'm banging my head on a wall with...

=========== THE BIT THAT DOESN'T WORK - READ THIS IF YOU WANT TO HELP !!! ==============

When I go to call the UpdateDataSet method for the child table, I get a SQL Server error that tells me ;

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblTenderLinkedItemCharacteristics_tblTenderLinkedItems1'. The conflict occurred in database 'xxxxxxxxx', table 'tblTenderLinkedItems', column 'TenderLinkedItemID'."

When I examine my SQL Server profiler trace I can see that it has tried to call my child insert stored procedure using the old parent record ID as opposed to the new ID I can debug and see in the child data row.

So the question is what controls which version of the data that the data adapter uses when running the INSERT, UPDATE and DELETE commands ?