Transaction Saves Before Commit

[jcb1269]

What would cause a transaction to save before the commit statement?

I'm doing the following, I have a subroutine in a class called clsConnection, the sub looks like this;

Public Sub OpenTransactionConnection()

If g_cnnRO.State = ConnectionState.Closed Then

'SETUP THE TRANSACTION
g_cnnRO = OpenMySqlConnection()
g_cmdUpdateAll.Connection = g_cnnRO
g_cmdUpdateAll.Transaction = g_Transaction
g_cmdUpdateAll = g_cnnRO.CreateCommand()
g_cmdUpdateAll.CommandType = CommandType.Text

'START THE TRANSACTION
g_Transaction = g_cnnRO.BeginTransaction(IsolationLevel.ReadCommitted)

End If

End Sub

I call this at the beginning of the update/add. I'm executing the sql statement for the update/add like so; ( the cnnTransaction is being set the to g_cnnRO which is the transaction connection object)

Protected Function ExecuteNonQuery(ByVal NonSelectQuery As String, ByVal cnnTransaction As MySqlConnection) As Integer
            '   protected scope to make available only to child classes

            ExecuteNonQuery = 0
            mstrErrorMessage = ""

            Dim myMySQLCommand As MySqlCommand = cnnTransaction.CreateCommand
            myMySQLCommand.CommandText = NonSelectQuery
            myMySQLCommand.CommandType = CommandType.Text

            Try
                ExecuteNonQuery = myMySQLCommand.ExecuteNonQuery()
            Catch ex As Exception
                mstrErrorMessage = ex.Message
            Finally
                myMySQLCommand.Dispose()
                myMySQLCommand = Nothing
            End Try
           
End Function

I've also tried:

Protected Function ExecuteNonQuery(ByVal NonSelectQuery As String, ByVal cnnTransaction As MySqlConnection) As Integer
            '   protected scope to make available only to child classes

            ExecuteNonQuery = 0
            mstrErrorMessage = ""

            Try
                g_cmdUpdateAll.CommandText = NonSelectQuery
                ExecuteNonQuery = g_cmdUpdateAll.ExecuteNonQuery()
            Catch ex As Exception
                mstrErrorMessage = ex.Message
            End Try
           
End Function

This function resides in a base class called clsDataAccess. The clsConnection class inherits this class.

I use this same code in a different program, the only difference is the opentransaction is done in a module and the executenonquery is done in each function.

When this runs it adds the record before the commit happens. I'm confused as to what I'm doing wrong. Any help would be great!

Thanks in advance!

jim