can't get scope_identity after insert in classic asp

I have to do a quick and dirty sql session storage for classic asp so I have the following stored procedure that does the inserting work. When I run it from sql management studio or through asp code, it inserts fine but it's not returning the scope_identity. What's odd is that when I execute the procedure from code I'm getting "operation is not allowed when the object is closed" error message on the line where it checks for rs.eof.

create procedure InsertSessionValue

    @keyname varchar(50),
    @value varchar(max)

as

    declare @ID int
    delete from storedsessionsimple where datesaved < dateadd(d, -1, getdate())

    insert into dbo.StoredSessionSimple (SessionKey, SessionValue, DateSaved) values (@keyname, @value, getdate())

    set @ID = SCOPE_IDENTITY()

    return @ID

and the asp code is...

set cnLocal = GetConnection()
    cnLocal.open()

    set rs = cnLocal.execute("exec InsertSessionValue开发者_StackOverflow社区 '" & SessionKey & "','" & SessionValue & "'")
    if not rs.eof then
        id = rs(0)
    end if
    rs.close: set rs = nothing
    end if
    cnLocal.close: set cnLocal = nothing

---

Using RETURN in your stored proc is typically used for returning a status or error code. If you want to return data as a recordset from your stored proc, use SELECT instead of RETURN.

Update: It sounds like it's still not picking up that your stored procedure is now returning a recordset. According to the MSDN documentation for an ADO Command:

If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset.

---

Your stored procedure will need a couple changes if you want to return the SCOPE_IDENTITY in a recordset. The way it is currently implemented the IDENTITY is being returned as a RETURN_VALUE parameter, which you would have to retrieve using the ADO Command object. The return value will be the first parameter in the Command.Parameters collection. See the following article for an example.

If you want to return the results in a recordset then try changing the line

set @ID = SCOPE_IDENTITY()

to

SELECT SCOPE_IDENTITY() AS NEW_ID

and removing the return statement.

---

Just Use

set nocount on ; 

Your Sql Insert Query with ";"

set nocount off ;

And use bellow method

Set rsInsert = conn.execute(SQLQuery)


if not rsInsert.EOF then
   response.write(rsInsert(0))
end if

It works fine. I tested.