Why always -1 recordcount? 1

[RollyS]

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

 

[RoyVidar]

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

 

[Sheco]

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

 

[zemp]

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

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

zemp

 

[chiph]

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

 

[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@!

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

 

[chiph]

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

 

[NoCoolHandle]

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.

 

[Sheco]

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

 

[Jasen]

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.

 

[bjd4jc]

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).

 

[Sheco]

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

True dat.

 

[chiph]

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

 

[NoCoolHandle]

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.

 

[RoyVidar]

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