Clever Geek Handbook

Return SCOPE_IDENTITY () without using CreateParam

I am trying to do this, but I get an error ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed.

on the line with this codeIf ScopeID.EOF Then

Please do not answer to use the method CreateParam

Finding a solution without this method. Thank.

<%  
    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

%>
+3


source to share


5 answers


Application .NextRecordSet()

after the command has done 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

%>
+3


source


Try the following:

Cmd.CommandText = "SET NOCOUNT ON; INSERT INTO TABLE (NAME) VALUES ('test'); SET NOCOUNT OFF; SELECT SCOPE_IDENTITY() AS ID"


As I understand it, SQL Server returns a (empty) recordset for the actual insert statement, unless it is "hidden" with SET NOCOUNT ON. I think the error message refers to the closing of the recordset and not the connection.

+5


source


If you are using modern version of SQL Server (2005) I think you can use the output clause:

Cmd.CommandText = "insert into [dbo].[test] (name) output inserted.id values ('test')"
+1


source


Is your connection open? Sounds like what the error message is complaining about. You cannot execute a command against a closed connection.

0


source


Divide the request into two parts:

INSERT INTO TABLE (NAME) VALUES ('test'); SELECT SCOPE_IDENTITY() AS ID

Pay attention to ;

. I think the second part of your request has failed.

0


source






More articles:


All Articles