Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
give me this type error again and again:
Additional information: The connection was not closed. The connection's current state is open.
in the following code.
 
public void store_detailinsertion()
        {
 
            try
            {
                ConnectionString.cnn.Open();
                cmd.Parameters.Clear();
                cmd = new SqlCommand("Insert into store_detail (prdid,quantity,itemtotal) Values (@prdid,@quantity,@itemtotal)", ConnectionString.cnn);
                cmd.Parameters.Add("@prdid", SqlDbType.VarChar).Value = mtbprdid.Text.ToString();
                cmd.Parameters.Add("@quantity", SqlDbType.VarChar).Value = txtqty.Text.ToString();
                cmd.Parameters.Add("@itemtotal", SqlDbType.VarChar).Value = txttotalstkqty.Text.ToString();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
 
                ConnectionString.cnn.Close();
                ConnectionString.cnn.Dispose();
            }
 

        }
 
public void store_detailUpdation()
      {
 
          try
          {
              ConnectionString.cnn.Open();
 
              cmd.Connection = ConnectionString.cnn;
              ConnectionString.cnn.Open();
              cmd.CommandText = "Update store_detail set itemtotal='" + txttotalstkqty.Text + "',quantity='" + txtqty.Text + "' Where prdid='" + mtbprdid.Text + "'";
 
              cmd.ExecuteNonQuery();
              cmd.Connection.Close();
          }
          catch (Exception)
          {
              throw;
          }
          finally
          {
              ConnectionString.cnn.Close();
              ConnectionString.cnn.Dispose();
          }
 
      }
 
 private void store_detailOperation()
        {
            try
            {
                ConnectionString.cnn.Open();
                DataSet ds = new DataSet();
               
 
                SqlDataAdapter da = new SqlDataAdapter("select * from store_detail where prdid='" + mtbprdid.Text + "'", ConnectionString.cnn);
                int s = (da.Fill(ds));
                if (s >= 1)
                {
                    store_detailUpdation();
                    //DisableTextBoxs();
                    //EnableButtons();
                    //btnsave.Enabled = false;
                    //ConnectionString.cnn.Close();
                }
                else
                {
 
                    store_detailinsertion();
                    //DisbleTextBoxs();
                    //EnableButtons();
                    //btnsave.Enabled = false;
                }
 
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionString.cnn.Close();
                ConnectionString.cnn.Dispose();
            }
        }
 
Plzzz, Anyone Can Help Me
Posted 25-Jan-13 7:33am
Edited 25-Jan-13 7:47am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

With this code in store_detailUpdation, you set a Connection object to an already open Connection object. This is probably the source of the error message.
cmd.Connection = ConnectionString.cnn;
ConnectionString.cnn.Open();
 

In store_detailUpdation, you are closing the connection twice. Once in the Try block and once in the Finally block.
 
These both close the database connection:
cmd.Connection.Close();
ConnectionString.cnn.Close();
See the documentation for try-finally (C# Reference)[^]
  Permalink  
v4
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If at all possible its best to use a using block with your command and connection objects.
 
public void store_detailinsertion(string connectionString, string sqlText)
{ 
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using(SqlCommand cmd = new SqlCommand(sqlText,conn))
        {
             cmd.Parameters.Add("@prdid", SqlDbType.VarChar).Value = mtbprdid.Text.ToString();
             cmd.Parameters.Add("@quantity", SqlDbType.VarChar).Value = txtqty.Text.ToString();
             cmd.Parameters.Add("@itemtotal", SqlDbType.VarChar).Value = txttotalstkqty.Text.ToString();
             cmd.ExecuteNonQuery();
        }
     }
}
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

The problem starts from this method
private void store_detailOperation()
You opened a connection here and performed a database operation and leaving the connection open in here
ConnectionString.cnn.Open();
DataSet ds = new DataSet();
               
SqlDataAdapter da = new SqlDataAdapter("select * from store_detail where prdid='" + mtbprdid.Text + "'", ConnectionString.cnn);
int s = (da.Fill(ds));
so after this invoking 'store_detailUpdation()' or store_detailinsertion() inside these method you are trying to open the connection again which is already opened for above select statement. and It's very obvious from the error message.
 
it's a common and best practice to add a Connection state checking before you try to open database connection again.
 
i.e add the conneciton state checking inside the 'store_detailUpdation,store_detailinsertion methods as shown below
public void store_detailUpdation()
{
   try
   {
        if(ConnectionString.cnn.State == ConnectionState.Open)
        {
          ConnectionString.cnn.Close();
        }
        ConnectionString.cnn.Open();
        ....
        ....
        ....
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.150327.1 | Last Updated 25 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100