recordcount return -1

[bzeng]

Hi,
I have a very strange problem with recordcount return -1.

DB Server : SQL Server 2000
Language: ASP(VB)

'------ Code Start -------------
sql1="select * from [Mapping]"
rs1.open sql1,conn, 3,3,1
response.write rs1.recordcount

However if I change to a different table

'------ Code Start -------------
sql1="select * from Timesheet"
rs1.open sql1,conn, 3,3,1
response.write rs1.recordcount

It returns a valid number.

I am pretty sure there are many rows in the table. I have even tried to change the name of the table, and a few other tables. I found a few other tables return -1 as well. I have no idea what's going on. All these table were created in a similar manner.


What have I done wrong??

thanks

Brian

 

[bzeng]

Interesting....
I went to the table - Mapping and set MappingID as the PK. THen it works.

So, the recordcount only works for those tables which contain PK?

(I have checked all those tables return -1 are the tables which have no PK)

What does record cout to do with PK??

 

[zemp]

The recordcount may also dependant on the cursor type. you can almost always get an accurate record count if your ADO cursor type is adOpenStatic. With some other cursor types the recordcount defaults to -1.

zemp

 

[SonOfEmidec1100]

MSDN on the subject does not mention PK differences, maybe a PK changes the default cursortype to one that supports recordcounts. (?)

For reference from MSDN

Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

If the Recordset object supports approximate positioning or bookmarks—that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True—this value will be the exact number of records in the Recordset, regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.