使用Adodb.Command返回SCOPE_IDENTITY（） [英] Returning SCOPE_IDENTITY() using Adodb.Command

问题描述

我有一个插入语句（存储过程），该语句在插入后返回SCOPE_IDENTITY（），但是，我试图弄清楚在使用 ADODB.Command 请求。

I have an Insert statement (Stored Procedure) that returns the SCOPE_IDENTITY() after insert, however, I am trying to figure out how to return it after I use an ADODB.Command request in Classic ASP.

SP的末尾包含以下内容：

The SP has this included at the end:

SET @LastID = SCOPE_IDENTITY() -- @LastID is an INT OUTPUT param.

在经典的ASP代码中处理插入查询时，这就是所谓的：

This is what is called when processing an Insert query in classic ASP code:

set SQLCOMM = Server.CreateObject("ADODB.Command")
SQLCOMM.ActiveConnection = CONNSTRING
SQLCOMM.CommandText = "Insert_SP_Returns_ScopeID"
SQLCOMM.CommandType = 1
SQLCOMM.CommandTimeout = 0
SQLCOMM.Prepared = true

LastIDParameter = SQLCOMM.CreateParameter("@LastID",adInteger,adParamOutput)
SQLCOMM.Parameters.Add(LastIDParameter)

SQLCOMM.Execute() 

LastID = LastIDParameter.Value

set SQLCOMM=Nothing

在我 set SQLCOMM = Nothing <之前我是否只是做这样的事情？ / code>？

Do I just do something like this before i set SQLCOMM=Nothing ?

NewID = SQLCOMM("LastID").Value

或...可以从 Adodb.Recordset中执行插入/更新存储过程而不是 ADODB.Command ？

OR... can an Insert/Update Stored Procedure be executed from a Adodb.Recordset instead of ADODB.Command instead?

    The example above gives the following error message:
    ADODB.Command error '800a0bb9'

    Arguments are of the wrong type, are out of acceptable range, 
    or are in conflict with one another. 

由于以下代码行而返回错误：

The error is being returned due to this line of code:

LastIDParameter = SQLCOMM.CreateParameter("@LastID",adInteger,adParamOutput)

推荐答案

您的命令调用存储过程的方法很好。您所要做的就是向命令对象添加一个额外的输出参数。 （多年来我没有做过任何vbscript，所以我不确定是否应该显式键入变量）。

Your command approach to calling the stored procedure is fine. All you have to do is add an extra output parameter to the command object. (I havent done any vbscript for years and so i am not sure whether you should be explicitly type your variables).

在VB脚本中

set SQLCOMM = Server.CreateObject("ADODB.Command") 
SQLCOMM.ActiveConnection = CONNSTRING 
SQLCOMM.CommandText = "Insert_SP_Returns_ScopeID" 
SQLCOMM.CommandType = 1 
SQLCOMM.CommandTimeout = 0 
SQLCOMM.Prepared = true 

Dim LastIDParameter
Dim LastID

LastIDParameter = SQLCOMM.CreateParameter("@LastID",adInteger,adParamOutput)
SQLCOMM.Parameters.Add(LastIDParameter)

SQLCOMM.Execute() 

LastID = LastIDParameter.Value

set SQLCOMM=Nothing 

然后在您的存储过程中

CREATE PROCEDURE Insert_SP_Returns_ScopeID

@Value1 int,
@Value2 int,
@LastID int OUTPUT

AS

INSERT INTO TableName(Value1,Value2) VALUES (@Value1,@Value2)

SET @LastID = SCOPE_IDENTITY()

编辑：您可能需要查找的值adInteger，adParamOutput并将其用作常量，可能无法为您的环境定义。如果是这样，使用...

You might need to look up the values of adInteger,adParamOutput and use those as the constants may not be defined for your environment. If so use...

SQLCOMM.CreateParameter("@LastID",3,2)

或定义常量。

这篇关于使用Adodb.Command返回SCOPE_IDENTITY（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！