MS Walkthrough: Saving Data to a Database (Multiple Tables)

[Magnus]

I'm testing walkthrough saving data to a Database (Multiple Tables).

http://msdn2.microsoft.com/en-us/library/4esb49b4(VS.80).aspx

In the famous Customer/Order example, I'm getting referential integrity
problems "The INSERT statement conflicted with the FOREIGN KEY
constraint..." if the identity/primary key column is "out of sync" with the
identity value in the database. I can easily acheive this f.ex. by pressing
add new record, cancel then add again and filling out customer and order
details. Obviously the customerId column in the order table has the wrong
values after the customer table has been inserted, as the customerid in the
customer table is updated to whatever number the database has given to the
record. Should I now update the columns referencing the customerID field
manually by code? Or how is a good way to do this?

I can't find any articles on how this can be effectively handled, can anyone
give me some pointers or give an example on how this should/could be
handled?

- Magnus

 

[sloan]

This is a good reason to use GUID's.

However, I have a INT (identity) work around.


Create your Customers table (DataTable) and tack on a "OrdinalID"


CustomerID,LastName,FirstName,OrdinalID
123,'Smith','John',1
234,'Jones','Mary',2

Create an Orders (DataTable)
OrderID,ProductID,Quanity,OrdinalID
0,1001,3,1
0,1002,2,1
0,1001,8,2
0,1003,5,2

The second table says:
John Smith (OrdinalID=1) ordered a productid of 1001, and wanted 3(qty) of
them.
John Smith (OrdinalID=1) ordered a productid of 1002, and wanted 2(qty) of
them.
Mary Jones (OrdinalID=2) ordered a productid of 1001, and wanted 8(qty) of
them.
Mary Jones (OrdinalID=2) ordered a productid of 1003, and wanted 5(qty) of
them.

Ok...ship that into the stored procedure.

Put everything into @variable or @temp tables.

When you create your Customers, you'll get back the @@IDENTITY, lets say you
get back 12221 as the IDENTITY.

If you did a bulk insert, then that means.

MaryJones was 12221, and JohnSmith was 12220.

Update the @variable or #temp table ....


123,'Smith','John',12220
234,'Jones','Mary',12221

0,1001,3,12220
0,1002,2,12220
0,1001,8,12221
0,1003,5,12221

Now you can insert Orders, with the correct relationship to what got
inserted.

You'll have to do some basic math on the @@IDENTITY, but you figure it out.

The inserts, basic math/updates will have to be in a TRANS, or you'll screw
yourself.


I hope that kinda makes sense. I've given a loose guideline, not an exact
solution. The key is to ... have some kind of relationship between Customer
and Order (OrdinalID) before you push the data in.
THEN insert the customers, find the @@IDENTITY, do some
math/updates........and inserts on the secondary (Orders) table.

...