none
Cannot create another SQLConnection instance after Transaction Begin RRS feed

  • Question

  • I have got a strange problem, let have a look on the snippet.

    Very straight forward ,

    1.Create a new sql connection
    2.begin transaction
    3.insert 2 rows,the values of [Message] is return by GetErrMsg function.
    4.finally, commit.

            private void Form1_Load(object sender, EventArgs e)
            {
                SqlConnection MyConnection = new SqlConnection("xxxx");
                MyConnection.Open();
    
                SqlCommand MyCommand ;
                MyCommand = new SqlCommand("", MyConnection);
    
                MyCommand.CommandText = "BEGIN TRANSACTION";
                MyCommand.ExecuteNonQuery();
    
                MyCommand.CommandText = "INSERT INTO SystemLog ([Source],[Message]) VALUES('abc.aspx', '" + GetErrMsg(1) + "')";
                MyCommand.ExecuteNonQuery();
                MyCommand.CommandText = "INSERT INTO SystemLog ([Source],[Message]) VALUES('abc.aspx', '" + GetErrMsg(2) + "')";
                MyCommand.ExecuteNonQuery(); 
    
                MyCommand.CommandText = "COMMIT";
                MyCommand.ExecuteNonQuery();
    
                MyConnection.Close();
            }
    
            private string GetErrMsg(int ErrorCode)
            {
                string s= "";
                SqlConnection MyConnection = new SqlConnection("xxxx");
                MyConnection.Open();
                SqlCommand MyCommand = new SqlCommand("SELECT ErrText FROM SystemMessage WHERE Code='"+ ErrorCode +"'");
                s = MyCommand.ExecuteScalar().ToString();
                MyConnection.Close();
                return s;
            }
    
    But the code abovem it error on GetErrMsg(),
    Line : s = MyCommand.ExecuteScalar().ToString();
    Error Message : Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    I have remove the Line of "BEGIN TRANSACTION" and "COMMIT", it work fine.
    It seem the GetErrMsg() function cannot create another SQLConnection instance after Begin a Transaction,

    So i fix it to pass the exist connection as parameter to GetErrMsg() function by following code,
    But i still think it not reasonable , each code block's scope is private,

    It should can create more and more SQLConnection or SQLCommand instance simultaneously.
    Do any people know why?

            private void Form1_Load(object sender, EventArgs e)
            {
                SqlConnection MyConnection = new SqlConnection("xxxx");
                MyConnection.Open();
    
                SqlCommand MyCommand ;
                MyCommand = new SqlCommand("", MyConnection);
    
                MyCommand.CommandText = "BEGIN TRANSACTION";
                MyCommand.ExecuteNonQuery();
    
                MyCommand.CommandText = "INSERT INTO SystemLog ([Source],[Message]) VALUES('abc.aspx', '" + GetErrMsg(1, MyConnection) + "')";
                MyCommand.ExecuteNonQuery();
                MyCommand.CommandText = "INSERT INTO SystemLog ([Source],[Message]) VALUES('abc.aspx', '" + GetErrMsg(2, MyConnection) + "')";
                MyCommand.ExecuteNonQuery(); 
    
                MyCommand.CommandText = "COMMIT";
                MyCommand.ExecuteNonQuery();
    
                MyConnection.Close();
            }
    
            private string GetErrMsg(int ErrorCode, SqlConnection conn)
            {
                string s= "";
                SqlCommand MyCommand = new SqlCommand("SELECT ErrText FROM SystemMessage WHERE Code='"+ ErrorCode +"'", conn);
                s = MyCommand.ExecuteScalar().ToString();
                return s;
            }

    My Blog : http://blog.miawork.com
    Tuesday, August 11, 2009 4:55 AM

Answers

  • In the first code you are not setting a connection on the command, so it will never return anything.

            private string GetErrMsg(int ErrorCode)
            {
                string s = "";
                try
                {
                    SqlConnection MyConnection = new SqlConnection(cs);
                    MyConnection.Open();
                    // This is missing a connection, catch it.
                    SqlCommand MyCommand = new SqlCommand("SELECT ErrText FROM SystemMessage WHERE Code='" + ErrorCode + "'"); 
                    s = MyCommand.ExecuteScalar().ToString();
                    MyConnection.Close();
                }
                catch (Exception se)
                {
                    MessageBox.Show(se.ToString(), "Exception");
                }
                return s;
            }

    Secondly, try using the SqlTransaction class, something like this:

               using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand MyCommand = con.CreateCommand();
                    SqlTransaction tran = con.BeginTransaction();
                    MyCommand.Transaction = tran;
    
                    MyCommand.CommandText = "INSERT INTO SystemLog ([Source],[Message]) VALUES('abc.aspx', '" + GetErrMsg(1) + "')";
                    MyCommand.ExecuteNonQuery();
    
                    MyCommand.CommandText = "INSERT INTO SystemLog ([Source],[Message]) VALUES('abc.aspx', '" + GetErrMsg(2) + "')";
                    MyCommand.ExecuteNonQuery();
    
                    tran.Commit();
                    con.Close();
                }

    HTH
    //Michael


    This posting is provided "AS IS" with no warranties.
    Tuesday, August 11, 2009 8:05 AM

All replies

  • In the first code you are not setting a connection on the command, so it will never return anything.

            private string GetErrMsg(int ErrorCode)
            {
                string s = "";
                try
                {
                    SqlConnection MyConnection = new SqlConnection(cs);
                    MyConnection.Open();
                    // This is missing a connection, catch it.
                    SqlCommand MyCommand = new SqlCommand("SELECT ErrText FROM SystemMessage WHERE Code='" + ErrorCode + "'"); 
                    s = MyCommand.ExecuteScalar().ToString();
                    MyConnection.Close();
                }
                catch (Exception se)
                {
                    MessageBox.Show(se.ToString(), "Exception");
                }
                return s;
            }

    Secondly, try using the SqlTransaction class, something like this:

               using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand MyCommand = con.CreateCommand();
                    SqlTransaction tran = con.BeginTransaction();
                    MyCommand.Transaction = tran;
    
                    MyCommand.CommandText = "INSERT INTO SystemLog ([Source],[Message]) VALUES('abc.aspx', '" + GetErrMsg(1) + "')";
                    MyCommand.ExecuteNonQuery();
    
                    MyCommand.CommandText = "INSERT INTO SystemLog ([Source],[Message]) VALUES('abc.aspx', '" + GetErrMsg(2) + "')";
                    MyCommand.ExecuteNonQuery();
    
                    tran.Commit();
                    con.Close();
                }

    HTH
    //Michael


    This posting is provided "AS IS" with no warranties.
    Tuesday, August 11, 2009 8:05 AM
  • Oh my god~~I made a impossible mistake@@
    thank you!

    My Blog : http://blog.miawork.com
    Tuesday, August 11, 2009 9:14 AM