RecordCount

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
Thu Oct 30 18:46:26 CST 2003

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" <markatumich@yahoo.com> wrote in message
news:OfyxlQ0nDHA.2964@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.

dlbjr
Thu Oct 30 20:02:49 CST 2003

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

-dlbjr

Discerning resolutions for the alms

Chris
Thu Oct 30 20:19:34 CST 2003

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" <dontknow@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

Bob
Thu Oct 30 21:47:17 CST 2003

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"

Chris
Thu Oct 30 22:49:35 CST 2003

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O%232JvG2nDHA.2216@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=%231IXms1NDHA.2208%40TK2MSFTNGP12.phx.gbl

Bob
Fri Oct 31 06:09:52 CST 2003

Chris Hohmann wrote:
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:O%232JvG2nDHA.2216@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=%231IXms1NDHA.2208%40TK2MSFTNGP12.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"