Returning SCOPE_IDENTITY() using 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.

The SP has this included at开发者_JAVA百科 the end:

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

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

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

NewID = SQLCOMM("LastID").Value

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)

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

In VB script

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 

And then in your stored procedure.

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

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

or define the constants.