The connection was not closed

Question

This is doing my head in. I have tried try and catch with finally and other things but this error keeps coming back. Please some one help. My c# code:

 void show()
    {
       string str = "SELECT PID, Pname, Gender, ContactNum, Category, Department, Ward, AdmitDate, DischargeDate, NumOfDays, CostOfTest, NumOfDocsVisited, DocFee, BedCost, TotalCost, GrandTotal FROM Patient";
        cmd = new SqlCommand(str, con);
            con.Open();
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            GridView1.DataBind();
                con.Close();
    }

error is:

The connection was not closed. The connection's current state is open. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: The connection was not closed. The connection's current state is open.

Source Error:

Line 50:            string str = "SELECT PID, Pname, Gender, ContactNum, Category, Department, Ward, AdmitDate, DischargeDate, NumOfDays, CostOfTest, NumOfDocsVisited, DocFee, BedCost, TotalCost, GrandTotal FROM Patient";
Line 51:             cmd = new SqlCommand(str, con);
Line 52:                 con.Open();
Line 53:                 SqlDataAdapter adp = new SqlDataAdapter(cmd);
Line 54:                 DataTable dt = new DataTable();

Stack Trace:

[InvalidOperationException: The connection was not closed. The connection's current state is open.]
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +14
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +94
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +110
System.Data.SqlClient.SqlConnection.Open() +96
SMC.Billing.show() in e:\VS2013 projects\SMC\SMC\Billing.aspx.cs:52
SMC.Billing.Button3_Click(Object sender, EventArgs e) in e:\VS2013 projects\SMC\SMC\Billing.aspx.cs:97
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9628722
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

Use the `using` clause to insure your connections are disposed of and closed properly should clear this up. — asawyer, May 08 '15 at 18:20
where in your code are you using `con` it's obvious that you are using a global connection and why not just add the following or check for the following State of the `con` just put `con.Close()` before calling the Open method.. but that's a poor way.. I would do a conditional check.. and to really be safe..wrap all of your sql objects around a `using(){}` this will ensure auto disposing of the objects and underlying object etc... — MethodMan, May 08 '15 at 18:28

score 2 · Answer 1 · answered May 08 '15 at 18:25

2

Looks to me like it is trying to tell you that you are trying to open an already open connection.

You might try (on line 52)

if (con.State == ConnectionState.Closed) con.Open;

answered May 08 '15 at 18:25

Kevin

1,462
9
9

i have already tried that – Sana Qureshi May 08 '15 at 18:31

score 1 · Accepted Answer · answered May 08 '15 at 18:23

1

Wrap your connection in a using block. This guarantees it always gets closed.

using (var connection = new SqlConnection(connectionString))
{
  connection.Open();
  //Do stuff with the connection
}

answered May 08 '15 at 18:23

Zer0

7,191
1
20
34

The connection was not closed

2 Answers2