NextRecordset

A Form has 2 textboxes - one for entering username & the other one for
entering password - both of which are populated in a SQL Server DB
table. The DB table has 3 columns - UID (Identity), Username &
Password.

Using the NextRecordset method of the Recordset object, I want to
retrieve the UID for the just inserted record. This is how I did it:

============================================
<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
objConn.Open 'blah...blah...blah....

Dim strSQL
strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
"');SELECT UID From Table1 WHERE UserName='" &
Request.Form("txtUserName") & "' AND Password='" &
Request.Form("txtPassword") & "';SELECT @@IDENTITY"

Dim objRS
Set objRS=objConn.Execute(strSQL).NextRecordset

Dim iUID
iUID=objRS(0).Value

Response.Write("Your ID is " & iUID)
%>
============================================

Please note that the SQL query shown above is just for the sake of
brevity otherwise it doesn't make much sense especially the 2 SELECT
queries since both of them effectively retrieve the same record!

As such the above code retrieves the correct UID from the DB table.but
if I replace the line

============================================
iUID=objRS(0).Value
============================================

with

============================================
iUID=objRS(1).Value
============================================

the following error gets generated:

============================================
Item cannot be found in the collection corresponding to the requested
name or ordinal.
============================================

Why? objRS(0) is the recordset from the first SELECT query; so isn't
objRS(1) the recordset from the second SELECT query?

Thanks,

Ron

Re: NextRecordset

RN1 wrote:
> A Form has 2 textboxes - one for entering username & the other one for
> entering password - both of which are populated in a SQL Server DB
> table. The DB table has 3 columns - UID (Identity), Username &
> Password.
>
> Using the NextRecordset method of the Recordset object, I want to
> retrieve the UID for the just inserted record. This is how I did it:
>
> ============================================
> <%
> Dim objConn
> Set objConn=Server.CreateObject("ADODB.CONNECTION")
> objConn.Open 'blah...blah...blah....
>
> Dim strSQL
> strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
> Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
> "');SELECT UID From Table1 WHERE UserName='" &
> Request.Form("txtUserName") & "' AND Password='" &
> Request.Form("txtPassword") & "';SELECT @@IDENTITY"
>

You think you're getting only two recordsets. Actually you are getting
three. The INSERT is generating a "x rows affected" message that is being
returned as a closed recordset. The best way to deal with this is to start
with a SET NOCOUNT ON statement to suppress the generation of these
informational messages:

strSQL="SET NOCOUNT ON;" & _
"INSERT INTO Table1 (UserName,Password) VALUES('" & _
Request.Form("txtUserName") & _
"','" & Request.Form("txtPassword") & _
"');SELECT UID From Table1 WHERE UserName='" & _
Request.Form("txtUserName") & "' AND Password='" & _
Request.Form("txtPassword") & "';SELECT @@IDENTITY"


> Dim objRS
> Set objRS=objConn.Execute(strSQL).NextRecordset
>
> Dim iUID
> iUID=objRS(0).Value
>
> Response.Write("Your ID is " & iUID)
> %>
> ============================================
>
>
> As such the above code retrieves the correct UID from the DB table.but
> if I replace the line
>
> ============================================
> iUID=objRS(0).Value
> ============================================
>
> with
>
> ============================================
> iUID=objRS(1).Value
> ============================================
>
> the following error gets generated:
>
> ============================================
> Item cannot be found in the collection corresponding to the requested
> name or ordinal.
> ============================================
>
> Why? objRS(0) is the recordset from the first SELECT query; so isn't
> objRS(1) the recordset from the second SELECT query?
>
No. The number inside the parenthseses refers to the field index. When you
call NextRecordset, you get a brand new recordset with a whole new set of
Field objects. SELECT @@IDENTITY (You should use SELECT "SCOPE_IDENTITY(0)"
by the way ) returns a single field, so get its value by using objRS(0).

Best practice is to use a stored procedure with an output parameter, but i
won't go into detail about that unless you ask me to.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: NextRecordset

