DOCUMENT:Q176564  11-NOV-1997  [vbwin]
TITLE   :PRB: RDO 2.0 CommitTrans/RollbackTrans Closes Resultset
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:vb5all kberrmsg

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
---------------------------------------------------------------------

SYMPTOMS
========

In Visual Basic 5.0, 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};
         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.
======================================================================
Keywords          : vb5all kberrmsg
Technology        : kbrdo
Version           : WINDOWS:5.0
Platform          : WINDOWS
Issue type        : kbprb
Solution Type     : kbpending

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1997.