Using same recordset twice

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.
LVL 7
TRACEYMARYAsked:
Who is Participating?
 
peh803Commented:
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
 
hongjunCommented:
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
 
hongjunCommented:
before your dbConn.close, you need this

rsUsers.close
dbConn.close
set rsUsers = nothing
set dbConn = nothing
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nurbekCommented:
recordcount is the collection of recordset

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

as hongjun  mentioned :)

0
 
peh803Commented:
@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
 
hongjunCommented:
@peh803
No problem ;)
0
 
larellnielsenCommented:
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
 
hongjunCommented:
@larellnielsen
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.