Bob Barrows [MVP] wrote:
> RN1 wrote:
>> A Form has 2 textboxes - one for entering username & the other one
>> for entering password - both of which are populated in a SQL Server
>> DB table. The DB table has 3 columns - UID (Identity), Username &
>> Password.
>>
>> Using the NextRecordset method of the Recordset object, I want to
>> retrieve the UID for the just inserted record. This is how I did it:
>>
>> ============================================
>> <%
>> Dim objConn
>> Set objConn=Server.CreateObject("ADODB.CONNECTION")
>> objConn.Open 'blah...blah...blah....
>>
>> Dim strSQL
>> strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
>> Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
>> "');SELECT UID From Table1 WHERE UserName='" &
>> Request.Form("txtUserName") & "' AND Password='" &
>> Request.Form("txtPassword") & "';SELECT @@IDENTITY"
>>
>
> You think you're getting only two recordsets. Actually you are getting
> three.
OH wait, this was dumb. You are using NextRecordset to deal with the
informational message, duh.
My statement still stands though: use SET NOCOUNT to suppress the message
instead of generating extra network traffic. And again, best practice is to
use a stored procedure with an output parameter.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: NextRecordset

On Dec 22, 4:48=A0pm, "Bob Barrows [MVP]"
wrote:
> Bob Barrows [MVP] wrote:
> > RN1 wrote:
> >> A Form has 2 textboxes - one for entering username & the other one
> >> for entering password - both of which are populated in a SQL Server
> >> DB table. The DB table has 3 columns - UID (Identity), Username &
> >> Password.
>
> >> Using the NextRecordset method of the Recordset object, I want to
> >> retrieve the UID for the just inserted record. This is how I did it:
>
> >> ==================== ===3D=
==================== =3D
> >> <%
> >> =A0 =A0Dim objConn
> >> =A0 =A0Set objConn=3DServer.CreateObject("ADODB.CONNECTION")
> >> =A0 =A0objConn.Open 'blah...blah...blah....
>
> >> =A0 =A0Dim strSQL
> >> =A0 =A0strSQL=3D"INSERT INTO Table1 (UserName,Password) VALUES('" &
> >> Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
> >> "');SELECT UID From Table1 WHERE UserName=3D'" &
> >> Request.Form("txtUserName") & "' AND Password=3D'" &
> >> Request.Form("txtPassword") & "';SELECT @@IDENTITY"
>
> > You think you're getting only two recordsets. Actually you are getting
> > three.
>
> OH wait, this was dumb. You are using NextRecordset to deal with the
> informational message, duh.
> My statement still stands though: use SET NOCOUNT to suppress the message
> instead of generating extra network traffic. And again, best practice is t=
o
> use a stored procedure with an output parameter.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"- Hide quoted text -
>
> - Show quoted text -

Yes Bob, you are very much correct.....a stored procedure would indeed
be a better & wise option. However just to use the NextRecordset
method of the Recordset object, I cited the example.

Anyway if I change the SQL query cited in my first post to this:

==================== =====3D=
===============3D
strSQL=3D"INSERT INTO Table1 (UserName,Password) VALUES('" &
Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
"');SELECT ID From Table1 WHERE UserName=3D'" &
Request.Form("txtUserName") & "' AND Password=3D'" &
Request.Form("txtPassword") & "';SELECT UserName FROM Table1 WHERE
Password=3D'" & Request.Form("txtPassword") & "'"
==================== =====3D=
================

i.e. the 2nd SELECT query now retrieves the UserName (instead of the
UID), then how do I retrieve the UserName using the 2nd SELECT query
using the NextRecordset method?

Of course, the above SQL query again doesn't make any sense since both
the SELECT queries can be clubbed into one SELECT query to get the UID
& UserName. In fact, the UserName can be retrieved using Request.Form
itself! So please allow me to reiterate that the SQL queries I have
been citing is just for the sake of using the NextRecordset method.

Thanks,

Ron

Re: NextRecordset

