programmer_bmb
asked on
Recordset.RecordCount in ADO
According to the following code, why always the result in MsgBox = -1
--------------------------
Dim conn As new Connection
Dim rs As New Recordset
conn.ConnectionString "Provider=SQLOLEDB.1;Integ
conn.Open
rs.Open " SELECT * FROM Items ", conn, adOpenDynamic, adLockOptimistic
MsgBox rs.RecordCount
--------------------------
I's sure there are data and I get them using :
MsgBox rs(0).Value & " " & rs(1).Value
Could any one help please ... Thanks
--------------------------
Dim conn As new Connection
Dim rs As New Recordset
conn.ConnectionString "Provider=SQLOLEDB.1;Integ
conn.Open
rs.Open " SELECT * FROM Items ", conn, adOpenDynamic, adLockOptimistic
MsgBox rs.RecordCount
--------------------------
I's sure there are data and I get them using :
MsgBox rs(0).Value & " " & rs(1).Value
Could any one help please ... Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source. Also the cursorlocations has an influence on the count.
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source. Also the cursorlocations has an influence on the count.
emoreau & Dhaest are right.
The reason for this is ADO will not fetch all the records from your database if it does not need to, and so doesn't know the record count until it gets to the last row. If your dataset is small (a few thousands of small rows) using static/client side cursors is fine. If your dataset is very large, however, using static or client side cursors can be slow, as they fetch all the records, and then return the first one to you. If you aren't necessarily going to use all the records, you can do
"select count(*) from Items" before hand to get the count, which is very fast in SQL Server because it maintains statistics so it doesn't have to count all the records each time ( as long as you have a primary key).
The reason for this is ADO will not fetch all the records from your database if it does not need to, and so doesn't know the record count until it gets to the last row. If your dataset is small (a few thousands of small rows) using static/client side cursors is fine. If your dataset is very large, however, using static or client side cursors can be slow, as they fetch all the records, and then return the first one to you. If you aren't necessarily going to use all the records, you can do
"select count(*) from Items" before hand to get the count, which is very fast in SQL Server because it maintains statistics so it doesn't have to count all the records each time ( as long as you have a primary key).
ASKER
Dhaest , JohnBPrice .. Thanks a lot for this rich information
ASKER
Is it same for:
myConn.Excexute (" SELECT * FROM Items " )
or there is different ?
myConn.Excexute (" SELECT * FROM Items " )
or there is different ?
Maybe you are looking for this rs.Fields.Count?