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.Fie lds(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]

Re: Returning Identity.

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" <shuckle@hotmai lXXX.com> wrote in message
news:OpFH0fdOEH A.3312@tk2msftn gp13.phx.gbl...[color=blue]
> 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.Fie lds(0)
> end if
>
> If I run the same query in QueryAnalyzer, I do get a return value.
>
> Am I missing something?
>
> Thanks
> Tom
>
>[/color]

[TomB]

Re: Returning Identity.

Very good.

I should have just done a stored procedure.

Thanks
TomB

"Aaron Bertrand - MVP" <aaron@TRASHasp faq.com> wrote in message
news:uYCWxxdOEH A.3044@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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[/color]
the[color=blue]
> rest of the code might reveal other issues as well.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
>
>
> "TomB" <shuckle@hotmai lXXX.com> wrote in message
> news:OpFH0fdOEH A.3312@tk2msftn gp13.phx.gbl...[color=green]
> > 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.Fie lds(0)
> > end if
> >
> > If I run the same query in QueryAnalyzer, I do get a return value.
> >
> > Am I missing something?
> >
> > Thanks
> > Tom
> >
> >[/color]
>
>[/color]