GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Transaction not committed properly

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Transaction not committed properly

    Dear All,
    I have this application which is using Innodb engine and .netconnector 5.0.8.1.So I am using C# for my coding. So I am not sure either is the engine or the connector problem? So I want to run transaction base appplication where either all my sql statement go through or all fail. So the funny part I notice it works well for most of my cases but for certain cases the second sql statement go through. I really cant figure out why is this happening. I have attached the code below. So the problem there is that my myInsertQuery1 goes through but not my myUpdateQuery1 ? So whyis this happening because both are under the same transactions right. I have separately attached below my function for thetransactionConnectionLocal1 where I keep the connection details.

    int rollbackBoolean = 0;
    MySqlTransaction transactionLocal1 = null;
    MySqlConnection connectionLocal1 = null;
    transactionConnectionLocal1 callTransactionConnectionLocal1= null;
    try
    {
    callTransactionConnectionLocal1 = newtransactionConnectionLocal();
    connectionLocal1=callTransactionConnectionLocal1.l ocalConnec tion1;
    connectionLocal1.Open();
    transactionLocal1 =connectionLocal1.BeginTransaction();
    }
    catch (MySql.Data.MySqlClient.MySqlException ex)
    {
    rollbackBoolean = 1;
    MessageBox.Show("Error From Database Connection " +ex.Message);
    }
    catch (System.Net.Sockets.SocketException ex)
    {
    rollbackBoolean = 1;
    MessageBox.Show("Error Sockets From Database Connection" + ex.Message);
    }


    String myUpdateQuery1 = "Update tblStock " +"Set tblStock.stockStatus = 'b'" +"Where tblStock.stockSerial ='" +serial + "'" ;
    MySqlCommand myCommand1 = newMySqlCommand(myUpdateQuery1);
    try
    {
    myCommand1.Connection = connectionLocal1;
    myCommand1.Transaction = transactionLocal1;myCommand1.ExecuteNonQuery();
    }
    catch (MySql.Data.MySqlClient.MySqlException ex)
    {
    rollbackBoolean = 1;
    MessageBox.Show("Error From myUpdateQuery1 " +ex.Message);
    }
    catch (System.Net.Sockets.SocketException ex)
    {
    rollbackBoolean = 1;
    MessageBox.Show("Error Sockets From myUpdateQuery1 " +ex.Message);
    }
    finally
    {
    myCommand1.Dispose();
    }

    String myInsertQuery1 = "Insert into tblTemp " +"Set stockSerial ='" + serial+ "'," +"tempTimeStamp='" +DateTime.Now.ToString(("yyyy:MM:dd HH:mm:ss")) + "';";MySqlCommand myCommand2 = newMySqlCommand(myInsertQuery1);

    try
    {
    myCommand2.Connection = connectionLocal1;
    myCommand2.Transaction = transactionLocal1;
    myCommand2.ExecuteNonQuery();
    }
    catch (MySql.Data.MySqlClient.MySqlException ex)
    {
    rollbackBoolean = 1;MessageBox.Show("Error From myInsertQuery1 " +ex.Message);
    }
    catch (System.Net.Sockets.SocketException ex)
    {
    rollbackBoolean = 1;
    MessageBox.Show("Error Sockets From myInsertQuery1 " +ex.Message);
    }
    finally{myCommand2.Dispose();
    }

    if (rollbackBoolean == 1)
    {
    transactionLocal1.Rollback();
    }
    else
    {
    try
    {
    transactionLocal1.Commit();
    }
    catch (MySql.Data.MySqlClient.MySqlException ex)
    {
    try
    {
    transactionLocal1.Rollback();
    }
    catch (MySqlException ex1){MessageBox.Show("An exception of type " +ex.GetType() +" was encountered while insertingthe data.");
    if (transactionLocal1.Connection != null)
    {
    MessageBox.Show("An exception of type " +ex1.GetType() +" was encountered whileattempting to rollback the transaction.");
    }
    }
    }
    catch (System.Net.Sockets.SocketException ex){rollbackBoolean = 1;MessageBox.Show("Error Sockets From Commit Process" + ex.Message);
    }
    finally{connectionLocal1.Close();
    }
    }


    // connection details function
    using System;
    using System.Collections.Generic;
    using System.Text;
    using MySql.Data.MySqlClient;
    namespace mSytemNonFranchise
    {
    public class transactionConnectionLocal1
    {
    public MySqlConnection localConnection1;
    public MySqlCommand command;
    public transactionConnectionLocal1()
    {
    this.localConnection1 = newMySqlConnection("Address='localhost';Database=' localDB';UserName='root';Pas sword='local12';Pooling='false'");
    // this.command = this.localConnection1.CreateCommand();
    // this.localConnection1.Open();}
    // destructor - explicitly
    ~transactionConnectionLocal1()
    {//this.command.Dispose();this.localConnection1.Close ();this .localConnection1.Dis pose();
    }
    }
    }
Working...
X