PRB: ODBCDirect Cursor Not Valid After Transaction Commits

ID: Q217192


The information in this article applies to:


SYMPTOMS

Using ODBCDirect with Visual Basic, you receive the following error on accessing a recordset object that was created inside of a transaction that has been committed or rolled back:

3670 Cursor is not valid


CAUSE

This error occurs because you are using server-side cursors on your connection and the cursor is being closed when the transaction is committed or rolled back. Whether a server-side cursor is closed on a transaction commit or rollback depends on the database driver that you are using. For the SQL Server driver, the default is to close the server-side cursor on the commit or rollback of a transaction.


RESOLUTION

When using ODBCDirect, the only two workarounds are the following:

  1. Use client side cursors.


  2. Requery the information (rs.requery) after a commit or rollback.

    NOTE: For this workaround to work, MSDTC must not be running. With some other data access APIs, such as RDO, you can manually set an option to preserve server-side cursors. You set this option using either the SQLSetConnectOption or the SQLSetConnectAttr ODBC API functions. These options must be set after the connection handle is allocated but before the connection has been made. With ODBCDirect it is not possible to have access to the connection handle before the connection has been established, so it is not possible to set the option.



STATUS

This behavior is by design.


MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new Visual Basic Standard EXE program. Form1 is created by default.


  2. Create a reference to the Microsoft DAO 3.50 or 3.51 Object Library.


  3. Place a CommandButton on the default form.


  4. Add the following code to the CommandButton Click event.

    NOTE: You will need to modify the query and connection string so that it will use your database:


  5. 
        On Error GoTo errhandler
        
        Dim wrkODBC As dao.Workspace
        Dim cn As dao.Connection
        Dim rs As dao.Recordset
        Dim szConnect As String
       
        szConnect = "ODBC;Driver={SQL Server};Server=(local);Database=pubs;uid=sa;pwd="
        
        Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
        wrkODBC.DefaultCursorDriver = dbUseServerCursor
        
        'You can work around this problem by uncommenting this next line.
        'The client batch cursor will preserve the cursor after the
        'transaction commits.
        'wrkODBC.DefaultCursorDriver = dbUseClientBatchCursor
        Set cn = wrkODBC.OpenConnection("", dbDriverNoPrompt, False, szConnect)
           
        wrkODBC.BeginTrans
            Set rs = cn.OpenRecordset("SELECT * FROM AUTHORS")
            Debug.Print "Recordset Returned: " & rs(0).Value
        wrkODBC.CommitTrans
        
        ' If using server-side cursors, (dbUseServerCursor) this line
        '  can be uncommented to requery the data after the transaction.
        ' NOTE: If MSDTC is running, this workaround will fail.
        '  With MSDTC running you will need to close and then
        '  reopen the connection before issuing the requery.
        'rs.Requery
        Debug.Print rs(0).Value   '<<Invalid Cursor Error Occurs here
        
        rs.Close
        cn.Close
        Set rs = Nothing
        Set cn = Nothing
    
        Exit Sub
        
    errhandler:
    
        'Iterate through the Errors collection to get detailed error information.
        '-----------------------------------------------------------------------
        Dim MyErr As Error
        For Each MyErr In DBEngine.Errors
            Debug.Print MyErr.Number & " -- " & MyErr.Description
        Next MyErr
     
  6. Run the code and note that the error occurs when you try to access the recordset after the transaction has been committed. Uncomment the appropriate lines to see the workaround mentioned above.



REFERENCES

SQL Server 6.5/7.0 Books Online

For additional information, please see the following articles in the Microsoft Knowledge Base:

Q176564 PRB: RDO 2.0 CommitTrans/RollbackTrans Closes Resultset

Q190109 HOWTO: Keep RDO Cursor Open After Transaction

Additional query words:


Keywords          : kbDAO350 kbVBp500 kbVBp600 
Version           : WINDOWS:5.0,6.0
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: March 13, 1999