Problem using server side cursors and recordcount

[David DB]

Hi,

Using VB6 and ADO, SQL server I have a problem when using server side
cursors:

Private rsCust As ADODB.Recordset
Private conDB as ADODB.Connection
private sConnect as string

Set connDB = New ADODB.Connection
set rsCust = New ADODB.Recordset

sConnect = "provider=sqloledb;server=" & DBCONNNAME & ";database=" &
DBNAME & "; uid=" & DBUSER & ";pwd=" & DBPASSW

connDB.CursorLocation = adUseServer
connDB.ConnectionString = sConnect
connDB.Open

rsCust.Open "Customers", connDB, adOpenDynamic, adLockOptimistic

When opening the recordset the recordcount is -1. If using client side
cursor it works OK, but we need to use server side because of multiuser, or
what ?

It seems that the recordset is not empty but recordcount is -1. Is there
another way to check the recordcount ?

David

[Ralph]

Post by David DB
Hi,
Using VB6 and ADO, SQL server I have a problem when using server side
Private rsCust As ADODB.Recordset
Private conDB as ADODB.Connection
private sConnect as string
Set connDB = New ADODB.Connection
set rsCust = New ADODB.Recordset
sConnect = "provider=sqloledb;server=" & DBCONNNAME & ";database=" &
DBNAME & "; uid=" & DBUSER & ";pwd=" & DBPASSW
connDB.CursorLocation = adUseServer
connDB.ConnectionString = sConnect
connDB.Open
rsCust.Open "Customers", connDB, adOpenDynamic, adLockOptimistic
When opening the recordset the recordcount is -1. If using client side
cursor it works OK, but we need to use server side because of multiuser, or
what ?
It seems that the recordset is not empty but recordcount is -1. Is there
another way to check the recordcount ?

This is by design.
Common work-around is to specifically query for a count - Count().

- ralph

[David DB]

Hi Ralph,

But is the only way to use multiuser and find out if someone else has
changed the data to use server side cursors ?

David

Post by Ralph

Post by David DB
Hi,
Using VB6 and ADO, SQL server I have a problem when using server side
Private rsCust As ADODB.Recordset
Private conDB as ADODB.Connection
private sConnect as string
Set connDB = New ADODB.Connection
set rsCust = New ADODB.Recordset
sConnect = "provider=sqloledb;server=" & DBCONNNAME & ";database=" &
DBNAME & "; uid=" & DBUSER & ";pwd=" & DBPASSW
connDB.CursorLocation = adUseServer
connDB.ConnectionString = sConnect
connDB.Open
rsCust.Open "Customers", connDB, adOpenDynamic, adLockOptimistic
When opening the recordset the recordcount is -1. If using client side
cursor it works OK, but we need to use server side because of multiuser,

or

Post by David DB
what ?
It seems that the recordset is not empty but recordcount is -1. Is there
another way to check the recordcount ?

This is by design.
Common work-around is to specifically query for a count - Count().
- ralph

[Ralph]

Post by David DB
Hi Ralph,
But is the only way to use multiuser and find out if someone else has
changed the data to use server side cursors ?

Actually it is the cursor-type (used in the generic sense of all attributes
combined) that determines whether a recordcount is returned or not.
("-1" should be interpreted as "record count not available")

So you "may" get a RecordCount if you use an adOpenKeyset or adOpenStatic
with a server-side cursor. All client-side cursors use adOpenStatic (no
matter what) so that's why client-side cursors always return recordcounts.

This is by design because the number of records in a dynamic cursor may
change, and to keep up with changes or notifying the client when the count
has changed would be a complex implementation. A RecordCount isn't provided
with adOpenForwardOnly cursor as part of the improved performance
implementation.

So try
rsCust.Open "Customers", connDB, adOpenKeyset, adLockOptimistic

Note that I used the word "may" a couple of paragraphs above. Unfortunately,
to add to the confusion not all provider/data engine/lock/cursor and
sometimes the query itself combinations work together to provide all
cursor-types. Normal behavior is if a request is made to deliver a
non-supported cursor-type, the provider choses one that is closest to the
request. ie - it won't error- it just might not behave the way you think it
should. <smile>

So how can you tell if RecordCount is going to be reliable - you have to
test and see.

So adOpenKeyset might work, and if it does (if your combination
provider/engine/etc supports it), it will only provide the record count for
the number of qualifying records returned at that instance. The recordset
will reflect changes to records in that RecordSet - but not if any
qualifying records are deleted or added. If you think about it - it is still
just a 'snapshot' count.

Frankly, because a "recordcount" is alway a tad obscure with a 'dynamic'
recordset, and because the simplest of code changes can make it
"non-available", IMHO, it is best to never depend on it unless you are using
a static Client-Side cursor. If you absolutely have to have one - ask for it
by query, or manually count the records returned.

-ralph