Code Bank
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Programming > Code Bank
Reload this Page

RecordCount - How to get a RecordCount from a RecordSet

Discuss RecordCount - How to get a RecordCount from a RecordSet in the Code Bank forum on ASP Free.
RecordCount - How to get a RecordCount from a RecordSet Code Bank forum containing sample code and scripts to help solve common problems. Take a look and use the code to assist in your projects.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old April 19th, 2007, 06:19 AM
degsy degsy is offline
Contributing User
ASP Free God 2nd Plane (6000 - 6499 posts)
  
Join Date: Aug 2005
Location: North East, UK
Posts: 6,191 degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 19 h 41 m 52 sec
Reputation Power: 121
RecordCount - How to get a RecordCount from a RecordSet
Why does RecordCount return as -1?
Recordcount is not supported with the default forward-only cursor

asp Code:
Original - asp Code 
  1. Set rs1 = Server.CreateObject("ADODB.Recordset")
  2. rs1.Open "SELECT user_name FROM users", Conn
  3.  
  4.     rCount = rs1.RecordCount
  5.     Response.Write "<p>Example 1:<br>"
  6.     Response.Write "Record Count: " & rCount & "<br>"
  7.         While Not rs1.EOF
  8.             Response.Write rs1("user_name") & "<br>"
  9.             rs1.MoveNext
  10.         Wend
  11.     Response.Write "</p>"
  12.  
  13. rs1.Close
  14. Set rs1 = Nothing


Result Code:
Original - Result Code 
    
Example 1:
Record Count: -1
John
Paul
George
Ringo



Below are five methods to get the correct RecordCount
Note: Where the RecordCount is displayed above/before the Recordset
Comments on this post
nofriends agrees: excellent work
__________________
CyberTechHelp
Reply With Quote
  #2  
Old April 19th, 2007, 07:10 AM
degsy degsy is offline
Contributing User
ASP Free God 2nd Plane (6000 - 6499 posts)
  
Join Date: Aug 2005
Location: North East, UK
Posts: 6,191 degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 19 h 41 m 52 sec
Reputation Power: 121
Count()
You can use the SQL Count() function to get a RecordCount

In this example a second RecordSet is used to execute the query using the Count() function.

asp Code:
Original - asp Code 
  1. Set rs2 = Server.CreateObject("ADODB.Recordset")
  2. rs2.Open "SELECT user_name FROM users", Conn
  3.  
  4.     Set rs2Count = Conn.Execute("SELECT Count(user_name) AS rCount FROM users")
  5.         rCount = rs2Count("rCount")
  6.     Set rs2Count = Nothing
  7.     
  8.     Response.Write "<p>Example 2:<br>"
  9.     Response.Write "Record Count: " & rCount & "<br>"
  10.         While Not rs2.EOF
  11.             Response.Write rs2("user_name") & "<br>"
  12.             rs2.MoveNext
  13.         Wend
  14.     Response.Write "</p>"
  15.     
  16. rs2.Close
  17. Set rs2 = Nothing


Result Code:
Original - Result Code 
    Example 2:
Record Count: 4
John
Paul
George
Ringo
Reply With Quote
  #3  
Old April 19th, 2007, 07:15 AM
degsy degsy is offline
Contributing User
ASP Free God 2nd Plane (6000 - 6499 posts)
  
Join Date: Aug 2005
Location: North East, UK
Posts: 6,191 degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 19 h 41 m 52 sec
Reputation Power: 121
CursorType
You can change the CursorType of the RecordSet to get the RecordCount

http://www.w3schools.com/ado/met_rs_open.asp
Code:
objRecordset.Open source,actconn,cursortyp,locktyp,opt


Note: You also need to specify the LockType when specifying the CursorType.

The default CursorType is 0 (adOpenForwardOnly)
The default LockType is 1 (adLockReadOnly)

asp Code:
Original - asp Code 
  1. Set rs3 = Server.CreateObject("ADODB.Recordset")
  2. rs3.Open "SELECT user_name FROM users", Conn,1,1
  3.  
  4.     rCount = rs3.RecordCount
  5.     Response.Write "<p>Example 3:<br>"
  6.     Response.Write "Record Count: " & rCount & "<br>"
  7.         While Not rs3.EOF
  8.             Response.Write rs3("user_name") & "<br>"
  9.             rs3.MoveNext
  10.         Wend
  11.     Response.Write "</p>"
  12.  
  13. rs3.Close
  14. Set rs3 = Nothing
Reply With Quote
  #4  
Old April 19th, 2007, 07:17 AM
degsy degsy is offline
Contributing User
ASP Free God 2nd Plane (6000 - 6499 posts)
  
