Transaction error on call to Commit() when select has been executed inside transaction

Question

I get this error at the Commit of a transaction in a desktop application:

This OleDbTransaction has completed; it is no longer usable.

Other posts I have seen with similar error suggests this can occur if it takes a long time, or contains large amounts of data. This is not the case here. Logging tells me it takes 140 ms from Begin to Commit and about 10 commands executed inside the transaction.

It is using an Oracle database.

This class is a simplified version of my database class:

Class MyDatabase 
Private mConnection AS OleDbConnection
Private mTransaction AS OleDbTransaction

Function Value(ByVal piSql As String) As Integer
  Try
    Dim lCommand As OleDbCommand
    lCommand = New OleDbCommand(piSql, mConnection)
    If mTransaction IsNot Nothing Then
        lCommand.Transaction = mTransaction
    End If
    Dim lValue = lCommand.ExecuteScalar()
    If Not lValue Is Nothing Then
      WriteLog(lValue.ToString(), 3, "Value Returned")
      Return lValue.ToString()
    Else
      WriteLog("<null>", 3, "Value Returned (null)")
      Return ""
    End If
  Catch ex As Exception
      WriteLog(ex.Message)
  End Try
End Function
Function ExecuteSql(ByVal piSql As String) As Integer
  Try
    Dim lCommand As OleDbCommand
    lCommand = New OleDbCommand(piSql, mConnection)
    If mTransaction IsNot Nothing Then
      lCommand.Transaction = mTransaction
    End If
    Return lCommand.ExecuteNonQuery()
  Catch ex As Exception
    WriteLog(ex.Message)
  End Try
End Function

Public Sub BeginTransaction()
  Try
    mTransaction = mConnection.BeginTransaction()
  Catch ex As Exception
    WriteLog(ex.Message)
  End Try
End Sub

Public Sub Commit()
  If Not mTransaction Is Nothing Then
    Try
      mTransaction.Commit()
    Catch ex As Exception
      WriteLog(ex.Message)
    End Try
    mTransaction.Dispose()
    mTransaction = Nothing
  End If
End Sub

Public Sub Rollback()
  If Not mTransaction Is Nothing Then
    Try
      mTransaction.Rollback()
    Catch ex As Exception
      WriteLog(ex.Message)
    End Try
    mTransaction.Dispose()
    mTransaction = Nothing
  End If
End Sub

End Class

Calling code (simplified):

mDatabase.BeginTransaction()
mOrderId = mDatabase.Value("select max(order_id) from ler_order")
mDatabase.ExecuteSql("insert into ler_order (order_id,status,message,plotter,reference,paper_size,orientation,plot_scale,format,usr,email,no_of_copies,date_time,uservar1,uservar2,uservar3,ell,nll,eur,nur,coord_type,graveforespoergselanmodningid,graveforespoergselnr,oprettetdato,aendretdato,graveartnavn,andengraveart,lek_virksomhed,lek_navn,lek_adresse,lek_postnr,lek_postdistrikt,lek_land,lek_telefon,lek_mobiltelefon,lek_telefax,lek_email,ga_navn,ga_adresse,ga_postnr,ga_postdistrikt,ga_land,ga_telefon,ga_mobiltelefon,ga_telefax,ga_email,gak_id,gak_virksomhed,gak_navn,gak_adresse,gak_postnr,gak_postdistrikt,gak_land,gak_telefon,gak_mobiltelefon,gak_telefax,gak_email,emailafsendt,konverteringsstatus)  values (101633,0,null,'LER','10d6d8bc-b9b2-44bb-84bf-ceca42a0970a',null,0,0,null,null,null,0,'09-05-2011 13:25:33',null,null,'VAND',-259954.967,145092.123,-259802.657,145147.225,1,'10d6d8bc-b9b2-44bb-84bf-ceca42a0970a',425950,'09-05-2011 13:20:27','09-05-2011 13:20:58','Gravemaskine',null,null,'Ledningsoplysning','Kokbjerg',null,'Kolding',null,'59 23 44 55',null,null,'info@mail.com','FORSYNINGSLEDNINGER','vej 12','1700','Nyberg','NO','21491697',null,null,'mail@info.com','051055f4-ea2a-4cb3-a016-6f6477e6a342','MUNCK FORSYNINGSLEDNINGER A/S','Jon Andersen','vej 38 B','7100','Vejle','NO',null,'23681515','76409220','mail@info.com','09-05-2011 13:20:58','OK')")
mDatabase.ExecuteSql("delete from ler_order_coord where order_id = 101633")
mDatabase.ExecuteSql("insert into ler_order_coord (order_id,polygon_no,seq_no,east,north)  values (101633,1,1,-259954.967,145120.599)")
mDatabase.ExecuteSql("insert into ler_order_coord (order_id,polygon_no,seq_no,east,north)  values (101633,1,2,-259951.933,145092.123)")
mDatabase.ExecuteSql("insert into ler_order_coord (order_id,polygon_no,seq_no,east,north)  values (101633,1,3,-259802.657,145111.956)")

mDatabase.Commit() 'This is where the error occurs

EDIT: See my answer for how I solved this.
I have a follow-up question on this: Is it not allowed to run a select inside a transaction like this? Or can it be done by running the transaction in a specific isolation level (I see that the BeginTransaction method has an optional parameter for doing this) ? ..Or some other sollution..? In my case, it was not a problem to move the select to run before the transaction started, but what if you need to run selects that must run inside the transaction?

Answer 1

I found an answer to another question, which helped me along the way: How to check if Dotnet transaction is rolled back?

I implemented the TransactionScope to have better control on my transaction, and I noticed a new error message in the log saying :

The Transaction Manager is not available. (Exception from HRESULT: 0x8004D01B)

This error was triggered on the select statement:

mOrderId = mDatabase.Value("select max(order_id) from ler_order")

I moved the select to before the transaction begins, and now it works!

EDIT:
I discovered that what triggered this error might not have been the use of TransactionScope, but that I tried to set lCommand.Transaction = mTransaction on the select command. This is apparently not allowed when the command is not an action command. This is however not the original problem, because the error on Commit was there before I tried to set the active transaction on the select command. This is just something I tried along the way trying to fix it.