PRB: RDO 2.0 CommitTrans/RollbackTrans Closes Resultset

• Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0

SYMPTOMS

In Visual Basic 5.0 and higher, after executing the CommitTrans/RollbackTrans methods of the RDO connection object, the following error occurs if you try to move the cursor around (for example, rs.MoveNext), query the column value (for example, debug.print rs(0)), or refresh the cursor (for example, rs.Move 0):

Run-time error '40088':
No open cursor or cursor closed.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

By default, the SQL Server ODBC driver will automatically close your cursor after a call to commit or rollback. This can be avoided (at your own risk) by setting a driver-specific statement option using the SQLSetConnectOption API. The option is documented in the SQL Server ODBC driver Help file, which you can also get when installing the SQL Server Books Online. Because of the problem noted above, SQLSetConnectionOption does not take effect.

The following code example demonstrates how to work around the problem by using the Server-side cursor driver and the rdExecDirect option of the connection object. SQL Server and the Pubs sample database is used here.

Step-by-Step Example

1. Start a new project in Visual Basic and choose "Standard EXE." Form1 is created by default.



2. From the Project menu, select References, and then choose Microsoft Remote Data Object 2.0.



3. Add a CommandButton to Form1.



4. Paste the following code in the General Declaration section of Form1:

   
         Option Explicit
         Const SQL_PRESERVE_CURSORS As Long = 1204
         Const SQL_PC_ON As Long = 1
         Const SQL_PC_OFF As Long = 0
   
         Private Declare Function SQLSetConnectOption Lib "odbc32.dll" _
         (ByVal hdbc&, ByVal fOption%, ByVal vParam As Any) As Integer
   
         Private Sub Command1_Click()
   
            Dim en As rdoEnvironment
            Dim cn As rdoConnection
            Dim rs As rdoResultset
            Dim strConnect As String
            Dim strSQL As String
            Dim intRet As Integer
   
            strConnect ="Driver={SQLServer};"
            strConnect = strConnect & _
            "Server=MyServer;Database=Pubs;Uid=sa;Pwd=;"
            Set en = rdoEnvironments(0)
            en.CursorDriver = rdUseServer
   
            Set cn = New rdoConnection
            intRet = SQLSetConnectOption(cn.hdbc, SQL_PRESERVE_CURSORS, _
            SQL_PC_ON)
            cn.Connect = strConnect
            cn.EstablishConnection rdDriverNoPrompt, False
   
            strSQL = "Select au_id, au_lname from authors"
            Set rs = cn.OpenResultset(Name:=strSQL, Type:=rdOpenKeyset, _
            LockType:=rdConcurValues)
   
            cn.Execute "Begin Transaction", rdExecDirect
            rs.MoveFirst
            rs.Edit
            rs(1) = "Vermont"
            rs.Update
            cn.Execute "Commit Transaction", rdExecDirect
            Debug.Print rs(1)
   
         End Sub
    




5. Note that you must change your Server, IUD, and Pwd parameters in the connect string.

Additional query words: kbVBp500 kbVBp600 kbdse kbDSupport kbVBp

Keywords          : 
Version           : 
Platform          : WINDOWS 
Issue type        : kbprb