Transaction with DataAdapter Update Method

[fscsantos]

Hi
I want to update two datasets within the same transaction. That is rollback both if either fails. I have an example. It uses ExecuteNonQuery. I attempted to do similar code but instead of calls to ExecuteNonQuery I called sqlDataAdapter.Update:

conn.Open()
Dim cbcommand1 As New SqlCommandBuilder(ad1)
Dim cbcommand2 As New SqlCommandBuilder(ad2)
myTrans = conn.BeginTransaction()
ad1.UpdateCommand.Transaction = myTrans
ad2.UpdateCommand.Transaction = myTrans

Try
ad1.Update(dataset1)
ad2.Update(dataset2)
myTrans.Commit()
Catch ex As Exception
MessageBox.Show(ex.Message)
myTrans.Rollback()
End Try


Unfortunately this doesn't appear to work. I get the following error at line:
" ad1.UpdateCommand.Transaction = myTrans "

Error: " Object reference not set to an instance of an object"

Any suggestions greatly appreciated.

 

[nakracreative]

you are using ad1.UpdateCommand.Transaction = myTrans

this command is wrong coz whenever dataadapter uses the update command then it must be supplied the name of the table as argument. e.g.

dataadapter.update("dataset1","table")

 

[fscsantos]

Sorry Nakracreative
I deceived myself already I rectified post at line wrong

 

[TechGnome]

What is the name of your data adaptors? ad1 & ad2 or sqlDataAdapter1 & sqlDataAdapter2???

Tg

 

[nakracreative]

you are using ad1.UpdateCommand.Transaction = myTrans

this command is wrong coz whenever dataadapter uses the update command then it must be supplied the name of the table as argument. e.g.

dataadapter.update("dataset1","table")

 

[galileo]

Error: " Object reference not set to an instance of an object"
This error occur because ad1.UpdateCommand is NOTHING