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 reque

Solution 1:

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 = trueDim 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.

CREATEPROCEDURE Insert_SP_Returns_ScopeID

@Value1int,
@Value2int,
@LastIDint OUTPUT

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