Microsoft KB Archive/176564

{|
 * width="100%"|

-

The information in this article applies to:


 * 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
 Start a new project in Visual Basic and choose "Standard EXE." Form1 is created by default. From the Project menu, select References, and then choose Microsoft Remote Data Object 2.0. Add a CommandButton to Form1.  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  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
 * }