Microsoft KB Archive/179226

= PRB: RDO: CommitTrans/RollBackTrans Closes Cursor Causing Error =

Article ID: 179226

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q179226



SYMPTOMS
Attempting to perform any action on an rdoResultset object connected to a SQL Server database after committing or rolling back a transaction on the rdoResultset's rdoConnection object (or simply the act of committing or rolling back the transaction), will result in the following error:

40002

S1010:[Microsoft][ODBC SQL Server Driver]Function sequence error

Similar errors can be expected with other servers.



CAUSE
Depending on the cursor type, the error above will be raised either on the CommitTrans or RollBackTrans or when any attempt is made to access a property or method of the rdoResultset object after the CommitTrans or RollBackTrans.

This occurs because many servers, including SQL Server, default to closing or deleting cursors at the end of the transaction. Any action on the rdoResultset causes an action on the now-closed cursor, which causes the error. When using server-side keyset or dynamic cursors with SQL Server, RDO will erroneously call SQLExtendedFetch on the CommitTrans or RollBackTrans statement, raising this error.



RESOLUTION
While this error can be trapped and handled, the better solution is to change the connection option so that the cursors are not closed at the end of the transaction. This behavior can be changed for most servers. The code to do this with SQL Server follows. For other servers, please consult the ODBC driver and server documentation for the equivalent call.

The following code works around the problem:

 Start a New Project. Add a command button to the default form.  Add the following to the General Declaration section: Option Explicit 'SQL Server specific connection options Private Const SQL_PRESERVE_CURSORS As Long = 1204 Private Const SQL_PC_ON As Long = 1 Private Const SQL_PC_OFF As Long = 0

'Possible ODBC function returns Private Const SQL_ERROR As Integer = -1 Private Const SQL_INVALID_HANDLE As Integer = -2 Private Const SQL_NO_DATA_FOUND As Integer = 100 Private Const SQL_SUCCESS As Integer = 0 Private Const SQL_SUCCESS_WITH_INFO As Integer = 1

Private Declare Function SQLSetConnectOption Lib "odbc32.dll" _ (ByVal hdbc As Long, ByVal fOption As Integer, pvParam As Any) _ As Integer

Private Declare Function SQLGetConnectOption Lib "odbc32.dll" _ (ByVal hdbc As Long, ByVal fOption As Integer, pvParam As Any) _ As Integer

  Add the following code to the click event of the CommandButton: (The server will need to be changed.) Private Sub Command1_Click Dim intRet As Integer Dim lngConnOption As Long Dim Conn As rdoConnection Dim Rslt As rdoResultset

'Make DSN-less connection. CHANGE SERVER UID PWD for your server Set Conn = rdoEnvironments(0).OpenConnection(_           "", rdDriverComplete, False, _       "DRIVER={SQL Server};SERVER=hoohaa;DSN=;DATABASE=pubs;UID= ;PWD= ;") 'Getting connection option intRet = SQLGetConnectOption(Conn.hdbc, SQL_PRESERVE_CURSORS, _          lngConnOption) If SQL_SUCCESS <> intRet Then MsgBox "SQLGetConnectOption Failed", "ERROR", vbCritical Conn.Close Exit Sub End If      'display it       Select Case lngConnOption Case SQL_PC_OFF MsgBox "Cursor Behavior: Close",, "Connection Option Value" Case SQL_PC_ON MsgBox "Cursor Behavior: Maintain",, "Connection Option Value" Case Else MsgBox "ERROR: Unknown Connection Option",, _ "Connection Option Value", vbCritical End Select 'uncomment the next 2 lines to stop error 'intRet = SQLSetConnectOption(Conn.hdbc, SQL_PRESERVE_CURSORS, ByVal _    'SQL_PC_ON) If intRet <> SQL_SUCCESS Then MsgBox "SQLSetConnectOption Failed",, "ERROR", vbCritical Conn.Close Exit Sub End If     MsgBox "Connection Option Set to SQL_PC_ON",, _ "Connection Option Status" Set Rslt = Conn.OpenResultset("select * from authors", _       rdOpenDynamic, rdConcurValues) Conn.BeginTrans Rslt.MoveFirst Rslt.Edit Rslt.rdoColumns("address").Value = "test" Rslt.Update 'will error on this line if connection option not set Conn.RollbackTrans Rslt.MoveLast Rslt.Close Conn.Close End Sub

 Run the project and note that the code fails as indicated. Uncomment the noted lines. Run the project again and note that the code does not fail.</li></ol>

<div class="status_section">

STATUS
This behavior is not by design, but since a workaround exists, it will not be fixed.

<div class="references_section">