PRB: Use Open Method to Change CursorType and LockType

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

Symptoms

If a recordset is opened through a connection object or a command object, the CursorType defaults to adOpenForwardOnly, and the LockType defaults to adLockReadOnly if the cursorlocation is adUseServer. Changing these properties before creating the recordset does not cause an error, but the properties are not preserved.

Cause

When execute is used to open a recordset, it is creating a recordset with default properties.

Resolution

Use the open method to create the recordset. The preceding properties of the recordset may then be set before the open statement or they may be set with the open statement.

Status

This behavior is by design.

More Information

The default CursorLocation is adUseServer, and the default LockType is adLockReadOnly. The default CursorType for adUseServer is
adOpenForwardOnly.


The CursorLocation can be set on the connection prior to opening the recordset. This CursorLocation is applied to the recordset opened on this connection. If the connection's CursorLocation is set to adUseClient, the resulting recordset CursorLocation would be adUseClient. The CursorType for adUseClient is always adOpenStatic. The default LockType remains
adLockReadOnly.

The following sample code connects to SQL server's Pubs database. The ConnectionString arguments need to be changed to match your setup.

Steps to Reproduce Behavior

  1. Create a Standard EXE project and select Microsoft Active Data Objects Library (on the Project menu, click References).
  2. Place seven command buttons on Form1.
  3. Paste the following code in the Form1 code window:
       Option Explicit
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

    Private Sub Command1_Click()
    With cn
    .CursorLocation = adUseServer
    .ConnectionString = "dsn=yourdatasourcename"uid=yourUserId;pwd=yourPassword
    .Open
    End With
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    End Sub

    Private Sub Command2_Click()
    Set rs = cn.Execute("select * from authors")
    End Sub

    Private Sub Command3_Click()
    With cmd
    .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = "select * from authors"
    End With
    Set rs = cmd.Execute
    End Sub

    Private Sub Command4_Click()
    rs.Open "select * from authors", cn
    End Sub

    Private Sub Command5_Click()
    Debug.Print "CursorLocation " & rs.CursorLocation; ""
    Debug.Print "cursortype " & rs.CursorType
    Debug.Print "locktype " & rs.LockType
    End Sub

    Private Sub Command6_Click()
    If rs.State = 1 Then
    rs.Close
    End If
    If Not rs Is Nothing Then
    Set rs = Nothing
    End If
    rs.CursorLocation = adUseServer
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    End Sub

    Private Sub Command7_Click()
    If Not rs Is Nothing Then
    Set rs = Nothing
    End If
    If cn.State = 1 Then
    cn.Close
    End If
    If Not cmd Is Nothing Then
    Set cmd = Nothing
    End If
    If Not cn Is Nothing Then
    Set cn = Nothing
    End If
    Unload Me
    End Sub

    Private Sub Form_Load()
    Command1.Caption = "connect"
    Command2.Caption = "connection.execute"
    Command3.Caption = "command.execute"
    Command4.Caption = "open recordset"
    Command5.Caption = "print recordset attributes"
    Command6.Caption = "close recordset"
    Command7.Caption = "quit"
    End Sub
  4. Run the code, and click Connect. Make sure the Immediate Window is open; if it is not open, select it from the View menu.
  5. Click connection.execute; then select the print recordset attributes and close recordset. Note the print out is in the immediate window.
  6. Click command.execute, followed by selecting print recordset attributes and close recordset. Note the print out is in the Immediate Window.
  7. Click the open recordset followed by selecting print recordset attributes and the close recordset. Again, note the print out in the Immediate Window.
You may test the above sequences for the Connection, Command, and Open methods in any order, and the corresponding print outs will not change for Cursorlocation, Cursortype, and Locktype.


The following table lists the meanings of these numbers:


Cursorlocation Cursortype Locktype

----------------------------------------------

0 N/A adforwardonly N/A
1 N/A adopenkeyset readonly
2 aduseserver adopendynamic pessimistic
3 aduseclient adopenstatic optimistic

References

Microsoft Developer Network CD, search on: "locktype"; "ADO Data Control Constants."
Propiedades

Id. de artículo: 188857 - Última revisión: 03/02/2005 - Revisión: 1

Comentarios