469,578 Members | 1,285 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

Transaction through stored Procedure

i have to update two tables from ASP pages with same data but i want
that both of them should be updated at one time. If either of them is
not updated then my transaction should roll back.I want this thing to
be in a stored procedure. so that i have to write an execute statement
only on the ASP page and pass the parameters.

Looking forward for ur reply
DEEPAK
Jul 20 '05 #1
2 2160
On 7 Oct 2004 04:01:39 -0700, Deepak Mehta wrote:
i have to update two tables from ASP pages with same data but i want
that both of them should be updated at one time. If either of them is
not updated then my transaction should roll back.I want this thing to
be in a stored procedure. so that i have to write an execute statement
only on the ASP page and pass the parameters.

Looking forward for ur reply
DEEPAK


Hi Deepak,

This is the rough outline of the procedure that will either execute all
statement or roll back everything. I'll leave it to you to fill in the
details (and improve the feedback on error).

CREATE PROC .....
AS
BEGIN TRANSACTION
UPDATE ..... -- first update
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN (99)
END
UPDATE ..... -- second update
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN (99)
END
COMMIT TRANSACTION
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
Hi Deepak,

The following structure is used to implement the transaction.

Begin Transaction
Try
{Statements]
Commit Transaction
Catch Exception
Rollback Transaction
End Try

The steps to complete a transaction including update of tables using a
stored procedure are listed below:

a.Open the connection.
b.Create a Transaction Object over the Connection Object by calling
its Begin Transaction method. The Begin Transaction method must be
called before performing any of the database operations that will take
part in the transaction.
c.Set Command Object's transaction property to the transaction object.
This will let ADO.Net to know which actions to do to undo when the
transaction is rolled back.
d.All the statements must be placed in the Try Clause of the exception
handler.
e.Set the Command Object's Command Text property to name of the stored
procedure.
f.Add parameters to the stored procedure. For each parameter, set its
name, type and value and add it to the Command Object's parameter
collection.
g.After all the parameters are set call the ExecuteNonQuery method of
the command object which will execute the stored procedure.
h.Commit the changes to the Tables by using Commit method.

I have explained this in detail in my book. However, thought of
posting it here.
Regards
Bharati
http://www.vkinfotek.com
Open source with a difference.
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Sam | last post: by
11 posts views Thread by harborboy76 | last post: by
15 posts views Thread by Zeng | last post: by
9 posts views Thread by ucasesoftware | last post: by
1 post views Thread by cricketweb | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.