Is there a way to get the count of a recordset without looping through to the EOF?
If not, then how can I store these values into an array as I am looping through the recordset?
Thanks in advance...
I have the start:
While Not rs.EOF
rs.movenext
......
Wend
The manual way that you have started is...
count = 0
While Not rs.EOF
count = count + 1
rs.movenext
Wend
rs.MoveFirst
if you use the Connection method...
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DSN=myData"
Set rs = Server.CreateObject("ADODB.Recordset")
sqlStr = "SELECT something FROM somewhere"
rs.open sqlStr, conn1, 3, 3
count = rs.recordcount
If you use rs.recordcount as advised, you need to do a rs.movelast beforehand, otherwise the provider will not have a value in the 'recordcount' property to return, so will always return -1.
HTH.
hmmmm...that has not been my experience. rs.recordcount returns the true count right off the bat in my applications.
What I haven't been able to do is use this property with the "Command" method. I have to fall back on the manual count.
The command object doesnt have the same level of traversal as the recordset object does, doesnt have the ado cursors, etc.
The manual count works just as well.
Plus, you can always use
SELECT Count(ID)...
record_count = rs(0)
Originally posted by evildj
if you use the Connection method...
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DSN=myData"
Set rs = Server.CreateObject("ADODB.Recordset")
sqlStr = "SELECT something FROM somewhere"
rs.open sqlStr, conn1, 3, 3
count = rs.recordcount
[/B]
This is the code to use, and it always return the recordcount, you just have to open the recordset properly i.e rs.open sqlStr, conn1, 3, 3 and use server.createobject to create your recordset
I have found that the connection method is not reliable either. This is how I have gotten around it:
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DSN=myData"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
sqlStr = "SELECT something FROM somewhere"
rs.open sqlStr
to get this to work, make sure you include adovbs.inc (outside of your VBScript tags).
<!--#include file="adovbs.inc"-->
You only have to include adovbs.inc if you want to refer to constants by adNameOfConstant, rather than numbers.
adUseClient is probably 0 - so if you changed it to that and didnt include the adovbs.inc file it should still work.
Hi all !
Actually, I don't know what the double "3" in rs.open sql,conn, 3, 3 mean and what their functions are !
Can u plz to explain more about this one !
Hi all !
Actually, I don't know what the double "3" in rs.open sql,conn, 3, 3 mean and what their functions are !
Can u plz to explain more about this one !
3, 3 is the cursor and lock type for the recordset you are opening.
3 is adOpenStatic for the cursor and adLockReadOnly for the database lock type.
loads of info on these via google if you need more info.