Solved

Using same recordset twice

Posted on 2004-10-21
338 Views
Last Modified: 2010-07-27
hi in my page i have
sqlUsers = "select * from AssetUser where AssetTag = '" & trim(request("AssetTag")) & "'"

set rsUsers = dbConn.Execute(sqlUsers)
if not rsUsers.EOF then
      do while not rsUsers.EOF
            'CountUsers = rsUsers.RecordCount
                      CountUsers = CountUsers + 1
            response.write "No of Users" & CountUsers
            rsUsers.MoveNext
            Loop
end if

So that i can display the number of records ..i tried the
recordcount but got -1 but when i did countusers + 1 it returned the 2 records.

now done below i use the same statement but display the data
if not rsUsers.EOF then
      do while not rsUsers.EOF
            response.write rsUsers("Username")                                  rsUsers.MoveNext
            Loop
end if

If i take out the top part of the recordset count then 2nd part displays but not when i include the count.

The 2nd part display ...with a condition but i want count to display on all conditions.....that is why i have it twice.

Any solutions.
0
Question by:TRACEYMARY
    8 Comments
     
    LVL 33

    Assisted Solution

    by:hongjun
    try this to get a correct recordcount

    Const adUseClient = 3
    Const adOpenForwardOnly = 0
    Const adLockOptimistic = 3
    Set rsUsers = Server.CreateObject("ADODB.Recordset")
       
    rsUsers.CursorLocation = adUseClient
    rsUsers.CursorType = adOpenForwardOnly
    rsUsers.Open sqlUsers, conn, , adLockOptimistic

    if not rsUsers.Eof then
    ....



    hongjun
    0
     
    LVL 19

    Accepted Solution

    by:
    This may be helpful to you:
    (taken from http://www.devguru.com/Technologies/ado/quickref/recordset_recordcount.html)

    The RecordCount property returns a long value that is the number of records in the Recordset object. The Recordset must be open to use this property, otherwise a run-time error will be generated. If the provider does not support this property or the count cannot be done, a value of -1 will be returned.

    The type of cursor being used by the Recordset affects whether this property can return a valid count. In general, you can obtain the actual count for a keyset and static cursor. However, you may get either a -1 or the count if a dynamic cursor is being used, and you cannot get a count if a forward-only cursor is being used (-1 is returned).

    Regards,
    peh803
    0
     
    LVL 33

    Expert Comment

    by:hongjun
    before your dbConn.close, you need this

    rsUsers.close
    dbConn.close
    set rsUsers = nothing
    set dbConn = nothing
    0
     
    LVL 13

    Expert Comment

    by:nurbek
    recordcount is the collection of recordset

    in order to use this property you have to
    Server.CreateObject("ADODB.Recordset")

    as hongjun  mentioned :)

    0
     
    LVL 19

    Expert Comment

    by:peh803
    @hongjun -- sorry if my info was a repost -- it obviously wasn't there when I initially viewed the question.  No harm intended.

    regards,
    peh803
    0
     
    LVL 33

    Expert Comment

    by:hongjun
    @peh803
    No problem ;)
    0
     
    LVL 2

    Expert Comment

    by:larellnielsen
    In TRACEYMARY's case I think that you would not want to use a forward only cursor if she is going to be use the record set twice for the count and a second time for output. Possibly a dynamic cursor (Const adOpenDynamic = 2) so the cursor can be reset after the count and before the output like this:

    Const adUseClient = 3
    Const adOpenDynamic = 2
    Const adLockOptimistic = 3
    Set rsUsers = Server.CreateObject("ADODB.Recordset")
       
    rsUsers.CursorLocation = adUseClient
    rsUsers.CursorType = adOpenDynamic
    Call rsUsers.Open(sqlUsers, dbConn, , adLockOptimistic)

    If Not rsUsers.EOF Then
      Do Until rsUsers.EOF
        CountUsers = CountUsers + 1
        rsUsers.MoveNext
      Loop
     
      rsUsers.MoveFirst
     
      Response.Write "No of Users" & CountUsers
     
      Do Until rsUsers.EOF
        Response.Write rsUsers("Username")                              
        rsUsers.MoveNext
      Loop
    End If

    rsUsers.Close
    dbConn.Close

    Set rsUsers = Nothing
    Set dbConn = Nothing

    I do not want any points because this is basically @hongjun and @peh803 answer with the exception of the cursor type.

    LaRell
    0
     
    LVL 33

    Expert Comment

    by:hongjun
    @larellnielsen
    :)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now