By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,805 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,805 IT Pros & Developers. It's quick & easy.

RecordCount

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.

"Mark Watkins" <ma*********@yahoo.com> wrote in message
news:Of**************@tk2msftngp13.phx.gbl...
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.

Jul 19 '05 #2

P: n/a
rs.Open strSQL, objConn, adLockReadOnly, adCmdTable
dblrecordCount = rs.RecordCount

-dlbjr

Discerning resolutions for the alms
Jul 19 '05 #3

P: n/a
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.

"dlbjr" <do******@do.u> wrote in message
news:epjob.159$Qy4.13102@typhoon01...
rs.Open strSQL, objConn, adLockReadOnly, adCmdTable
dblrecordCount = rs.RecordCount

-dlbjr

Discerning resolutions for the alms

Jul 19 '05 #4

P: n/a
Chris Barber wrote:
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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #5

P: n/a
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:O%****************@TK2MSFTNGP12.phx.gbl...
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...TNGP12.phx.gbl
Jul 19 '05 #6

P: n/a
Chris Hohmann wrote:
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:O%****************@TK2MSFTNGP12.phx.gbl...
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" response.write Ubound(arResults,2) + 1 & " records"
else
response.write "no records"
end if


Plus 1.

Haven't we had this conversation already. :)

http://groups.google.com/groups?selm...TNGP12.phx.gbl

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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.