Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using same recordset twice

Posted on 2004-10-21
8
Medium Priority
?
351 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
Comment
Question by:TRACEYMARY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 33

Assisted Solution

by:hongjun
hongjun earned 750 total points
ID: 12369495
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:
peh803 earned 750 total points
ID: 12369519
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
ID: 12369536
before your dbConn.close, you need this

rsUsers.close
dbConn.close
set rsUsers = nothing
set dbConn = nothing
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:nurbek
ID: 12369555
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
ID: 12369557
@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
ID: 12369577
@peh803
No problem ;)
0
 
LVL 2

Expert Comment

by:larellnielsen
ID: 12369788
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
ID: 12369826
@larellnielsen
:)
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 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