NextRecordset

R

RN1

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
 
B

Bob Barrows [MVP]

RN1 said:
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.
 
B

Bob Barrows [MVP]

Bob said:
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.
 
R

RN1

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"- 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:

========================================
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.

Thanks,

Ron
 
B

Bob Barrows [MVP]

RN1 said:
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) & "<BR>"
Set objRS = objRS.NextRecordset
if not objRS is nothing then
if objRS.State = 1 then '1=adStateOpen
Response.Write "UserName contains " & _
objRS("UserName") & "<BR>"
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.
 
R

RN1

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) & "<BR>"
Set objRS = objRS.NextRecordset
if not objRS is nothing then
    if objRS.State = 1 then '1=adStateOpen
        Response.Write "UserName contains " & _
            objRS("UserName") & "<BR>"
    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"- 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
 
M

Mark J. McGinty

Bob Barrows said:
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) & "<BR>"
Set objRS = objRS.NextRecordset
if not objRS is nothing then
if objRS.State = 1 then '1=adStateOpen
Response.Write "UserName contains " & _
objRS("UserName") & "<BR>"
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top