×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Why always -1 recordcount?

Why always -1 recordcount?

Why always -1 recordcount?

(OP)
THis is my environment:
VB6 w/ SP5 running on Win2000 Pro w/ SP4
Now, why is it that when I query the SQL Server 2000 (w/o SP installed) with the ff code snippet:

rs2.Open "select * FROM Feb910", g_AdoDBCn, adOpenKeyset, adLockOptimistic, adCmdText
Debug.Print rs2.recordcount

I get -1 recordset. Thanks in advance

JOJO ACOSTA

RE: Why always -1 recordcount?

Search, and you'll find

Thread709-926639, Thread709-903278, Thread709-850505...

Bottom line, if you really, really need a recordcount - use a select count(*) from the table with same where condition (my opinion). To just test if the recordset contains records, test for .bof/.eof.

Roy-Vidar

RE: Why always -1 recordcount?

Try setting your recordset's CursorLocation property to client before you open it.

RE: Why always -1 recordcount?

Change your cursor type. Not all cursor types return a recordset. Try,

CODE

rs2.Open "select * FROM Feb910", g_AdoDBCn, adOpenStatic, adLockOptimistic, adCmdText

zemp

RE: Why always -1 recordcount?

RoyVidar has the right idea.  You can't trust the RecordCount property.

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

RE: Why always -1 recordcount?

Yes you can!!!

-1 = true
i.e. We have a record!

This will always show with Firehose and dynamic cursors because they dont care were they are, just that there is a record@!

Dyanmic won't tell because they don't know if a reocrd will be deleted or added and a firehose (forward only) will never assume you need to go any further than you are now.

Read up on record location and cursor types. They will behave exaclty as you should expect.

Rob

RE: Why always -1 recordcount?

The MSDN documentation is incorrect.  

I used to work for a premier Microsoft partner, and we eventually ended up talking to the ADO development team to find out why it was unreliable.  Summary: it just is.

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

RE: Why always -1 recordcount?

No it isn't. The documentation is incorect maybe, but the recordcount is correct when you look at what type of recordset you are dealing with and what you expect of it.

Rob
MCSE, MCSD, MCDBA 6.5 7 and 2k
Also who in the dev team did you talk to, and how much do they really understand?
There are definitly incorrect and wrong things with ADO, but the recordcount is correct when you think about the record type.

RE: Why always -1 recordcount?

If you think about how RecordCount works under the hood then it won't seem "wrong" ...

RE: Why always -1 recordcount?

In other words, it's a "feature" not a bug.

I've never had a problem with doing something like

reccound=t = UBound(rst())

to get a record count.

________
Remember, you're unique... just like everyone else.

RE: Why always -1 recordcount?

Even if you could trust the RecordCount property (which I agree with johnwm that you really can't), it is a very expensive operation as it needs to go completely to the end of a recordset and then back to where it was when checked. If you have 20 records you're fine but if you lots of records then it is slooooooow. Select Count(*) with the same WHERE Clause is much, much more efficient (especially if the table is indexed properly).

RE: Why always -1 recordcount?

Quote (bjd4jc):

"... Select Count(*) with the same WHERE Clause is much, much more efficient (especially if the table is indexed properly)."

True dat.

RE: Why always -1 recordcount?

It's a trade-off.  Using Count(*) can result in additional network trips (it won't if you're using multiple result sets).  Doing a UBound() once the resultset is back at the client results in high CPU usuage on the client, but maybe that's OK, depending on what the user's expectations are (or maybe it's on a slow network).

If you're time critical, write some test code to try it both ways to see which is faster for you.

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

RE: Why always -1 recordcount?

What do you get for a recordcount when you get rid of the last parameter?

adCmdText

i.e. rs2.Open "select * FROM Feb910", g_AdoDBCn, adOpenKeyset, adLockOptimistic

My guess is a good (non -1) recordcount.

:)

RE: Why always -1 recordcount?

NoCoolHandle,

"Yes you can!!!

-1 = true
i.e. We have a record!

This will always show with Firehose and dynamic cursors because they dont care were they are, just that there is a record@!
"

 - on my setup it gives -1 regardless of whether .bof/.eof is true or false - so I don't know whether or not I have a record.

Besides, the documentation states that dynamic cursor will return -1 or the actual count depending on data source.

"What do you get for a recordcount when you get rid of the last parameter?

adCmdText

i.e. rs2.Open "select * FROM Feb910", g_AdoDBCn, adOpenKeyset, adLockOptimistic

My guess is a good (non -1) recordcount.
"

On my setup, the OP's initial code, gives a correct count regardless of cursor location and regardless of whether or not the CommandTypeEnum is specified.

I've never relied on the .RecordCount property of ADO recordsets (I don't think I used it much with DAO either, though it's more reliable there), but did some small, not very academic testing some years ago. Like in this thread, it showed different behaviour under different conditions and on different computers, some also with the same MDAC.

The decision of whether or not to trust the .RecordCount property of ADO recordsets, is entirely up to the reader, I don't, and my advise, is the same as what I stated in the first reply:

"Bottom line, if you really, really need a recordcount - use a select count(*) from the table with same where condition (my opinion). To just test if the recordset contains records, test for .bof/.eof."

Roy-Vidar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close