Monday, March 12, 2012

Problems with foreign key

Hi, i have 3 tables.

Orders: {OrderNr(PK, autonumber), date}

Order_Line: {OrderNr(FK), ItemNr(FK), number}

Items: {ItemNr(PK), Name, ...}

How do I insert a new Order with a new order_line? I get this error:The INSERT statement conflicted with the FOREIGN KEY constraint

I can't figure out how to capture the autogenerated OrderNr.

Insert into thte Orders table, then insert into the order_line table like:

INSERT INTO Orders(date) VALUES (GETUTCDATE()) SELECT SCOPE_IDENTITY()

Get the new order number by executing the above with executescaler. Then use that in this:

INSERT INTO Order_Line(OrderNr,ItemNr,number) VALUES (@.OrderNr,@.ItemNr,@.number)

No comments:

Post a Comment