Join Date: Aug 2005
Location: North East, UK
Posts: 6,191 degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 19 h 41 m 52 sec
Reputation Power: 121
Loop and MoveFirst
You can Loop through the RecordSet and increment a variable to hold the RecordCount.

Here is an example using a While loop and MoveFirst to reset the RecordSet

asp Code:
Original - asp Code 
  1. Set rs4 = Server.CreateObject("ADODB.Recordset")
  2. rs4.Open "SELECT user_name FROM users", Conn
  3.     
  4.     rCount = 0
  5.     While Not rs4.EOF
  6.         rCount = rCount+1
  7.         rs4.MoveNext
  8.     Wend
  9.     rs4.MoveFirst
  10.     
  11.     Response.Write "<p>Example 4:<br>"
  12.     Response.Write "Record Count: " & rCount & "<br>"
  13.         While Not rs4.EOF
  14.             Response.Write rs4("user_name") & "<br>"
  15.             rs4.MoveNext
  16.         Wend
  17.     Response.Write "</p>"
  18.  
  19. rs4.Close
  20. Set rs4 = Nothing
Last edited by degsy : April 19th, 2007 at 07:20 AM.
Reply With Quote
  #5  
Old April 19th, 2007, 07:20 AM
degsy degsy is offline
Contributing User
ASP Free God 2nd Plane (6000 - 6499 posts)
  
Join Date: Aug 2005
Location: North East, UK
Posts: 6,191 degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 19 h 41 m 52 sec
Reputation Power: 121
Loop through and concatenate string to create variable
You can Loop through the Recordset and create two variables.
One will hold the RecordSet data
The other will increment a counter for the RecordCount

asp Code:
Original - asp Code 
  1. Set rs5 = Server.CreateObject("ADODB.Recordset")
  2. rs5.Open "SELECT user_name FROM users", Conn
  3.     
  4.     rCount = 0
  5.     While Not rs5.EOF
  6.         rsStr = rsStr & rs5("user_name") & "<br>"
  7.         rCount = rCount+1
  8.         rs5.MoveNext
  9.     Wend
  10.     Response.Write "<p>Example 5:<br>"
  11.     Response.Write "Record Count: " & rCount & "<br>"
  12.     Response.Write rsStr
  13.     Response.Write "</p>"
  14.  
  15. rs5.Close
  16. Set rs5 = Nothing
Reply With Quote
  #6  
Old April 19th, 2007, 07:23 AM
degsy degsy is offline
Contributing User
ASP Free God 2nd Plane (6000 - 6499 posts)
  
Join Date: Aug 2005
Location: North East, UK
Posts: 6,191 degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level)degsy User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 19 h 41 m 52 sec
Reputation Power: 121
getRows()
You can use getRows() to put the contents of the RecordSet into an array.

You can then use the UBound function to get the RecordCount.

You can then use a For Loop to loop through the RecordSet

asp Code:
Original - asp Code 
  1. Set rs6 = Conn.Execute("SELECT user_name FROM users")
  2.  
  3.     rsArray = rs6.getRows()
  4.     rCount = UBound(rsArray,2)+1
  5.     Response.Write "<p>Example 6:<br>"
  6.     Response.Write "Record Count: " & rCount & "<br>"
  7.         For r=0 To UBound(rsArray,2)
  8.             Response.Write rsArray(0,r) & "<br>"
  9.         Next
  10.     Response.Write "</p>"
  11.  
  12. rs6.Close
  13. Set rs6 = Nothing
Reply With Quote
  #7  
Old April 19th, 2007, 07:41 AM
nofriends's Avatar
nofriends nofriends is offline
Senior Water Wizard
ASP Free God 11th Plane (10000 - 10499 posts)
  
Join Date: Aug 2004
Location: Cape Town, RSA
Posts: 10,186 nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)nofriends User rank is Brigadier General (60000 - 70000 Reputation Level)  Folding Points: 102109 Folding Title: Super Ultimate Folder - Level 1Folding Points: 102109 Folding Title: Super Ultimate Folder - Level 1Folding Points: 102109 Folding Title: Super Ultimate Folder - Level 1Folding Points: 102109 Folding Title: Super Ultimate Folder - Level 1Folding Points: 102109 Folding Title: Super Ultimate Folder - Level 1Folding Points: 102109 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 3 Months 2 Weeks 2 Days 7 h 36 m 24 sec
Reputation Power: 699
nice work Degsy
Comments on this post
asmoran agrees: Boo! &lt;random rep&gt;
__________________
Look! Its a ShemZilla


Reply With Quote