订单查询及订单明细

发布于 2024-11-01 08:41:23 字数 623 浏览 1 评论 0原文

订单表

OrderId
CustomerId
Date
Total

OrderDetails

OrderDetailId
OrderId
ProductId
Quantity
UnitPrice

我正在使用订单查询

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

我多次执行 OrderDetail 查询。

DECLARE @OrderID int
SET @OrderID = SCOPE_IDENTITY()

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

这里的问题是，在我第二次执行此查询后，@OrderID 的值发生了变化，并且不再与 Orders 表中的外键匹配。那么我应该怎么做才能解决这个问题？

原文

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.

DECLARE @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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迷迭香的记忆 2024-11-08 08:41:23

I. 您必须使用相同的连接来：

插入订单：

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

检索SCOPE_IDENTITY()并存储它在代码中的某个地方（您没有提供 .NET 代码，因此我可以给您更准确的建议）：

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

II. 然后（是否相同连接）：

插入 OrderDetails 使用：

--连接：

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

-- 或参数：

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

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

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)

回复收藏 0 原文

缱倦旧时光 2024-11-08 08:41:23

如果您的订单详细信息作为身份字段，那么当您调用 INSERT INTO dbo.OrderDetail 时，它将更改 SCOPE_IDENTITY。您需要为每个订单条目设置或 @OrderID 一次，并且在向订单表添加另一列之前不要再次调用 SCOPE_IDENTITY。

--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)

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)

回复收藏 0 原文

~没有更多了~