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
%>
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
%>
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.
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')"
Is your connection open? Sounds like what the error message is complaining about. You cannot execute a command against a closed connection.
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.