Using transaction for update and insert

[e106199]

Hi,
i will be doing an update then an insert so i want to use the transaction to make sure if one fails the other wont do what its supposed to do. But there is a little problem, in my insert function i call the update function first. How can i run the same transaction while calling a function? If i dump all update function code in insert function section it gets ugly so i want to keep it seperate but also be able to use the same transaction for both.
How can i do this?
thanks in advance
-shane

it will be something like:
insert function start
declare transaction
update function starts with transaction
update function runs but transaction.commit isnt called yet
insert function runs with transaction
if everything is ok transaction.commit 'update and insert is now complete
something is wrong transaction.rollback 'update and insert both fail
end insert function

 

[chiph]

I'm not understanding what behavior you want. It sounds like you want them both to succeed, or both to fail. Correct?

If so, that's what a transaction does best. You may just be having some implementation problems. Declare & start the transaction before calling the update & insert methods. Pass the transaction object to them, and inside each check to see if the transaction object is not null/nothing. If it isn't null, you can assign it to the transaction property of your command object (meaning: this update/insert is taking place inside a transaction). If it is null, then don't set it (meaning: this update/insert is not taking place inside a transaction, which may be OK for your app, but isn't generally a good idea).

If an SQLException or System.Exception was thrown somewhere in your update/insert code, you'd roll back the transaction. Otherwise all is well and you can commit.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first