ADO.Net how to get auto-increment primary key from master into detail table
Posted on 2010-09-22
Using Visual Studio 2008 I have written an app in VB.Net that runs under .Net Framework 2.0.
I am working with disconnected datasets and in this example I have 2 tables in a dataset.
One table can be regarded as the master and the other as the detail.
The Master table has an autoincrement Primary Key value, the detail table has a foreign key into this primary key.
The detail tables' foreign key allows NULLs.
When I add a new row to the master (disconnected from the database) it gets a -ve primary key value i.e. -1 - (because the autoincrement PK can't be properly known until the data is inserted into the database).
When I call an Update() on the Master table, the -ve PK is then replaced with a real PK from the database.
I then need to update the foreign key value in the detail table to match the real PK, but I can't work out how to get the real PK from the Master to the Detail table.
Tried creating a constraint in the dataset bewteen the tables with cascaded updates, this worked fine except that the constaint will not allow the foreign key in the detail table to be NULL. I need to allow NULLs in the detail table - so that won't work.
I can call Update() on the master and get it to generate the proper PKs, but can't find a way of tying up a master PK with which detail row it corresponds to.
Any suggestions welcome.