Re: beginTrans without commitTrans or rollbackTrans

From: Jezebel (dwarves_at_heaven.com.kr)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 19:30:06 +1000

By definition, if the transaction is not committed, then it doesn't happen.

A better way to deal with this is to raise your own error and exit via the
error-handler, instead of using Exit Function:

     If lngRecordsAffected = 0 Then
         test = 1
         Err.Raise vbObjectError
     End If

And a better construction for your clean-up and exit code is:

     cnnDB.CommitTrans
     test = 0

test_exit:
    on error resume next
     Set rst = Nothing
     Set cmd = Nothing
     CloseConnection cnnDB
     Exit Function

 test_Err:
     cnnDB.RollbackTrans
     resume text_exit

"bucket79" <bucket79@daum.net> wrote in message
news:95f72778.0406081716.3ba4f50a@posting.google.com...
> hi
> what if there's beginTrans without commitTrans or rollbackTrans ?
> i'll show some code
>
> Public Function test()
>
> Dim cnnDB As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim cmd As ADODB.Command
> Dim strSQL As String
>
> On Error GoTo test_Err
>
> test = -1
>
> Set cnnDB = OpenConnection("some db")
> Set cmd = CreateObject("ADODB.Command")
> cnnDB.BeginTrans
>
> strSQL = "some update sql....
> With cmd
> Set .ActiveConnection = cnnDB
> .CommandType = adCmdText
> .CommandText = strSQL
>
> .Execute lngRecordsAffected, , adExecuteNoRecords
> End With
>
> If lngRecordsAffected = 0 Then
> test = 1
> Exit Function
> End If
>
> strSQL = "some insert sql....
> With cmd
> Set .ActiveConnection = cnnDB
> .CommandType = adCmdText
> .CommandText = strSQL
>
> .Execute , , adExecuteNoRecords
> End With
>
>
> cnnDB.CommitTrans
> CloseConnection cnnDB
> Set rst = Nothing
> Set cmd = Nothing
> test = 0
> Exit Function
>
> test_Err:
> cnnDB.RollbackTrans
> CloseConnection cnnDB
> If Not rst Is Nothing Then
> Set rst = Nothing
> End If
> If Not cmd Is Nothing Then
> Set cmd = Nothing
> End If
>
> End Function
>
> is this code right thing?
> i mean when lngRecordsAffected is 0, function exit without commitTrans
> or rollbackTrans
> when i test above code, update sql doesn't update table
> does it means there's some rollbackTrnas silencly?
> any post will be apreciated :)

Relevant Pages