0

I wish to do some sort of delayed rollback (not in batch) in sample c# desktop app 1 button inserts the data, the other rolls it back

I tried the following but it is not working 

error:  The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

.

private void button1_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
        //conn = new SqlConnection(ConnectionString);
        conn.Open();
        try
        {
            //tran = conn.BeginTransaction("Transaction1");
            SqlCommand cmd = new SqlCommand("begin transaction", conn);
            SqlCommand cmd1 = new SqlCommand("insert into employee values ('6','aaaaaa','111')", conn);
            cmd.ExecuteNonQuery();
            cmd1.ExecuteNonQuery();
            conn.Close();

        }
        catch { }
    }
    private void button2_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
        conn.Open();
        SqlCommand cmd2 = new SqlCommand("rollback transaction", conn);
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

Is what i am attempting even possible? or am I just going at it the wrong way?

AngelicCore
  • 1,413
  • 3
  • 22
  • 40

1 Answers1

0

Youch.

TECHNICALLY, everything you have in button2_Click is "new", so there is no "tie back" to the original "begin transaction" statement.

But I really don't think you want to do the manual "begin transaction" and "rollback transaction" statements.

I ~~think~~ you want to try and make this variable:

**tran** = conn.BeginTransaction("Transaction1");

this "tran" variable a member variable whose scope is bigger than one command button. (Lets say you make it a member variable and rename it to _transaction)

and then either call

_transaction.Rollback();

or 

_transaction.Commit()

But wow. That's is flakey. I'm just giving you an idea if you're really bent on doing it.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • :) Actually I've started there..but wanted to do it my way because it seemed more manual I thought it would work because I am sending these statements to the db engine. and in Sql Server these commands don't have to be executed in the same batch – AngelicCore May 01 '13 at 20:52
  • I have no idea if "your way" will actually work. But to even attempt it, you have to allow the scope of (SqlCommand cmd = new SqlCommand("begin transaction", conn);) to be bigger than just the command button. It needs to be a member variable at the least. You're getting the error because the first cmd falls out of scope after the button1_click, so button_2 "rollback tran" has no idea what you're talking about, thus your error. – granadaCoder May 01 '13 at 20:54
  • will try that and get back to you – AngelicCore May 01 '13 at 23:13