Please help me ...

[fscsantos]

Update two datasets in same transaction

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.

 

[jmcilhinney]

If you are using a CommandBuilder then there is no UpdateCommand to attach the Transaction to. The SQL statements are generated on-the-fly, hence the NullReferenceException. This is the main reason I stopped using CommandBuilders myself. I think you can actually call a method of the CommandBuilder to get the commands that would be generated and then attach them to the DataAdapter, but I've never tried it. In my view, CommandBuilders make you lazy, give less control and should only be used in the simplest of scenarios.

 

[fscsantos]

jmcilhinney,
Very Grateful for the post!
Thanks

 

[nakracreative]

ad1.Update(dataset1) ;
ad2.Update(dataset2)


while using the commandbuilder object, you must specify the tablename alongwith the dataset name in the argument to th eupdate method of the dataadapter. example

ad1.update(dataset1,"<tablename>")

also mention the tablename and ur code will work fine.

 

[fscsantos]

nakracreative,
thanks for the suggestion but the error give me at line:
" ad1.UpdateCommand.Transaction = myTrans "