RN1 wrote:
> On Dec 22, 4:48 pm, "Bob Barrows [MVP]"
> wrote:
>> Bob Barrows [MVP] wrote:
>>> RN1 wrote:
>>>> A Form has 2 textboxes - one for entering username & the other one
>>>> for entering password - both of which are populated in a SQL Server
>>>> DB table. The DB table has 3 columns - UID (Identity), Username &
>>>> Password.
>>
>>>> Using the NextRecordset method of the Recordset object, I want to
>>>> retrieve the UID for the just inserted record. This is how I did
>>>> it:
>>
>>>> ============================================
>>>> <%
>>>> Dim objConn
>>>> Set objConn=Server.CreateObject("ADODB.CONNECTION")
>>>> objConn.Open 'blah...blah...blah....
>>
>>>> Dim strSQL
>>>> strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
>>>> Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
>>>> "');SELECT UID From Table1 WHERE UserName='" &
>>>> Request.Form("txtUserName") & "' AND Password='" &
>>>> Request.Form("txtPassword") & "';SELECT @@IDENTITY"
>>
>>> You think you're getting only two recordsets. Actually you are
>>> getting three.
>>
>> OH wait, this was dumb. You are using NextRecordset to deal with the
>> informational message, duh.
>> My statement still stands though: use SET NOCOUNT to suppress the
>> message instead of generating extra network traffic. And again, best
>> practice is to use a stored procedure with an output parameter.
>>
>
> Yes Bob, you are very much correct.....a stored procedure would indeed
> be a better & wise option. However just to use the NextRecordset
> method of the Recordset object, I cited the example.
>
> Anyway if I change the SQL query cited in my first post to this:
>
> ========================================
> strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
> Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
> "');SELECT ID From Table1 WHERE UserName='" &
> Request.Form("txtUserName") & "' AND Password='" &
> Request.Form("txtPassword") & "';SELECT UserName FROM Table1 WHERE
> Password='" & Request.Form("txtPassword") & "'"
> =========================================
>
> i.e. the 2nd SELECT query now retrieves the UserName (instead of the
> UID), then how do I retrieve the UserName using the 2nd SELECT query
> using the NextRecordset method?
>
> Of course, the above SQL query again doesn't make any sense since both
> the SELECT queries can be clubbed into one SELECT query to get the UID
> & UserName. In fact, the UserName can be retrieved using Request.Form
> itself! So please allow me to reiterate that the SQL queries I have
> been citing is just for the sake of using the NextRecordset method.
>
Again. Use SET NOCOUNT ON. Get into the habit. Also, you should get into the
habit of using parameters. I will do that for this example because i abhor
dynamic sql.

You have to call NextRecordset for each resultset returned by your sql
statements.

Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
objConn.Open 'blah...blah...blah....

Dim strSQL
strSQL="SET NOCOUNT ON;" & _
"INSERT INTO Table1 (UserName,Password) VALUES(?,?)" & _
";SELECT ID From Table1 WHERE UserName=? AND " & _
"Password=?;SELECT UserName " & _
" FROM Table1 WHERE Password=?"

Dim cmd, arParms
arParms=Array(Request.Form("txtUserName"), _
Request.Form("txtPassword"), _
Request.Form("txtUserName"), _
Request.Form("txtPassword"), _
Request.Form("txtPassword"))

set cmd=createobject("adodb.command")
with cmd
.commandtext=strSQL
set .ActiveConnection = objConn
.CommandType = 1 'adCmdText
End With
Dim objRS
Set objRS=cmd.Execute(,arParms)
Response.Write "ID contains " & objRS(0) & "
"
Set objRS = objRS.NextRecordset
if not objRS is nothing then
if objRS.State = 1 then '1=adStateOpen
Response.Write "UserName contains " & _
objRS("UserName") & "
"
end if
end if
objRS.close:set objRS = nothing
objConn.close:set objConn=nothing

Notes:
1. The NextRecordset method returns a new recordset if a resultset is
pending. if there are no pending resultsets, it returns Nothing

2. Whether previous resultsets are still accessible depends on the cursor
location. With a server-side cursor, the current resultset is always
discarded when calling NextRecordset. So with this code:
objConn.cursorlocation = 2 'adUseServer
Set objRS=cmd.Execute(,arParms)
Set objRS2=objRS.NextRecordset

objRS will be set to Nothing. However, with this code:
objConn.cursorlocation = 3 'adUseClient
Set objRS=cmd.Execute(,arParms)
Set objRS2=objRS.NextRecordset

objRS will still contain the first recordset and objRS2 will contain the
second.

3. Do not depend on objRS.State to tell you if you've reached the last
resultset. Without SET NOCOUNT, the informational messages will be sent as
closed recordsets, and there may be more resultsets pending. Keep calling
NextRecordset until it returns Nothing

