DB question: Copying ID

Hi,

I'm trying to insert one value (an order) into the table Order and (via a for-loop) all the products in that order in the table Product, hence, one order can have multiple products (and must have at least one). I have an automatically increased value for the OrderID as the primary key for Order, and I have a foreign key named OrderID in the Product table. So far, I _think_ everything's logically correct.

However, I don't understand how to retrieve the OrderID to be able to insert it in the Product table upon insertion. I guess this is done all the time, but the only solution I can think of is to make a new SQL Command, asking for the just created OrderID to use it in the SQL Command for the products' for-loop. I'm sure that's a bad idea. :-)

Can I use relationships or so to make this automatically updated (that is, to have the Product table "check for" the OrderID and insert the OrderID upon insertion of the Product row(s))?

I hope this is clear to you. Thanks in advance for all help!

Pettrer

If you want to get the last ID after insertion then use the Scope_Identity() to get that.

Regards