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.

 

[TomB]

Very good.

I should have just done a stored procedure.

Thanks
TomB