ADO Recordset returning -1 recordcount

[NatHunter]

Hello Folks,

This seems to be a common problem, but I can't find a thread which provides an answer for my situation.

I have a program which uses a single procedure to open a recordset:

rs.Open sSql, cnDb, adOpenStatic, adLockOptimistic
(sSQL is a sql variable).

The database may be a Jet 3.5.1, Jet 4.0 or SQL Server. For the first two db types, the recordcount is returned as expected, but for the SQL Server version, it's always -1, even though records may successfully be returned.

I've tried every combination of parameters - replacing adOpenStatic with adOpenKeySet and adLockOptimistic with adlockpessimistic, but it's still returning a recordcount of -1.

Hopefully I'm missing something simple, so help here would be appreciated!

 

[MarkSweetland]

Set the CursorLocation property on your connection object (cnDb) to adUseClient.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[ArtieChoke]

I use adOpenKeyset and get the record count back no problem. Using adUseClient has it's own issues, since it's an asynchronous query. What version of SQL Server are you using? Also, you can check if the record count property is supported by checking that property.

How are you connecting to the database - ODBC, OLE, etc.?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[NatHunter]

Thanks.

Recordcount seems to be supported, as it does have a value (-1). Is there something else I need to check?

I'm using the following connection settings - the code does connect to the SQL 2000 Database:

Set cn = New ADODB.Connection
' Set connection properties.
cn.ConnectionTimeout = 10
cn.Provider = "sqloledb"
cn.Properties("Data Source").value = ServerName
cn.Properties("Initial Catalog").value = DatabaseName

cn.Properties("User ID").value = Username
cn.Properties("Password").value = Password

cn.open

Paul

 

[MarkSweetland]

Check out the following TT FAQs:

faq333-618, ASP

faq222-3670, VB

The following article gives a good overview of cursors and lock types. Although it references MySQL as the server, which has its own limitations, it does give a good description of ADO types.

http://www.vbmysql.com/articles/visual-basic_mysql/cursorsandlocks.html

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[pweegar]

The following is from

http://www.devguru.com/technologies/ado:

The RecordCount property returns a long value that is the number of records in the Recordset object. The Recordset must be open to use this property, otherwise a run-time error will be generated. If the provider does not support this property or the count cannot be done, a value of -1 will be returned.

The type of cursor being used by the Recordset affects whether this property can return a valid count. In general, you can obtain the actual count for a keyset and static cursor. However, you may get either a -1 or the count if a dynamic cursor is being used, and you cannot get a count if a forward-only cursor is being used (-1 is returned).


It's a very good site and has lots of useful info.

 

[NatHunter]

Thanks for your tips and pointers. After some investigation, I discovered that I needed to set the CursorLocation property on my connection to adUseClient adUseServer is the default and doesn't give the recordcount, nor does it allow MoveNext, MoveLast etc.

so setting the following before cn.open did the trick.

cn.CursorLocation = adUseClient

Paul