How to use transaction

[vipinkrshamra]

Hi ,

I am trying to use use dataadapter.update with in trascantion. But it is failing with following exception. "System.InvalidOperationException: Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

If I remove trasaction from my code everything works fine but once I use trascation same code fails.

Below code fail at "da.Update(ds, TableName)" if transactions are used.

***********

Private Function Save(ByVal da As SqlDataAdapter, ByVal ds As DataSet, ByVal TableName As String) As Boolean
SaveMainTable = False
Dim intSuccessfulInsertion As Integer
Dim myTrans As SqlTransaction

Try
Dim bldr1 As SqlCommandBuilder = New SqlCommandBuilder(da)
da.InsertCommand = bldr1.GetInsertCommand

da.InsertCommand.Connection.Open()
myTrans = da.InsertCommand.Connection.BeginTransaction

intSuccessfulInsertion = da.Update(ds, TableName) -->Failing here
myTrans.Commit()
SaveMainTable = True

Catch sqlex As SqlException
myTrans.Rollback()

Catch ex As Exception
WriteLogFile("Problem in updating database." & ex.ToString, "ProcessHTML")
End Try
End Function

 

[vipinkrshamra]

I have wasted lots of time to troubleshoot this problem but with no result. I would appreciate if anyone could help me on this.

 

[SMURF75]

Im just reading of some code I havent written myself, but i would suggest you try

myTrans = da.InsertCommand.Connection.BeginTransaction

bldr1.Transaction = myTrans '<----Add this line

intSuccessfulInsertion = da.Update(ds, TableName)

But this is just me trying to figure out what my co-workers have written and they are on vacation


- - - - - - - - - - - - - - - - - -
Im three apples high, Im blue, and i most certainly like that cold beer that should be every mans right after a hard days work!

 

[vipinkrshamra]

Thanks a lot for helping me on this issue.

In my code "bldr1" is object of "SqlCommandBuilder". I could not find "Transaction " property for "SqlCommandBuilder".

I may be missing something.
Please advice.

 

[jebenson]

You begin the transaction, but never tell the data adapter's InsertCommand that it should use this transaction. Try this:

myTrans = da.InsertCommand.Connection.BeginTransaction

[red]da.InsertCommand.Transaction = myTrans[/red] '<---Add this line

intSuccessfulInsertion = da.Update(ds, TableName)

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[vipinkrshamra]

Thanks a lot jebenson for pointing out my mistake. That was the problem.

I appreciate your time as well as of SMURF75.