Error in OleDbTransaction

Question

I'm doing a transaction with OleDb. I don't show how did I calculate the variables like finalQuantity and finalMoneyBuyer because it's not important. My code is this:

    using(OleDbConnection con = DAL.GetConnection())
    {
        OleDbTransaction transaction = null;
        try
        {
            con.Open();
            transaction = con.BeginTransaction();

            if (realQuantity == quantity)
            {
                sql = "DELETE FROM item WHERE (id =" + id + ")";
            }
            else if (realQuantity > quantity)
            {
                sql = "UPDATE item SET quantity = " + finalQuantity + " WHERE (id = "+id+")";
            }
            OleDbCommand cmd = DAL.GetCommand(con, sql);
            cmd.Transaction = transaction;

            string sql2 = "UPDATE lol SET money = " + finalMoneyBuyer + " WHERE (UserName = '" + Session["username"] + "')";
            OleDbCommand cmd2 = DAL.GetCommand(con, sql2);
            cmd2.Transaction = transaction;

            string sql3 = "UPDATE lol SET money = " + finalMoneySeller + " WHERE (UserName = '" + seller + "')";
            OleDbCommand cmd3 = DAL.GetCommand(con, sql3);
            cmd3.Transaction = transaction;

            int num1 = cmd.ExecuteNonQuery();
            int num2 = cmd2.ExecuteNonQuery();
            int num3 = cmd3.ExecuteNonQuery();

            if(num1 == 0 || num2 == 0 || num3 == 0)
            {
                //No esperamos a que sea 0, asi que vamos a echar para atras todo lo que hicimos
                transaction.Rollback();
                //mandar error
                Response.Redirect("home.aspx?err=Error1");
            }
            else
            {
                transaction.Commit();
                Response.Redirect("home.aspx?err=Purchase was successful!");
            }
        }
        catch(OleDbException ex)
        {
            try
            {
                //algo malo paso, vamos a echar para atras todo lo que hicimos.
                transaction.Rollback();
                Response.Redirect("home.aspx?err=Error2");
            }
            catch{}
        }
    }

After running the code... I get this error: error

I hope you can help, thanks.

I'm new in transactions and I took the information from this answer: [How do I do many SQL queries as transactions?](http://stackoverflow.com/questions/30686261/how-do-i-do-many-sql-queries-as-transactions?noredirect=1#comment49442935_30686261) — Pichi Wuana, Jun 12 '15 at 13:47

score 1 · Answer 1 · answered Jun 12 '15 at 13:48

1

You have to set the transaction for the command objects:

cmd.Transaction = transaction;

etc.

answered Jun 12 '15 at 13:48

LInsoDeTeh

1,028
5
11

Wait... Where exactly? Sorry, I'm a bit new to this. – Pichi Wuana Jun 12 '15 at 13:52
after OleDbCommand cmd = DAL.GetCommand(con, sql) and before ExecuteNonQuery() – LInsoDeTeh Jun 12 '15 at 13:53
Thanks. Now it went through. But, what are the reasons that the Transaction didn't succeed? It went to this line `Response.Redirect("home.aspx?err=Error2");` – Pichi Wuana Jun 12 '15 at 13:58
It's like the exception in your screenshot sais: When you have transactions open on your connection, you have to tell each command, which runs on this connection, which transaction it belongs to. – LInsoDeTeh Jun 12 '15 at 13:59
Yes, I did that now. I changed my code to tell `cmd.Transaction = transaction` . The problem is that now it goes to the catch, and don't do the Transaction. I would want to correct that. – Pichi Wuana Jun 12 '15 at 14:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/80402/discussion-between-pichi-wuana-and-linsodeteh). – Pichi Wuana Jun 12 '15 at 14:05

Error in OleDbTransaction

1 Answers1