Updating multiple tables with UpdateDataSet

Topics: Data Access Application Block, General discussion
Feb 26, 2007 at 9:41 PM
I have a 'dsCustOrd' dataset (with 'Customers', 'Orders' tables).
There is a relationship between the tables on CustomerID.
There is a constraint between the tables on CustomerID. (Can't add to 'Orders' if a customer doesn't exist)

I add a new row to the 'Customers' table.
I add a new row to the 'Orders' table. For that row, I fill the 'CustomerID' with -1 since it is related to the newly added 'Customers' row whose CustomerID is not yet set.

How can I update this dataset properly?

Currently, I call UpdateDataSet(dsCustOrd, 'Customers',...) which works.
Then I call UpdateDataset(dsCustOrd, 'Orders',...) which doesn't work and gives an "Insert statement conflicted with Column Foreign Key constraint..." error, most likely since the -1 value in CustomerID does not match the new CustomerID of the new row in 'Customers'.

How can I properly update both tables???

Feb 27, 2007 at 8:43 AM
Could we have a look on your code and your sql to see what your doing.

Is this Entlib 2.0?

Your logic seems a little shaky. How do u know what customerid to use when you are updating the orders table and you have a newly created customer?
Feb 27, 2007 at 1:25 PM
Yes, it is Entlib 2.0

I don't know what customerid to use. That's why I enter -1. Since the tables are related, I thought it would be filled once the Customer record is saved and a customerid is created.
Feb 27, 2007 at 9:49 PM
U cant have the constraint there.

Because if you update the customer table with the newly created id, u get another constraint error.

In your way is to decouple the tables (No constraint)

Run the UpdateDataSet(dsCustOrd, 'Customers', ...) Refresh the Customers table and read the newly created customerid

Update the Orders table in the dataset, replacing -1 with the newly created customerid

Then you are ready to run UpdateDataSet(dsCustOrd, 'Orders', ...)