INSERT操作（SQL / ASP）上的返回键（标识） [英] Return Key (identity) on INSERT operation (SQL/ASP)

问题描述

我有以下SP：


CREATE PROCEDURE Orders_CreateOrder

@CustomerID int，

@LocationID int，

@CustOrderNo varchar（20），

@OrderDate smalldatetime

AS


插入订单（CustomerID） ，LocationID，CustOrderNo，OrderDate）

值（@ CustomerID，@ LocationID，@ CustomerOrderNo，@ OrderDate）

如果@@ RowCount> 0

选择Scope_Identity（）作为OrderID

GO


SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO


理论上说如果Insert操作成功，那么键是

（Scope_Identity）被返回。


如果我在QA中尝试以下操作，它可以工作：Exec Orders_CreateOrder 1,1，

''123d''， ''20040323''


但是，在我的ASP代码中，我收到一个错误：

关闭对象时不允许操作。 ;

ASP片段：

sSQL =" Exec Orders_CreateOrder" &安培; _

Request.Form（" CustomerID"）& ， &安培; _

Request.Form（" Depot"）& "，''" &安培; _

Request.Form（" CustOrderNo"）& ''，''" &安培; _

ISODate（Request.Form（" OrderDate"））& "''"


设置rsOrders = oConn.Execute（sSQL）

有什么想法吗？


干杯


克里斯

I have the following SP:

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)
If @@RowCount > 0
Select Scope_Identity() as OrderID
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The theory is that if the Insert operation is successful, the key
(Scope_Identity) is returned.

If I try the following in QA, it works: Exec Orders_CreateOrder 1, 1,
''123d'', ''20040323''

However, in my ASP code, I get an error:
"Operation is not allowed when the object is closed."
ASP Snippet:
sSQL = "Exec Orders_CreateOrder " & _
Request.Form("CustomerID") & ", " & _
Request.Form("Depot") & ", ''" & _
Request.Form("CustOrderNo") & "'', ''" & _
ISODate(Request.Form("OrderDate")) & "''"

Set rsOrders = oConn.Execute(sSQL)
Any ideas?

Cheers

Chris

推荐答案

在你的代码开头添加SET NOCOUNT ON。


-

Tibor Karaszi，SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp

" CJM" < CJ ***** @ yahoo.co.uk>在留言中写道

新闻：************** @ tk2msftngp13.phx.gbl ...

Add SET NOCOUNT ON in the beginning of your proc code.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"CJM" <cj*****@yahoo.co.uk> wrote in message
news:Of**************@tk2msftngp13.phx.gbl...

我有以下SP ：

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int，
@LocationID int，
@CustOrderNo varchar（20），
@ OrderDate smalldatetime
AS

插入订单（CustomerID，LocationID，CustOrderNo，OrderDate）
值（@ CustomerID，@ LocationID，@ CustomerOrderNo，@ OrderDate）
如果@@ RowCount> 0
选择Scope_Identity（）作为OrderID


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
（Scope_Identity）。

如果我在QA中尝试以下操作，它可以工作：Exec Orders_CreateOrder 1,1，
''123d''，''20040323''

然而，在我的ASP代码中，我收到一个错误：
对象关闭时不允许操作。"

ASP片段：
sSQL =" Exec Orders_CreateOrder" &安培; _
Request.Form（CustomerID）& ， &安培; _
Request.Form（" Depot"）& "，''" &安培; _
Request.Form（" CustOrderNo"）& ''，''" &安培; _ = / ISODate（Request.Form（" OrderDate"））& 设置rsOrders = oConn.Execute（sSQL）

任何想法？

干杯
Chris

I have the following SP:

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)
If @@RowCount > 0
Select Scope_Identity() as OrderID
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The theory is that if the Insert operation is successful, the key
(Scope_Identity) is returned.

If I try the following in QA, it works: Exec Orders_CreateOrder 1, 1,
''123d'', ''20040323''

However, in my ASP code, I get an error:
"Operation is not allowed when the object is closed."
ASP Snippet:
sSQL = "Exec Orders_CreateOrder " & _
Request.Form("CustomerID") & ", " & _
Request.Form("Depot") & ", ''" & _
Request.Form("CustOrderNo") & "'', ''" & _
ISODate(Request.Form("OrderDate")) & "''"

Set rsOrders = oConn.Execute(sSQL)
Any ideas?

Cheers

Chris

Tibor是正确的，您需要在

开头存储时添加SET NOCOUNT ON程序（在这种情况下，在AS之后的行上）。 FWIW，我将这个

行添加到我创建的每个存储过程中。


-

Aaron Bertrand

SQL Server MVP
http://www.aspfaq.com/

" CJM" < CJ ***** @ yahoo.co.uk>在留言中写道

新闻：************** @ tk2msftngp13.phx.gbl ...

Tibor is correct, you need to add SET NOCOUNT ON at the beginning of your
stored procedure (in this case, on the line after AS). FWIW, I add this
line to EVERY stored procedure I create.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"CJM" <cj*****@yahoo.co.uk> wrote in message
news:Of**************@tk2msftngp13.phx.gbl...

我有以下SP ：

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int，
@LocationID int，
@CustOrderNo varchar（20），
@ OrderDate smalldatetime
AS

插入订单（CustomerID，LocationID，CustOrderNo，OrderDate）
值（@ CustomerID，@ LocationID，@ CustomerOrderNo，@ OrderDate）
如果@@ RowCount> 0
选择Scope_Identity（）作为OrderID


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
（Scope_Identity）。

如果我在QA中尝试以下操作，它可以工作：Exec Orders_CreateOrder 1,1，
''123d''，''20040323''

然而，在我的ASP代码中，我收到一个错误：
对象关闭时不允许操作。"

ASP片段：
sSQL =" Exec Orders_CreateOrder" &安培; _
Request.Form（CustomerID）& ， &安培; _
Request.Form（" Depot"）& "，''" &安培; _
Request.Form（" CustOrderNo"）& ''，''" &安培; _ = / ISODate（Request.Form（" OrderDate"））& 设置rsOrders = oConn.Execute（sSQL）

任何想法？

干杯
Chris

I have the following SP:

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)
If @@RowCount > 0
Select Scope_Identity() as OrderID
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The theory is that if the Insert operation is successful, the key
(Scope_Identity) is returned.

If I try the following in QA, it works: Exec Orders_CreateOrder 1, 1,
''123d'', ''20040323''

However, in my ASP code, I get an error:
"Operation is not allowed when the object is closed."
ASP Snippet:
sSQL = "Exec Orders_CreateOrder " & _
Request.Form("CustomerID") & ", " & _
Request.Form("Depot") & ", ''" & _
Request.Form("CustOrderNo") & "'', ''" & _
ISODate(Request.Form("OrderDate")) & "''"

Set rsOrders = oConn.Execute(sSQL)
Any ideas?

Cheers

Chris

我的

这篇关于INSERT操作（SQL / ASP）上的返回键（标识）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！