Re: Opening parameterized ADO Recordset with Dynamic/Keyset cursor typ

I always use parameterized queries to access the database by using the ADO
Command object. When I want to open a recordset I use Command.Execute.
This
gives me a static recordset, wich is sufficient most of the time.

A Command object inherits the current CursorLocation from the existing
Connection Objects setting.

If you have set CursorLocation on the Connection object to adUseClient, you
will always get back a Static Cursor for Recordsets returned by
Command.Execute(). In fact, it is impossible to get back anything other than
a Static Cursor when the Cursor Location is adUseClient.

If you have set CursorLocation on the Connection object to adUseServer, then
by default, you will always get back a ForwardOnly Cursor for Recordsets
returned by Command.Execute().

Now I want to open a recordset with a dynamic or keyset cursor type, using
the folling code :

Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open <oCommand>, , 1, 3 '(keyset cursor/optimistic locking)

With ADO only the CursorLocation is fixed.
CursorType and LockType may be coerced into something else if the Provider
does not support that combination.
That is most true for Server-Sided cursors where it is highly
database-specific as to what cursortypes/locktypes are supported.
After you have done a successful Rs.Open() or Command/Connection Execute(),
you can examine the Recordset's CursorType and LockType properties to see
what you really got.
It could be different.

I have here a table of CursorTypes/LockTypes/CursorLocations for SQL Server
Provider, and it lists when the CursorTypes/LockTypes change.
I worked this out by inspection.
I found out that

SQL Server always honors the requested LockType unless Client-sided and
adLockPessimistic
SQL Server always honors the requested CursorType if the table is indexed
SQL Server coerces CursorType if the table is not-indexed depending on
CursorType/LockType requested (Keyset is never honored, it will be either
Dynamic or Static)

And I never checked to find out what happens if you Recordset is based on a
JOIN.

Stephen Howe


.

Relevant Pages

  • Re: ADO Recordset vs Command and AD
    ... The CursorLocation can be set on the connection prior to opening the ... This CursorLocation is applied to the recordset opened on this ... Open method of a Recordset object, so you can assign the cursorType ... CommandText property of a Command object so you can turn on paging and use ...
    (microsoft.public.scripting.vbscript)
  • Re: Current Recordset does not support updating.
    ... it may be a limitation of your ... > that the recordset is editable. ... > above CursorLocation to see if problem went away, ... I changed the CursorType to adOpenKeyset. ...
    (microsoft.public.vb.database.ado)
  • Re: Return a recordset from an MDB
    ... Is it necessary to specify a CursorLocation? ... Execute method of the Command object to return a Recordset, ... If you use a Connection object, ...
    (microsoft.public.scripting.vbscript)
  • Re: VB 6.0 Crystal 8.5 ADO - Passing recordset to report problem
    ... .CursorLocation = adUseClient ... > recordset and then try and pass this recordset to a crystal report ... > source in my report using the 'Set Location' menu option. ... > Dim Report As New CRAXDRT.Report ...
    (microsoft.public.vb.crystal)
  • Re: Download Speed
    ... out, under SQL Server, is server-sided,readonly,forwardonly. ... then you have no control on the RecordSet, you should get the ... get back Forward only, Readonly, Server Sided RecordSet, CacheSize = 1. ... Connection CursorLocation is Client-sided, you should get back Static, ...
    (microsoft.public.data.ado)