Returning Identity.

[TomB]

I tried the following

Set RS=CN.Execute("INSERT into tableX yada yadayada ; SELECT
SCOPE_IDENTITY()")

Using Profiler, I see the statement go through.
But ASP gives me an error saying RS is not an object.
"Operation is not allowed when the object is closed."

In these lines
if not RS.EOF then
theNewId=RS.Fields(0)
end if

If I run the same query in QueryAnalyzer, I do get a return value.

Am I missing something?

Thanks
Tom

[Aaron Bertrand - MVP]

You forgot SET NOCOUNT ON. The n row(s) affected message from the INSERT
fools ADO into believing there is an interim resultset. Though, seeing the
rest of the code might reveal other issues as well.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:Op**************@tk2msftngp13.phx.gbl...

I tried the following

Set RS=CN.Execute("INSERT into tableX yada yadayada ; SELECT
SCOPE_IDENTITY()")

Using Profiler, I see the statement go through.
But ASP gives me an error saying RS is not an object.
"Operation is not allowed when the object is closed."

In these lines
if not RS.EOF then
theNewId=RS.Fields(0)
end if

If I run the same query in QueryAnalyzer, I do get a return value.

Am I missing something?

Thanks
Tom

[TomB]

Very good.

I should have just done a stored procedure.

Thanks
TomB

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:uY**************@TK2MSFTNGP10.phx.gbl...

You forgot SET NOCOUNT ON. The n row(s) affected message from the INSERT
fools ADO into believing there is an interim resultset. Though, seeing the rest of the code might reveal other issues as well.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:Op**************@tk2msftngp13.phx.gbl...

I tried the following

Set RS=CN.Execute("INSERT into tableX yada yadayada ; SELECT
SCOPE_IDENTITY()")

Using Profiler, I see the statement go through.
But ASP gives me an error saying RS is not an object.
"Operation is not allowed when the object is closed."

In these lines
if not RS.EOF then
theNewId=RS.Fields(0)
end if

If I run the same query in QueryAnalyzer, I do get a return value.

Am I missing something?

Thanks
Tom