4. I strongly discourage the use of multiple resultsets in ASP if they can
be avoided. Your goal should be to reduce network traffic and extra
processing of results, not increase them.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: NextRecordset

On Dec 23, 9:18=A0pm, "Bob Barrows [MVP]"
wrote:
> RN1 wrote:
> > On Dec 22, 4:48 pm, "Bob Barrows [MVP]"
> > wrote:
> >> Bob Barrows [MVP] wrote:
> >>> RN1 wrote:
> >>>> A Form has 2 textboxes - one for entering username & the other one
> >>>> for entering password - both of which are populated in a SQL Server
> >>>> DB table. The DB table has 3 columns - UID (Identity), Username &
> >>>> Password.
>
> >>>> Using the NextRecordset method of the Recordset object, I want to
> >>>> retrieve the UID for the just inserted record. This is how I did
> >>>> it:
>
> >>>> ==================== ===3D=
==================== =3D
> >>>> <%
> >>>> Dim objConn
> >>>> Set objConn=3DServer.CreateObject("ADODB.CONNECTION")
> >>>> objConn.Open 'blah...blah...blah....
>
> >>>> Dim strSQL
> >>>> strSQL=3D"INSERT INTO Table1 (UserName,Password) VALUES('" &
> >>>> Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
> >>>> "');SELECT UID From Table1 WHERE UserName=3D'" &
> >>>> Request.Form("txtUserName") & "' AND Password=3D'" &
> >>>> Request.Form("txtPassword") & "';SELECT @@IDENTITY"
>
> >>> You think you're getting only two recordsets. Actually you are
> >>> getting three.
>
> >> OH wait, this was dumb. You are using NextRecordset to deal with the
> >> informational message, duh.
> >> My statement still stands though: use SET NOCOUNT to suppress the
> >> message instead of generating extra network traffic. And again, best
> >> practice is to use a stored procedure with an output parameter.
>
> > Yes Bob, you are very much correct.....a stored procedure would indeed
> > be a better & wise option. However just to use the NextRecordset
> > method of the Recordset object, I cited the example.
>
> > Anyway if I change the SQL query cited in my first post to this:
>
> > ==================== =====
================
> > strSQL=3D"INSERT INTO Table1 (UserName,Password) VALUES('" &
> > Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
> > "');SELECT ID From Table1 WHERE UserName=3D'" &
> > Request.Form("txtUserName") & "' AND Password=3D'" &
> > Request.Form("txtPassword") & "';SELECT UserName FROM Table1 WHERE
> > Password=3D'" & Request.Form("txtPassword") & "'"
> > ==================== =====
=================3D
>
> > i.e. the 2nd SELECT query now retrieves the UserName (instead of the
> > UID), then how do I retrieve the UserName using the 2nd SELECT query
> > using the NextRecordset method?
>
> > Of course, the above SQL query again doesn't make any sense since both
> > the SELECT queries can be clubbed into one SELECT query to get the UID
> > & UserName. In fact, the UserName can be retrieved using Request.Form
> > itself! So please allow me to reiterate that the SQL queries I have
> > been citing is just for the sake of using the NextRecordset method.
>
> Again. Use SET NOCOUNT ON. Get into the habit. Also, you should get into t=
he
> habit of using parameters. I will do that for this example because i abhor=

