Connections and Transactions

[nashak]

We are logging errors into a table (yeah, I know what happens if db
access throws exceptions).

My parent method sets up a connection, opens connection and begins a
transaction. It then calls several methods and passes them the
connection and transaction objects. Now when an exception is thrown, I
create another method and have to now create a new connection to access
my DB table. I cannot use the original connection as in that case it
wants my sql commands to be bound to the parent transaction. After
logging the error, the exception passes to the try-catch block of
parent method that rollsback the transaction. However, I cannot create
a separate transaction in my LogError method even though I am using a
new connection object since the parent rollback also rollsback the
LogError write to the table. Any suggestion on how to go about fixing
this?

Thanks,

 

[Sahil Malik]

Can you show sample code of your try catch and your logerror routine?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[nashak]

Hello,

Here is the code,


Public Sub Insert(ByVal drNewRow As DataRow, _
ByVal myconnection as sqlconnection, _
ByVal mytransaction as sqlTransaction)
......
daData.InsertCommand.Connection = myConnection
daData.InsertCommand.Transaction = myTransaction
daData.Update(dsData, myTable)

Catch ex As Exception

Dim newdbobject = New DBUtility
newdbobject.LogError(ex, myUserID)


Finally
.........

End Sub




Public Sub LogError(ByVal ex As Exception, _
ByVal userID As Long)
....
Try
myConnection1 = GetConnection()
myConnection1.Open()

cmdSelectData1.CommandText = "select * from Table"
cmdSelectData1.CommandType = CommandType.Text
cmdSelectData1.Connection = myConnection1

**Cannot create a new transaction and use BeginTransaction on this
connection

drRow1 = dsData1.Tables("ExceptionLog").NewRow
....
dsData1.Tables("ExceptionLog").Rows.Add(drRow1)
...
daData1.InsertCommand.Connection = myConnection1
**Cannot bind the insert command to local transaction. Parent method
rollback also rolls this insert back

daData1.Update(dsData1, "ESYSExceptionLog")

Catch exp As Exception
.....
Finally
myConnection1.Close()

End Try
End Sub


Please let me know if you need additional information.

 

[Sahil Malik]

Yes I need some more info

What is the code for "GetConnection"


- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[nashak]

oops,

the things works

I was copying and pasting and realized that i didn't copy commit()

 

[Sahil Malik]

Okay .. that's the easiest help I've given out so far

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[nashak]

LOL