behavior of beginTrans and commitTrans

[lok1234]

Dear all,

I would like to make sure my concept of beginTrans and CommitTrans of ADODB.connection is correct, please comment on my question. Thanks in advance.

I have the following codes:

Public gConnect_Main As ADODB.Connection  'this is the connection object

  
Dim astrsql As String
Dim v_remark As String

gConnect_Main.BeginTrans

'assume the remark of ID="A1" has the original values = "lok"

astrsql = "update header set remark='lok1234' where ID='A1' "
gConnect_Main.Execute astrsql

astrsql = "select remark from header where ID='A1' "

v_remark = Fn_Select(gConnect_Main, astrsql)

Debug.Print v_remark

gConnect_Main.CommitTrans

Public Function Fn_Select(pConnect As ADODB.Connection, ByVal aSQLline As String) As Variant
On Error GoTo Err
Dim aRs As New ADODB.Recordset
If fn_SqlOpenRS(aRs, aSQLline, adCmdText, pConnect, adUseServer, adOpenForwardOnly) Then
    If Not aRs.EOF Then
        Fn_Select = IIf(IsNull(aRs.Fields(0).Value), 0, aRs.Fields(0).Value)
    End If
    Call fn_closeADOrs(aRs)
End If
Exit Function
Err:
    MsgBox Err.Description, vbCritical, Err.Source
    Fn_Select = 0
    Call fn_closeADOrs(aRs)
End Function

As far as I know, begintrans would only update the value if and only if committrans occurs. I would like to know, why "debug.print v_remark" prints the new value: "lok1234" ? I suppose this value is not yet updated in the database as commitTrans is not yet executed.

Is this related to the fact that in the function: "fn_Select" i use "adUseServer" and "adOpenForwardOnly"?

[Pradeep1210]

As far as I know, begintrans would only update the value if and only if committrans occurs. I would like to know, why "debug.print v_remark" prints the new value: "lok1234" ? I suppose this value is not yet updated in the database as commitTrans is not yet executed.

Is this related to the fact that in the function: "fn_Select" i use "adUseServer" and "adOpenForwardOnly"?

A transaction occurs in the database. So it will always give you the new updated values when you query it from the same connection. However if you close the connection without calling the UpdateTrans method, everything will be rolled back.

[lok1234]

A transaction occurs in the database. So it will always give you the new updated values when you query it from the same connection. However if you close the connection without calling the UpdateTrans method, everything will be rolled back.

i see. Do you mean within the same connection, every query can "see" the updated data? No matter I am using adOpenForwardOnly and aduseServer as the cursortype and cursorlocation?

Furthermore, is it related to close the recordset?

[Pradeep1210]

A transaction relates to a connection. You can open as many recordsets as you want on that connection and you will see the changed values. As long as the connection is open, you won't feel the difference. Once you close the connection, the transaction will be rolled back if it has not been explicitly committed.