> dynamic sql.
>
> You have to call NextRecordset for each resultset returned by your sql
> statements.
>
> =A0 =A0 Dim objConn
> =A0 =A0 Set objConn=3DServer.CreateObject("ADODB.CONNECTION")
> =A0 =A0 objConn.Open 'blah...blah...blah....
>
> =A0 =A0 Dim strSQL
> strSQL=3D"SET NOCOUNT ON;" & _
> "INSERT INTO Table1 (UserName,Password) VALUES(?,?)" & _
> ";SELECT ID From Table1 WHERE UserName=3D? AND =A0" & _
> "Password=3D?;SELECT UserName " & _
> " FROM Table1 WHERE Password=3D?"
>
> Dim cmd, arParms
> arParms=3DArray(Request.Form("txtUserName"), _
> Request.Form("txtPassword"), _
> Request.Form("txtUserName"), _
> Request.Form("txtPassword"), _
> Request.Form("txtPassword"))
>
> set cmd=3Dcreateobject("adodb.command")
> with cmd
> =A0 =A0 .commandtext=3DstrSQL
> =A0 =A0 set .ActiveConnection =3D objConn
> =A0 =A0 .CommandType =3D 1 'adCmdText
> End With
> =A0 =A0 Dim objRS
> =A0 =A0 Set objRS=3Dcmd.Execute(,arParms)
> Response.Write "ID contains " & objRS(0) & "
"
> Set objRS =3D objRS.NextRecordset
> if not objRS is nothing then
> =A0 =A0 if objRS.State =3D 1 then '1=3DadStateOpen
> =A0 =A0 =A0 =A0 Response.Write "UserName contains " & _
> =A0 =A0 =A0 =A0 =A0 =A0 objRS("UserName") & "
"
> =A0 =A0 end if
> end if
> objRS.close:set objRS =3D nothing
> objConn.close:set objConn=3Dnothing
>
> Notes:
> 1. The NextRecordset method returns a new recordset if a resultset is
> pending. if there are no pending resultsets, it returns Nothing
>
> 2. Whether previous resultsets are still accessible depends on the cursor
> location. With a server-side cursor, the current resultset is always
> discarded when calling NextRecordset. So with this code:
> objConn.cursorlocation =3D 2 'adUseServer
> Set objRS=3Dcmd.Execute(,arParms)
> Set objRS2=3DobjRS.NextRecordset
>
> objRS will be set to Nothing. However, with this code:
> objConn.cursorlocation =3D 3 'adUseClient
> Set objRS=3Dcmd.Execute(,arParms)
> Set objRS2=3DobjRS.NextRecordset
>
> objRS will still contain the first recordset and objRS2 will contain the
> second.
>
> 3. Do not depend on objRS.State to tell you if you've reached the last
> resultset. Without SET NOCOUNT, the informational messages will be sent as=

> closed recordsets, and there may be more resultsets pending. Keep calling
> NextRecordset until it returns Nothing
>
> 4. I strongly discourage the use of multiple resultsets in ASP if they can=

> be avoided. Your goal should be to reduce network traffic and extra
> processing of results, not increase them.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"- Hide quoted text -
>
> - Show quoted text -

Thanks so very much, Bob, for the in-depth explanation. It was really
very kind of you to invest so much time & energy to help me out.
Thanks a lot once again.

Now I get the point...the NextRecordset method has to be called for
each recordset that the SQL query returns.

Regards,

Ron

Re: NextRecordset

