Return SCOPE_IDENTITY() without using CreateParam

Question

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

%>

Should your connection be set with a `Set Cmd.ActiveConnection = conn`? — Chris Farmer, Apr 10 '12 at 02:27
Check reply from Robert Wagner in the below link http://stackoverflow.com/questions/304281/classic-asp-getting-scope-identity-value-from-sql2005 — Ravi Vanapalli, Apr 10 '12 at 13:55

My Other Me · Answer 1 · 2012-04-11T07:22:17.517

5

Try this:

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) record-set for the actual insert statement if it is not "hidden" by SET NOCOUNT ON. I think the error message refers to the record-set being closed, and not the connection.

edited Apr 11 '12 at 07:22

answered Apr 11 '12 at 07:14

My Other Me

5,007
6
41
48

score 3 · Accepted Answer · answered Apr 14 '12 at 13:56

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

%>

Set ScopeID = ScopeID.NextRecordSet() is what did it for me – Graham Jan 29 '14 at 13:41 — Graham, Jan 29 '14 at 13:41

score 1 · Answer 3 · answered Apr 16 '12 at 08:56

1

If you are running a modern version of SQL Server (2005 on wards I think) you can use the output clause:

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

answered Apr 16 '12 at 08:56

My Other Me

5,007
6
41
48

score 0 · Answer 4 · answered Apr 10 '12 at 02:26

0

Is your connection open? It seems like that's what the error message is complaining about. You can't execute a command against a closed connection.

answered Apr 10 '12 at 02:26

Chris Farmer

24,974
34
121
164

You would say the the error for not having an open connection would be on the `Set ScopeID = Cmd.Execute()` line? – Guido Gautier Apr 10 '12 at 08:01
I believe the error message sounds like a closed connection. Does this error go away when you explicitly open your connection? – Chris Farmer Apr 10 '12 at 13:53
The same message is given for a closed recordset. In this case the query does not return an open recordset, hence the fault when trying to assess EOF. – Guido Gautier Apr 10 '12 at 14:06

score 0 · Answer 5 · answered Apr 10 '12 at 08:04

0

Split your query into two parts:

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

Notice the ;. I think the second part of your query is not executed.

answered Apr 10 '12 at 08:04

Guido Gautier

1,237
9
13

Return SCOPE_IDENTITY() without using CreateParam

5 Answers5