ASP.NET - Roll back insert statment

Hello all
I have some web site which have some page insert in two sql server DB tables , the  first one I have method which insert into some table called users and then I have to add in some other table called Permissions, and If  the user adding failed I want to roll back from both tables , please how can I do that , please if any body can help me please send me or tell me about some URL may help me in doing that
Regards

Use the Transction in your code.

1. Enclose the insert and other operation inside the transaction.
2. If the transaction is succeed then commit the transaction
3. Else rollback the transaction


The below example uses the sql transaction


//SQl transaction
SqlTransaction myTransaction = SqlConnectionObject.BeginTransaction();
try
{
//Perform your insert statmements

SqlCommand cmd=new Command("Select * from mytable");
cmd.connection=myconnection;
cmd.Transaction = myTransaction.Transaction;



// If succeed then commit  
   myTransaction.Commit();
}
catch
{
    // IF error roolback
    myTransaction.Rollback();
    throw;


You need to use transactions for this:
1. Start transaction before Insert statement.
2. After the Schema permissions are successfully inserted then commit the transaction.
3. If the schema permissions are failed then rollback the transaction.

How to use the transactions check this lnk:
http://www.codeproject.com/Articles/10223/Using-Transactions-in-ADO-NET

Hope this helps!

And when you say , page inserts into two tables - I hope that is done under one single action on a single ASP.net page. Inj this case, you need to call a stored procedure which will either commit/rollback the transaction depending on its sucess.

In your stored procedure, put your insert statements within a try-catch. Then place a "Begin Transaction" statement before the Try. In the case of any error, place a "Rollback" in the Catch block. You can use the "@@Trancount" server variable to determine if there were any errors. Check the following sample.

CREATE PROCEDURE StoredProcedureName 
AS
BEGIN
--Begin the transaction before your insert statements
BEGIN TRANSACTION
BEGIN TRY
--Place the insert statements here.
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    -- Some error occured
    ROLLBACK
END CATCH
--All is fine. Commit the transaction
COMMIT
END

Otherwise, you are going to have to do this on your server side code  - .Net. 

Database db = DatabaseFactory.CreateDatabase(); 
using (DbConnection conn = db.CreateConnection())
{ 
   //Open the connection.
   conn.Open(); 
   //Begin a transaction
   Dbtrans trans = conn.Begintrans(); 
   try 
   { 
//db.ExecuteCommand1
//db.ExecuteCommand2
trans.Commit(); 
   } 
   catch 
   { 
      trans.Rollback(); 
   } 
}

Hope the code is explanatory.

Hello,

Make your SP as following way

 

CREATE PROCEDURE InsertSP

 

(

 

@nameFirst varchar(50),

 

@nameLast varchar(50),

 

@email varchar(50),

@PermissionValue varchar(50)

)

AS

 

BEGIN

Begin Transaction InsertUser

INSERT INTO User_Master( [nameFirst], [nameLast], [email]) VALUES ( @nameFirst, @nameLast, @email)

 

DECLARE @intValue as Int = SCOPE_IDENTITY()

 

IF @intValue > 0

BEGIN

    INSERT INTO Permission( @intValue,PermissionValue) VALUES ( UserID, @PermissionValue)

    COMMIT TRANSACTION InsertUser;

 

   

END

ELSE

BEGIN

 

    ROLLBACK TRANSACTION InsertUser;

END

 

END

Hope this helpful!

Thanks

 

 

 

 

 

Slight modification in [)ia6l0 iii code's

Change in Code : Remove COMMIT from the second last line (Mark in Red) and write just before END TRY (Mark in Green)
Reason :  If you would write commit in the last line, COMMIT Statement would always execute. Now suppose, if there is any error it would be handled in CATCH and it would rollback the transaction, but COMMIT would still run and you would get unhandled error like this
Msg 3902, Level 16, State 1, Procedure StoredProcedureName, Line 17
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Check this code for the reference

CREATE PROCEDURE StoredProcedureName
Modified Code