"Bob Barrows [MVP]" wrote in message
news:u1air9XRIHA.4740@TK2MSFTNGP02.phx.gbl...
> RN1 wrote:
>> On Dec 22, 4:48 pm, "Bob Barrows [MVP]"
>> wrote:
>>> Bob Barrows [MVP] wrote:
>>>> RN1 wrote:
>>>>> A Form has 2 textboxes - one for entering username & the other one
>>>>> for entering password - both of which are populated in a SQL Server
>>>>> DB table. The DB table has 3 columns - UID (Identity), Username &
>>>>> Password.
>>>
>>>>> Using the NextRecordset method of the Recordset object, I want to
>>>>> retrieve the UID for the just inserted record. This is how I did
>>>>> it:
>>>
>>>>> ============================================
>>>>> <%
>>>>> Dim objConn
>>>>> Set objConn=Server.CreateObject("ADODB.CONNECTION")
>>>>> objConn.Open 'blah...blah...blah....
>>>
>>>>> Dim strSQL
>>>>> strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
>>>>> Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
>>>>> "');SELECT UID From Table1 WHERE UserName='" &
>>>>> Request.Form("txtUserName") & "' AND Password='" &
>>>>> Request.Form("txtPassword") & "';SELECT @@IDENTITY"
>>>
>>>> You think you're getting only two recordsets. Actually you are
>>>> getting three.
>>>
>>> OH wait, this was dumb. You are using NextRecordset to deal with the
>>> informational message, duh.
>>> My statement still stands though: use SET NOCOUNT to suppress the
>>> message instead of generating extra network traffic. And again, best
>>> practice is to use a stored procedure with an output parameter.
>>>
>>
>> Yes Bob, you are very much correct.....a stored procedure would indeed
>> be a better & wise option. However just to use the NextRecordset
>> method of the Recordset object, I cited the example.
>>
>> Anyway if I change the SQL query cited in my first post to this:
>>
>> ========================================
>> strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
>> Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
>> "');SELECT ID From Table1 WHERE UserName='" &
>> Request.Form("txtUserName") & "' AND Password='" &
>> Request.Form("txtPassword") & "';SELECT UserName FROM Table1 WHERE
>> Password='" & Request.Form("txtPassword") & "'"
>> =========================================
>>
>> i.e. the 2nd SELECT query now retrieves the UserName (instead of the
>> UID), then how do I retrieve the UserName using the 2nd SELECT query
>> using the NextRecordset method?
>>
>> Of course, the above SQL query again doesn't make any sense since both
>> the SELECT queries can be clubbed into one SELECT query to get the UID
>> & UserName. In fact, the UserName can be retrieved using Request.Form
>> itself! So please allow me to reiterate that the SQL queries I have
>> been citing is just for the sake of using the NextRecordset method.
>>
> Again. Use SET NOCOUNT ON. Get into the habit. Also, you should get into
> the habit of using parameters. I will do that for this example because i
> abhor dynamic sql.
>
> You have to call NextRecordset for each resultset returned by your sql
> statements.
>
> Dim objConn
> Set objConn=Server.CreateObject("ADODB.CONNECTION")
> objConn.Open 'blah...blah...blah....
>
> Dim strSQL
> strSQL="SET NOCOUNT ON;" & _
> "INSERT INTO Table1 (UserName,Password) VALUES(?,?)" & _
> ";SELECT ID From Table1 WHERE UserName=? AND " & _
> "Password=?;SELECT UserName " & _
> " FROM Table1 WHERE Password=?"
>
> Dim cmd, arParms
> arParms=Array(Request.Form("txtUserName"), _
> Request.Form("txtPassword"), _
> Request.Form("txtUserName"), _
> Request.Form("txtPassword"), _
> Request.Form("txtPassword"))
>
> set cmd=createobject("adodb.command")
> with cmd
> .commandtext=strSQL
> set .ActiveConnection = objConn
> .CommandType = 1 'adCmdText
> End With
> Dim objRS
> Set objRS=cmd.Execute(,arParms)
> Response.Write "ID contains " & objRS(0) & "
"
> Set objRS = objRS.NextRecordset
> if not objRS is nothing then
> if objRS.State = 1 then '1=adStateOpen
> Response.Write "UserName contains " & _
> objRS("UserName") & "
"
> end if
> end if
> objRS.close:set objRS = nothing
> objConn.close:set objConn=nothing
>
> Notes:
> 1. The NextRecordset method returns a new recordset if a resultset is
> pending. if there are no pending resultsets, it returns Nothing
>
> 2. Whether previous resultsets are still accessible depends on the cursor
> location. With a server-side cursor, the current resultset is always
> discarded when calling NextRecordset. So with this code:
> objConn.cursorlocation = 2 'adUseServer
> Set objRS=cmd.Execute(,arParms)
> Set objRS2=objRS.NextRecordset
>
> objRS will be set to Nothing. However, with this code:
> objConn.cursorlocation = 3 'adUseClient
> Set objRS=cmd.Execute(,arParms)
> Set objRS2=objRS.NextRecordset
>
> objRS will still contain the first recordset and objRS2 will contain the
> second.
>
> 3. Do not depend on objRS.State to tell you if you've reached the last
> resultset. Without SET NOCOUNT, the informational messages will be sent as
> closed recordsets, and there may be more resultsets pending. Keep calling
> NextRecordset until it returns Nothing
>
> 4. I strongly discourage the use of multiple resultsets in ASP if they can
> be avoided. Your goal should be to reduce network traffic and extra
> processing of results, not increase them.

Just 2 things to add:

1. @@IDENTITY will return the wrong answer if the table happens to have any
FOR INSERT triggers defined, SCOPE_IDENTITY() is preferred.

2. A better way to return the row that was just inserted is to use the value
of SCOPE_IDENTITY() in the criteria:

DECLARE @id int
INSERT [...]
Set @id = SCOPE_IDENTITY()
SELECT * FROM MyTable WHERE [ID] = @id


-Mark


> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>