Order query and OrderDetail

Order Table

OrderId
CustomerId
Date
Total

OrderDetails

OrderDetailId
OrderId
ProductId
Quantity
UnitPrice

i am using Order query

INSERT INTO dbo.Orders (CustomerId, Date, Total)
VALUES (1, 2011-04-10, 50000);

I am executing OrderDetail query many times.

DECL开发者_运维技巧ARE @OrderID int
SET @OrderID = SCOPE_IDENTITY()

INSERT INTO dbo.OrderDetail (OrderId,ProductDescriptionId,Quantity,UnitPrice)
VALUES (@OrderID,11,2,50000)

The problem here is that the value of @OrderID changes, and no longer matches the foreign key from the Orders table after i execute this query for the second time. so what should i do to fix this?

---

I. You have to use the same connection to:

Insert the Order:

INSERT INTO dbo.Orders (CustomerId, Date, Total)
VALUES (1, 2011-04-10, 50000);

Retrieve SCOPE_IDENTITY() and store it in some place in your code (you havn't supplied the .NET code, so I could give you more precise advice) :

command.CommandText = "SELECT SCOPE_IDENTITY()";
int lastOrderId = (int)command.ExecuteScalar();

II. Then (same connection or not):

Insert OrderDetails using:

-- concatenation:

"INSERT INTO dbo.OrderDetail (OrderId,ProductDescriptionId,Quantity,UnitPrice)
 VALUES ('" + lastOrderId.ToString() + "',11,2,50000)"

-- or Parameters:

command.Parameters.Add("@OrderID", SqlDbType.Int);
command.Parameters("@OrderID").Value = lastOrderId;

INSERT INTO dbo.OrderDetail (OrderId,ProductDescriptionId,Quantity,UnitPrice)
VALUES (@OrderID,11,2,50000)

---

If your order detail as an identity field, then when you call INSERT INTO dbo.OrderDetail it's going to change SCOPE_IDENTITY. You'd need to set or @OrderID once for each Order entry and not call SCOPE_IDENTITY again until you've added another column to your Orders table.

--Run this once per order
INSERT INTO dbo.Orders (CustomerId, Date, Total)
VALUES (1, 2011-04-10, 50000);

--Run this once per order
DECLARE @OrderID int
SET @OrderID = SCOPE_IDENTITY()

--Run this once per orderdetail
INSERT INTO dbo.OrderDetail (OrderId,ProductDescriptionId,Quantity,UnitPrice)
VALUES (@OrderID,11,2,50000)