Return SCOPE_IDENTITY() without using CreateParam

adodb, asp-classic, vbscript

Solution

Applying `.NextRecordSet()` after the command did the trick:

<%  
    set Cmd = Server.CreateObject("ADODB.Command")
    Cmd.ActiveConnection = conn

    Cmd.CommandText = "INSERT INTO TABLE (NAME) VALUES ('test')  SELECT SCOPE_IDENTITY() AS ID"
    Cmd.CommandType = 1
    Cmd.CommandTimeout = 0
    Cmd.Prepared = true

    Set ScopeID = Cmd.Execute()
            ScopeID.NextRecordSet() // <---- Fix

    If ScopeID.EOF Then
        Response.Write "There was an Error in your request, Please try again"
        Response.End
    Else
        ID= ScopeID(0).Value
    End IF

    ScopeID.Close
    Set ScopeID = Nothing
    Set Cmd = Nothing

    Response.Write ID

%>

Problem

I'm trying this but getting an error `ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed.` on the line with this code `If ScopeID.EOF Then` Please do not answer to use the `CreateParam` method, Looking for a solution without this method. Thanks. ``` <% set Cmd = Server.CreateObject("ADODB.Command") Cmd.ActiveConnection = conn Cmd.CommandText = "INSERT INTO TABLE (NAME) VALUES ('test') SELECT SCOPE_IDENTITY() AS ID" Cmd.CommandType = 1 Cmd.CommandTimeout = 0 Cmd.Prepared = true Set ScopeID = Cmd.Execute() If ScopeID.EOF Then Response.Write "There was an Error in your request, Please try again" Response.End Else ID= ScopeID(0).Value End IF ScopeID.Close Set ScopeID = Nothing Set Cmd = Nothing Response.Write ID %> ```

Original source