The Connection Was Not Closed the Connection's Current State Is Open

The connection was not closed the connection's current state is open

Better you write finally block and within it con.close() every where where you used try catch blocks.
Eg.

public void run_runcommand(string query)   
{   
    try   
    {   
        con.Open();   
        SqlCommand cmd1 = new SqlCommand(query, con);   

        cmd1.ExecuteNonQuery();    
        con.Close();    
    }    
    catch (Exception ex)
    {
       throw ex; //TODO: Please log it or remove the catch
    }
    finally
    {
       con.close();
    }

}

try       
{           
    string query="my query";           
    db.run_runcommand(query);          
}         
catch(Exception ex)            
{         
    MessageBox.Show(ex.Message);              
}   
finally
{
   con.close();
}

The connection was not closed, The connection's current state is open

It's likely that an exception is being thrown in the try block that you aren't handling. See this note in MSDN for try-finally:

Within a handled exception, the associated finally block is guaranteed to be run. However, if the exception is unhandled, execution of the finally block is dependent on how the exception unwind operation is triggered.

I would recommend wrapping the connection in a using block anyway:

using (SqlConnection connection = new SqlConnection(connectionString))
{
     //etc...
}

Alternatively, add a catch block to the try-finally:

    conn.Open();

    try
    {

    }
    catch
    {

    }
    finally
    {
        conn.Close();
    }

Connection was not closed, Connection's current state is open error in foreach loop

Move your conn.Open(); call before your foreach loop.

SqlConnection conn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

conn.Open();

foreach (ListItem item in CheckBoxList1.Items)
{
    if(item.Selected)
    {

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "[dbo].[AccountCode_Update]";
        cmd.Parameters.AddWithValue("@Batch_Num", SqlDbType.Int).Value = i;
        cmd.Parameters.AddWithValue("@Batch_Date", SqlDbType.DateTime).Value = dt;
        cmd.Parameters.AddWithValue("@Account_Code", SqlDbType.VarChar).Value = BatchCodeList.SelectedValue;

        cmd.ExecuteNonQuery();
    }
}

conn.Close();

What's happening is that you are calling conn.Open() of a connection that is already open and it throws an error. This is why the first call works and the next ones fail.

Take a look at the MSDN documentation for the Open() method. It has some examples of what will cause exceptions.

In this case

InvalidOperationException

Cannot open a connection without specifying a data source or server.
or
The connection is already open.

The connection was not closed. The connection's current state is Open

You are trying to open the connection twice, you have conn.Open() 2 times in your method, but you are newer closing the connection before trying to "re-open" it.

Are you sure you want to open the connection twice? Try removing the second conn.Open(), that should actually work.

---

Related Topics