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

[quoted text, click to view]

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
--

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