Counting rows in a recordset

Bill

unread,

Jun 20, 2003, 11:44:09 AM6/20/03

to

I'm still having problems counting the number of rows in my recordset.
I'm using rs.recordcount and I'm using the adOpenStatic cursor, yet
I'm still getting the -1 value returned, and I can't understand why.

Here's how I'm setting up the code. I've tried two differrent ways:

set rs=server.creatobject("adodb.recordset")

Const adOpenStatic=0

with rs
.cursortype=adOpenStatic
end with

rs.open sql, cn

myCount=rs.recordcount

I've also tried

rs.open sql, cn, adOpenStatic

myCount=rs.recordcount

Both of these return a -1 result, no matter how many rows there are in
the recordset. I'm at my wits end here. What do I do?

Thanks,

Bill

Ray at <%=sLocation%>

unread,

Jun 20, 2003, 12:39:14 PM6/20/03

to

http://www.aspfaq.com/2193

Ray at work

"Bill" <billzi...@gospellight.com> wrote in message
news:8da5f4f4.03062...@posting.google.com...

Bob Barrows

unread,

Jun 20, 2003, 1:13:51 PM6/20/03

to

When you use Execute to open a recordset, you get the default forward-only
cursor, no matter what properties you set on the recordset prior to that.
You must use "rs.Open" if you want to have control over the cursortype and
location.

If I was doing it, I would not open a more expensive cursor just to get the
record count. I would open the recordset using execute, and use GetRows to
read the data from the recordset into an array, like this:

ar = rs.GetRows

This allows me to immediately close the recordset and connection, thus
improving the performance and scalability of my application. It can be
thousands of times faster to loop through an array than through a recordset.
And the bonus: to get the recordcount, just do:

lRecs = ubound(ar,2)

I have posted several examples of using GetRows in the past couple months:
it shouldn't be too hard to use Google to find them.

Bob Barrows

William Zimmerman

unread,

Jun 20, 2003, 1:20:31 PM6/20/03

to

Thanks for the lead, but it still is not working. The solution they
profided still returns a -1.

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

TJS

unread,

Jun 20, 2003, 12:28:42 PM6/20/03

to

adOpenStatic is not 0 it is 3

set rs=server.creatobject("adodb.recordset")
rs.open sql, cn, 3
myCount=rs.recordcount

if you still get -1 then the problem is your sql is not finding any matching
records.

look here for more info.:

http://www.aspin.com/func/search?cob=aspkey&qry=recordcount

================================
http://www.ASPkey.net/
A Resource Site for Web Developers
*Free OnLine web Tools
*Free development services
================================

Bill wrote in message <8da5f4f4.03062...@posting.google.com>...

Chris Hohmann

unread,

Jun 20, 2003, 2:46:23 PM6/20/03

to

"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:O$D2V#0NDHA...@TK2MSFTNGP12.phx.gbl...

> And the bonus: to get the recordcount, just do:
>
> lRecs = ubound(ar,2)

Plus 1?

SPA

unread,

Jun 20, 2003, 2:49:03 PM6/20/03

to

Wonder if this will help u

http://forums.devshed.com/t48403/s06a710d314edaca56e70c78a0275251f.html

SPA

"Bill" <billzi...@gospellight.com> wrote in message
news:8da5f4f4.03062...@posting.google.com...

Bob Barrows

unread,

Jun 20, 2003, 2:56:22 PM6/20/03

to

Now I'm really off-base - good catch

Bob Barrows

unread,

Jun 20, 2003, 3:01:02 PM6/20/03

to

Oh wait - you ARE using rs.Open - my bad.

The problem is this:
Const adOpenStatic=0

The value for adOpenStatic is 3, not 0. See here:
http://www.connectionstrings.com/adoenumerations.asp

See here for an alternative to using Const:
http://www.aspfaq.com/show.asp?id=2112

I still think you should consider using GetRows.

Bob Barrows

Chris Hohmann

unread,

Jun 20, 2003, 3:27:36 PM6/20/03

to

"Bob Barrows" <reb_...@yahoo.com> wrote in message

news:OfnJo31N...@TK2MSFTNGP12.phx.gbl...

LOL. By the way, I owe you a debt of thanks. I've recently started to adopt
your sp as connection method philosophy and I really like it. I paid for the
connection object, why not take advantage of everything it has to offer,
right? Thanks.

-Chris

Bob Barrows

unread,

Jun 20, 2003, 3:28:34 PM6/20/03

to

Chris Hohmann wrote:
> ... By the way, I owe you a debt of thanks. I've recently started to

> adopt your sp as connection method philosophy and I really like it. I
> paid for the connection object, why not take advantage of everything
> it has to offer, right? Thanks.
>

Actually, you should be thanking Bill Vaughn - it was his book, "ADO
Examples and Best Practices", that convinced me of its value.

But, you're welcome :-)

Bob

PS. Again, this technique is broken in dotnet, so keep that in mind ...