NextRecordset

Discussion in 'ASP General' started by RN1, Dec 22, 2007.

  1. RN1

    RN1 Guest

    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
     
    RN1, Dec 22, 2007
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Dec 22, 2007
    #2
    1. Advertising

  3. 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"
     
    Bob Barrows [MVP], Dec 22, 2007
    #3
  4. RN1

    RN1 Guest

    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.
    >
    > --
    > 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
     
    RN1, Dec 23, 2007
    #4
  5. 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) & "<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"
     
    Bob Barrows [MVP], Dec 23, 2007
    #5
  6. RN1

    RN1 Guest

    On Dec 23, 9:18 pm, "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:

    >
    > >>>> ============================================
    > >>>> <%
    > >>>> 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) & "<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
     
    RN1, Dec 23, 2007
    #6
  7. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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) & "<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


    > --
    > 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"
    >
     
    Mark J. McGinty, Dec 27, 2007
    #7
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joseph Still
    Replies:
    2
    Views:
    169
    Joseph Still
    Feb 23, 2009
Loading...

Share This Page