Microsoft KB Archive/190109

= How To Keep RDO Cursor Open After Transaction =

Article ID: 190109

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q190109



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.



Visual Basic 6.0 Step-by-Step Example
 Start a new project in Visual Basic and choose "Standard EXE." Form1 is created by default. On the Project menu, click References, and then select 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

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

</li></ol>

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. Make sure that UID has the appropriate permissions to perform this operation on the database.

Additional query words: kbvbp600 kbvbp400 kbvbp500 kbDatabase kbRDO200bug kbRDO

Keywords: kbhowto KB190109

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.