DOCUMENT:Q190109  09-JAN-2000  [vbwin]
TITLE   :HOWTO: Keep RDO Cursor Open After Transaction
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:4.0,5.0,6.0
OPER/SYS:
KEYWORDS:kbGrpDSVBDB

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

 - Microsoft Visual Basic Enterprise Edition for Windows, versions 4.0, 5.0, 6.0 
-------------------------------------------------------------------------------

SUMMARY
=======

By default, the SQL Server ODBC driver automatically closes cursor after a call
to commit or rollback. If you reference the RDO resultset afterwards, for
example, rs.MoveNext, Debug.Print rs(0), the following error occurs:

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

This article demonstrates, in Visual Basic 6.0, how you can keep the cursor open
by setting a driver-specific statement option, using the SQLSetConnectOption API
before establishing the connection. This option is documented in the SQL Server
ODBC Driver Help file, which you can also obtain when installing the SQL Server
Books Online.

However, this approach does not work in Visual Basic 5.0 using RDO (Msrdo20.dll
version 5.xx.xxxx) due to a known RDO bug. To reference the resultset after the
transaction, you must either Requery the resultset or use the Server-side cursor
driver and the rdExecDirect option of the connection object.

In Visual Basic 4.0, the resultset remains open after transaction.

MORE INFORMATION
================

Visual Basic 6.0 Step-by-Step Example
-------------------------------------

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

2. On the Project menu, click References, and then select 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

         Private Declare Function SQLSetConnectOption Lib "odbc32.dll" (ByVal
            hdbc&, ByVal fOption%, ByVal vParam As Any) As Integer
         Dim WithEvents cn As rdoConnection
         Dim rs As rdoResultset

         Private Sub cn_BeforeConnect(ConnectString As String, Prompt As
           Variant)
            Dim intRet As Integer
            intRet = SQLSetConnectOption(cn.hdbc, SQL_PRESERVE_CURSORS,
             SQL_PC_ON)
         End Sub

         Private Sub Command1_Click()
            Dim strConnect As String
            Set cn = New rdoConnection
            strConnect = "DRIVER={SQL
              Server};SERVER=MyServer;DATABASE=pubs;UID=sa;PWD="
            cn.Connect = strConnect
            cn.EstablishConnection
            Set rs = cn.OpenResultset("SELECT * FROM authors", rdOpenKeyset,
              rdConcurValues)

            cn.BeginTrans
            rs.Edit
            rs(1) = "Vermont"
            rs.Update
            cn.CommitTrans
            Debug.Print rs(1)
         End Sub

Visual Basic 5.0 Step-by-Step Example
-------------------------------------

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

2. On the Project menu, click References, and then select 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

         Dim cn As rdoConnection
         Dim rs As rdoResultset

         Private Sub Command1_Click()
            Dim strConnect As String
            Set cn = New rdoConnection
            strConnect = "Driver={SQL
              Server};Server=yourserver;Database=Pubs;Uid=sa;Pwd=;"
            With cn
              .CursorDriver = rdUseServer
              .Connect = strConnect
              .EstablishConnection
            End With
            Set rs = cn.OpenResultset("Select * from Authors", rdOpenKeyset, _
               rdConcurValues)

            cn.Execute "Begin Transaction", rdExecDirect
            Debug.Print rs(1)
            rs.Edit
            rs(1) = "Vermont"
            rs.Update
            cn.Execute "Commit Transaction", rdExecDirect
            Debug.Print rs(1)
         End Sub

Note that Authors table in SQL Server Pubs database is used here for testing
purposes. You must change your Server, Uid, and Pwd parameters in the connect
string.

Additional query words: kbvbp600 kbvbp400 kbvbp500 kbDatabase kbRDO200bug kbRDO

======================================================================
Keywords          : kbGrpDSVBDB 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600 kbVB400Search kbVB400
Version           : WINDOWS:4.0,5.0,6.0
Issue type        : kbhowto

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

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 2000.