Transaction error!

[MrNorth]

I need help with a piece of code I am writing... I thought this was really genious, but it turns out it doesn't work. Howcome?

 

Here is the code:

 

Try
           m_conOleDb.ConnectionString = g_ConnStrings.GetInstance.GetConnectStringByPhase(Request.DesignPhase)
           m_conOleDb.Open()

           With cmdOleDb
               .Connection = m_conOleDb
               .CommandText = Request.Command
               .CommandType = Request.CommandType
           End With

           'If any parameters exist in the Request object, add them
           If Request.Parameters.Count > 0 Then
               For Each oParam In Request.Parameters
                   prmOleDb = cmdOleDb.Parameters.Add(oParam.ParamName, oParam.ParamValue)
               Next
           End If

           'We check if the command uses a transaction or not
           If Request.Transactional Then
               tranOleDb = m_conOleDb.BeginTransaction
           End If

           'Create a dataadapter and pass the command as a parameter
           daOleDb = New OleDbDataAdapter(cmdOleDb)
           daOleDb.Fill(oDataSetOleDb.ReturnedDataSet)

           Return oDataSetOleDb

       Catch exOleDb As OleDbException
           Request.Exception = exOleDb
           If Request.Transactional Then
               tranOleDb.Rollback()
           End If
       Catch ex As Exception
           Request.Exception = ex
           If Request.Transactional Then
               tranOleDb.Rollback()
           End If
       Finally
           If Request.Transactional Then
               tranOleDb.Commit()
           End If
           If m_conOleDb.State = ConnectionState.Open Then
               m_conOleDb.Close()
           End If
       End Try

 

the problem is wiht the transactions. I get an exception on the line " tranOleDb.Commit()"

 

saying "This OleDbTransaction has completed; it is no longer usable"

 

 

What is the problem? If I don't use transactions, it works just great :)

 

kind regards

Henrik

[PlausiblyDamp]

The finally block will get executed if the code in the try block suceeds or fails, if it fails one of the tranOleDB.Rollback() statements will get executed and then it will try to call tranOleDb.Commit() in the finally block.

You may want to move the commit part to just before the line

 

Return oDataSetOleDb in the try block.

[MrNorth]

thanks for the reply, but that resulted in an empty dataset :(

 

If I run the code without transactions, it works great

 

 

If you are curious this is a data tier based on singleton and abstract factory patterns... that is why I have wrapper classes for everything, even datasets and datareaders

 

 

yours

Henrik

[samsmithnz]

Why do you need to use a transaction here? It looks like you're just reading data, and you don't need a transaction for that.

 

A transaction is best when you have to send multiple changes, and you want ALL of them to succeed or all to fail.

 

But you definetly need to move the commit out of the finally statement, even if theres an error it will run.

[MrNorth]

well this data tier is using command objects and can execute both SPs textcommands and tablecommands. It is not only for reading data...

 

kind regards

HEnrik

[PlausiblyDamp]

If you step through the code does it throw an exception anywhere?