RecordCount

[Mark Watkins]

I know for a fact that in my database, I have three records under the users
table. However when I execute this code:

--------------BEGIN CODE----------------

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\Inetpub\wwwroot\spruce.mdb;"
strQuery = "SELECT * FROM schedule"
Set rst = Server.CreateObject("ADODB.recordset")
rst.Open strQuery, objConn
Set est = Server.CreateObject("ADODB.recordset")
est.Open "SELECT * FROM users", objConn
est.MoveFirst
Response.Write est.RecordCount

----------------------END CODE ------------------------------

It prints out "-1" instead of "3"

Any idea why this may be happening? Am I using RecordCount correctly? All
I wanna do is know the number of records resulting from my query without
using a silly loop.

 

[Chris Barber]

Default cursor type is 'firehose' forward-only server-side which doesn't
populate the recordcount property.

Try doing:

..MoveLast
..MoveFirst

to populate the recordcount or consider a client-side cursor (all data gets
transferred to the client).

Chris.

I know for a fact that in my database, I have three records under the users
table. However when I execute this code:

--------------BEGIN CODE----------------

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\Inetpub\wwwroot\spruce.mdb;"
strQuery = "SELECT * FROM schedule"
Set rst = Server.CreateObject("ADODB.recordset")
rst.Open strQuery, objConn
Set est = Server.CreateObject("ADODB.recordset")
est.Open "SELECT * FROM users", objConn
est.MoveFirst
Response.Write est.RecordCount

----------------------END CODE ------------------------------

It prints out "-1" instead of "3"

Any idea why this may be happening? Am I using RecordCount correctly? All
I wanna do is know the number of records resulting from my query without
using a silly loop.

 

[dlbjr]

rs.Open strSQL, objConn, adLockReadOnly, adCmdTable
dblrecordCount = rs.RecordCount

-dlbjr

Discerning resolutions for the alms

 

[Chris Barber]

Not meaning to offend but Mark isn't really going to be able to learn
anything from such a short reply that makes no attempt to explain why the
issue is occurring?
Brings to mind the adage about 'Give a man a meal and he'll eat for a day
but give him the knowledge and tools to grow his own and he'll never want
for food again'?

Chris.

rs.Open strSQL, objConn, adLockReadOnly, adCmdTable
dblrecordCount = rs.RecordCount

-dlbjr

Discerning resolutions for the alms

 

[Bob Barrows]

Default cursor type is 'firehose' forward-only server-side which
doesn't populate the recordcount property.

Try doing:

.MoveLast
.MoveFirst
to populate the recordcount

There are some problems with this advice:
1. Since it's a forward-only cursor, the MoveFirst method will usually not
be supported. Some providers, however, will support it, but their method of
supporting it may not be to your liking: MoveFirst causes the recordset to
be requeried, which can have a large impact on performance. If the provider
does not support MoveFirst with forward-only cursors, and error will be
raised.
2. Even if the MoveFirst is supported, it will still be a forward-only
cursor, and RecordCount will still contain -1 after the MoverFirst. This is
different from the behavior of DAO recordsets.

or consider a client-side cursor (all
data gets transferred to the client).

That will definitely work. However, you do not need a client-side cursor to
get a recordcount: there are several server-side cursor types that will
support record-count: static, keyset, dynamic, and with the Jet provider,
Table.

However, I do not recommend opening one of the non-default cursor types
merely to get a record count. The non-default cursor types require more
resources and do not perform as well as the default due to the extra
functionality offered. There are other ways to get a record count from a
default forward-only cursor. My favorite is to use GetRows to stuff the data
from the recordset into an array. This has two benefits:
1. I can immediately close the recordset and connection, allowing other
threads on the server to use the connection instead of creating a new one.
2. I can work with the data in the array, which will be much quicker than
using a cursor to work with it.

Once the data is in the array, you can use Ubound to determine the number of
records:

est.Open ...
if not est.EOF then arResults = est.GetRows
est.close: set est = nothing
objConn.close:set objConn=nothing
if isArray(arResults) then
response.write Ubound(arResults,2) & " records"
else
response.write "no records"
end if

HTH,
Bob Barrows

 

[Chris Hohmann]

Once the data is in the array, you can use Ubound to determine the number of
records:

est.Open ...
if not est.EOF then arResults = est.GetRows
est.close: set est = nothing
objConn.close:set objConn=nothing
if isArray(arResults) then
response.write Ubound(arResults,2) & " records"
else
response.write "no records"
end if

Plus 1.

Haven't we had this conversation already.

http://groups.google.com/groups?selm=#[email protected]

 

[Bob Barrows]

response.write Ubound(arResults,2) + 1 & " records"

Plus 1.

Haven't we had this conversation already.

http://groups.google.com/groups?selm=#[email protected]

Wow! This is an example of a mistake I would never make in my own code but
which keeps slipping into my air code examples! Thanks for the re-catch!

Bob