[RESOLVED] Using Commit / Rollback when using dataset and oledbcommandbuilder

[Jigabyte]

Hi,

I understand how begintrans / commit and rollback work from VB6 but is it possible to use it if I am using the oledbcommandbuilder to populate my dataset and when I do updates. I am writing a routine which updates several tables and I only want this to be completed if all updates were successful. Here is how I populate my dataset:-

        sSQL = "SELECT * FROM TO_TRAINING_AND_OPERATING_ITEMS WHERE TO_REFNO = " & Me.tdgMain.Columns("TO_REFNO").Text
        oleDACurrentTOOP = New OleDbDataAdapter
        oleDACurrentTOOP.SelectCommand = New OleDbCommand(sSQL, oleDCData)
        oleDSCurrentTOOP = New DataSet
        oleDACurrentTOOP.Fill(oleDSCurrentTOOP, "TO_TRAINING_AND_OPERATING_ITEMS")

Many Thanks,

Jiggy!

[wakawaka]

It is possible, because you are setting the select command object. With the OleDbConnection, you can use the BeginTransaction method and pass that to the commands Transaction property then give the command to the builder.

Wrap the execution of the command in a try block. If it passed, call Commit on the transaction, otherwise call Rollback.

EDIT: Link to documentation for the BeginTransaction method and how to use it.

[Jigabyte]

Thanks for the reply, the problem I am facing is I have three tables I need to alter. If any one of the tables fail in update I want to roll back all three. So table 1 was fine, table 2 was fine but table 3 failed so I want to rollback all tables. I almost want to do the begin trans on the connection. I think I need to add all three tables to one dataset and do the begin trans on that dataset then I would only have to do one commit or rollback and if an error occured it would correct all three table. Can anyone give an example on how to do this barring in mind I am using dataadapter and commandbuilders?

Many Thanks,

Jiggy!

[techgnome]

The select is irrelevant... it's the update that's important... you open a connection, begin your transaction, update the first table, update the second table, update the third table, if there are no errors, you commit it... if there are errors you roll it back.

All of your datatables should be in the same dataset to begin with... you shouldn't have multiple datasets... just one... if even that... you can also get away with multiple datatables, no dataset... but often it's easier to have them all in a dataset if you have more than just a few.

-tg

[wakawaka]

Yes, the select doesn't mean anything, I just associated with that because it was what I saw first in the code, my bad.

[techgnome]

waka - no worries, my comment was aimed a jiga in the first place....

going back for a sec...

I almost want to do the begin trans on the connection.

Yes... that's what you want... ONE connection, issue a transaction on it, then do your updates...

I think I need to add all three tables to one dataset and do the begin trans on that dataset then I would only have to do one commit or rollback and if an error occured it would correct all three table.

mmmm.... no and yes... this is why I cautioned about having multiple datasets in the first place in my original reply... I just wanted to expand on it a little... the transaction would be on the CONNECTION... not the dataset... the connection... so the transaction will affect anything that goes through that connection... you can use the datatables, adaptors and use the Update command... or you can loop through the data and call sprocs... what ever... as long as it goes through that conneciton, it will be part of the transaction.

-tg

[Jigabyte]

Thanks very much to you both. Makes sense now