Error "The connection was not closed. The connection's current state is open."

Question

0.00/5 (No votes)

See more:

I have a part of code as following and it keep showing an error "The connection was not closed. The connection's current state is open." at the

C#

cmd2.Connection.Open();

after debugging. so I'm wondering how can i solve this problem if i using multiple connection?

Please someone assist me for this question since I had used few days to find the solution for it. Thanks.

C#

private void loadgrid()
    {
        string sql;
        string sql2;
        string a;
        string b;
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet ds = new DataSet();
        String connectionString = ConfigurationManager.ConnectionStrings["VSGIHURConnectionString"].ConnectionString;
        SqlConnection connection = new SqlConnection(connectionString);

        if (ddl_groupid.Text == "")
        {
            a = "";
            gv.Visible = false;


        }
        else
        {
            a = " AND UniformGroupid = '" + ddl_groupid.Text.ToString() + "' ";
            gv.Visible = true;

        }

        if (ddl_uniformid.Text == "")
        {
            b = "";

        }
        else
        {
            b = " AND uniformtran.UniformID = '" + ddl_uniformid.Text + "'";

        }

       sql = "select distinct UniformInfo.UniformGroupID, UniformTran.Uniformid from UniformTran " +
              " inner join UniformInfo on UniformTran.Uniformid = UniformInfo.UniformID " +
              " where UniformGroupid <> '' " + a + b + " and datename(MONTH, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlMonth.Text + "' " +
              " and datename(YEAR, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlYr.Text + "' " +
              " order by UniformTran.Uniformid ";
       
        SqlCommand cmd = new SqlCommand(sql, connection);
        cmd.Connection.Open();
   
        SqlDataReader ddlValues;
        ddlValues = cmd.ExecuteReader();

       int count0 = 10000;

       for (int i = 0; i < count0; i++)
       {
           if (connection.State == ConnectionState.Open)
           {
               if (ddlValues.HasRows)
               {
                   ddlValues.Read();
                   {

                       sql2=("Select UniformGroupID, Uniformid, 'STOCK' as transctype,  convert(varchar(25),dateadd(dd,-(day(convert(datetime,transcdate,103))-1),convert(datetime,transcdate,103)),103) as transcdate, " +
                               " '00:00:00' as transctime, '-' as actualqty ,'B/F' as method " +
                               " ,currentonhand as onhand,Batnbr from " +
                               " (select  top 1 UniformGroupID, UniformTran.UniformID, 'STOCK' as transctype,  transcdate, " +
                               " '00:00:00' as transctime, '-' as actualqty ,'B/F' as method " +
                               " ,onhand  as currentonhand,Batnbr  from uniformtran " +
                               " inner join UniformInfo on UniformTran.Uniformid = UniformInfo.UniformID " +
                               " where UniformGroupid <> '' " + a + b + " AND datename(MONTH, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlMonth.Text + "' " +
                               " and datename(YEAR, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlYr.Text + "' order by Batnbr)z " +
                               " union" +
                               " select UniformGroupID, UniformID, transctype,transcdate ,transctime, actualqty = case when CAST (actualqty as int) < 0 then CAST (actualqty as int) * -1 " +
                               " else actualqty end,method =  case " +
                               " when (CAST(actualqty as int) * cast (method as int )) > 0 then 'IN'" +
                               " when (CAST(actualqty as int) * cast (method as int )) < 0 then 'OUT'" +
                               " when (CAST(actualqty as int) * cast (method as int )) = 0 then '-'" +
                               " end" +
                               " ,currentonhand,Batnbr from" +
                               " (select UniformGroupID, Batnbr, UniformTran.UniformID, transctype ,qtyvariance,((CAST(actualqty as int ) - cast (qtyvariance  as int) ) * CAST (method as int)) as actualqty,method,onhand,currentonhand,transcdate,transctime from uniformtran" +
                               " inner join UniformInfo on UniformTran.Uniformid = UniformInfo.UniformID " +
                               " where UniformGroupid <> '' " + a + b + " AND datename(MONTH, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlMonth.Text + "' and datename(YEAR, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlYr.Text + "' AND transctype like 'Receive%'" +
                               " Union" +
                               " select UniformGroupID, Batnbr,UniformTran.UniformID, transctype ,qtyvariance,(CAST(actualqty as int ) - cast (qtyvariance  as int) )as actualqty,method,onhand,currentonhand,transcdate,transctime from uniformtran" +
                               " inner join UniformInfo on UniformTran.Uniformid = UniformInfo.UniformID " +
                               " where UniformGroupid <> '' " + a + b + " AND datename(MONTH, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlMonth.Text + "' and datename(YEAR, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlYr.Text + "'  AND transctype like 'Issue%' and actualqty <> '0' " +
                               " Union" +
                               " select UniformGroupID, Batnbr, UniformTran.UniformID, transctype ,qtyvariance,(CAST(actualqty as int ) - cast (qtyvariance  as int) )as actualqty,method,onhand,currentonhand,transcdate,transctime from uniformtran" +
                               " inner join UniformInfo on UniformTran.Uniformid = UniformInfo.UniformID " +
                               " where UniformGroupid <> '' " + a + b + " AND datename(MONTH, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlMonth.Text + "' and datename(YEAR, CONVERT (datetime,uniformtran.transcdate, 103)) = '" + ddlYr.Text + "'  AND transctype like 'Stock Adjustment%'" +
                               " ) as a" +
                               " order by uniformid, transcdate,transctime,Batnbr");

                      SqlCommand cmd2 = new SqlCommand(sql2, connection);
                       cmd2.Connection.Open();
                       adapter.SelectCommand = cmd2;
                       adapter.Fill(ds, "TempMovementRecord");

                       SqlDataReader ddlValues2;
                       ddlValues2 = cmd.ExecuteReader();
                       if (ddlValues2.HasRows)
                       {
                           ddlValues2.Read();
                           {

                               ddl_uniformid.Text = ddlValues2["UniformID"].ToString();
                           }

                       }

                       gv.DataSource = ddlValues2;
                       gv.DataBind();

                       cmd2.Connection.Close();
                       cmd2.Connection.Dispose();
                   }




                   int count2 = ds.Tables[0].Rows.Count;

                   DataTable dt = new DataTable();
                   DataRow dr;

                   dt.Columns.Add(new DataColumn("UniformGroupID",typeof(string)));
                   dt.Columns.Add(new DataColumn("UniformID", typeof(string)));
                   dt.Columns.Add(new DataColumn("transctype", typeof(string)));
                   dt.Columns.Add(new DataColumn("transcdate", typeof(string)));
                   dt.Columns.Add(new DataColumn("transctime", typeof(string)));
                   dt.Columns.Add(new DataColumn("actualqty", typeof(int)));
                   dt.Columns.Add(new DataColumn("method", typeof(string)));
                   dt.Columns.Add(new DataColumn("onhand", typeof(string)));
                   dt.Columns.Add(new DataColumn("batnbr", typeof(string)));


                   for (int j = 0; j < count2; j++)
                   {
                       dr = dt.NewRow();

                       dr[0] = ds.Tables[0].Rows[j]["UniformGroupID"];
                       dr[1] = ds.Tables[0].Rows[j]["UniformID"];
                       dr[2] = ds.Tables[0].Rows[j]["transctype"];
                       dr[3] = ds.Tables[0].Rows[j]["transcdate"];
                       dr[4] = ds.Tables[0].Rows[j]["actualqty"];
                       string m = ds.Tables[0].Rows[j]["method"].ToString().Trim();
                       dr[5] = ds.Tables[0].Rows[j]["onhand"];
                       dr[6] = ds.Tables[0].Rows[j]["batnbr"];

                       dt.Rows.Add(dr);
                   }

                   DataView dv = new DataView(dt);


                   gv.DataSource = dv;
                   gv.DataBind();

                   adapter.Dispose();
               }
           }
       }

       int count = gv.Rows.Count;
       if (count > 0)
       {
           Label lblqty = (Label)gv.Rows[0].FindControl("lblqty");
           lblqty.Text = "-";

           btnPrint.Visible = true;
           ibExcel.Visible = true;

       }
       else
       {
           btnPrint.Visible = false;
           ibExcel.Visible = false;
       }

        cmd.Connection.Close();
        cmd.Connection.Dispose();

    }

Posted 17-Dec-12 20:49pm

singping

Add a Solution

Comments

Abhishek Pant 18-Dec-12 3:00am

you havn't closed that cmd.Connection and now you are operating a another operation with opening and this connection is already opened as cmd.Connection.Open(); it I think you do not need to open another cmd2.Connection.Open();

singping 18-Dec-12 3:14am

if i remove the other open connection, then another error will occur as "There is already an open DataReader associated with this Command which must be closed first." at the "adapter.Fill(ds, "TempMovementRecord");" . Ｉ　ｆｏｕｎｄ　ｔｈａｔ　ｉｆ　ｗａｎｔ　ｔｏ　ｓｏｌｖｅ　ｔｈｉｓ　ｅｒｒｏｒ　ｈａｖｅ　ｔｏ　ｄｏ　ｉｎ　ｄｉｆｆｅｒｅｎｔ　ｃｏｎｎｅｃｔｉｏｎ．　Ｓｏ　ｈｏｗ　ｃａｎ　ｉ　ｓｏｌｖｅ　ｔｈｉｓ　ｐｒｏｂｌｅｍ？

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Zoltán Zörgő** · Answer 1 · 2012-12-17T21:08:00

Solution 2

As I see, you came from a scripting world (php?), since you do database stuff like there.
First of all, it depends on the driver and the connection string you use, how the exact connection object is handled. As there is an underlying layer (mostly unmanaged code), and connection creation is resource expensive in general - it is not obvious, when the connection object beneath is really disposed. It is most likely reused, and disposed when the driver or the GC thinks to be necessary.

Since the execution looks not too long,
cmd.Connection.Open();
and
cmd2.Connection.Open();
will be trying to reopen the same connection.
Don't do that. Open your connection at the beginning of your database interactions, and close at the very end of it. Believe me, the framework will do the work. The sample on the link below is a good one for how to use using statement in conjunction with a connection.

http://www.dotnetperls.com/sqlparameter[^]

BTW:
- don't build query string like this, use parameters (like in the sample), you made security holes.
- such complex queries are more easy to handle with database objects like views and stored procedures. Easier to maintain and to debug!

Posted 17-Dec-12 21:08pm

Zoltán Zörgő

Updated 17-Dec-12 21:40pm

v3

Comments

singping 18-Dec-12 3:26am

Ｔｈａｎｋｓ　ｔｏ　ｙｏｕｒ　ｏｐｉｎｉｏｎ　ｂｕｔ　there is error for your link, can't display out the page.

Zoltán Zörgő 18-Dec-12 3:41am

Updated.

SuperJWP 30-Nov-18 2:25am

I am having issues with my code , when i submit the data

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Linq;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Configuration;
using System.Dynamic;
using System.Data.SqlClient;
using System.Data;

public partial class NewEmployeeRequestForm : System.Web.UI.Page
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection();

protected void Page_Load(object sender, EventArgs e)

{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=SH-JASONK\DEV;Initial Catalog=EmployeeRegistration;Integrated Security=True");
{

con.Open();
SqlCommand cmd = new SqlCommand("insert into Employees values(@FirstName, @LastName, @Email, @Gender, @Phone, @StartDate, @Company, @Department, @Applications, @Requestedby)", con);

cmd.Parameters.AddWithValue("@FirstName", FirstName.Text);
cmd.Parameters.AddWithValue("@LastName", LastName.Text);
cmd.Parameters.AddWithValue("@Email", Email.Text);
cmd.Parameters.AddWithValue("@Gender", RadioButtonList1.SelectedValue);
cmd.Parameters.AddWithValue("@Phone", Phone.Text);
cmd.Parameters.AddWithValue("@StartDate", StartDate.Text);
cmd.Parameters.AddWithValue("@Company", Company.SelectedValue);
cmd.Parameters.AddWithValue("@Department", Departments.SelectedValue);
cmd.Parameters.AddWithValue("@Applicatons", CheckBoxList1.SelectedValue);
cmd.Parameters.AddWithValue("@Devices", RadioButtonList.SelectedValue);
cmd.ExecuteNonQuery();
con.Close();

}

}

}

The Error i receive is :

Must declare the scalar variable "@Applications".
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.Data.SqlClient.SqlException: Must declare the scalar variable "@Applications".

Source Error:

Line 42: cmd.Parameters.AddWithValue("@Applicatons", CheckBoxList1.SelectedValue);
Line 43: cmd.Parameters.AddWithValue("@Devices", RadioButtonList.SelectedValue);
Line 44: cmd.ExecuteNonQuery();
Line 45: con.Close();
Line 46:

ZurdoDev 10-Jun-19 7:53am

Why did you post this here?

OriginalGriff · Answer 2 · 2012-12-17T21:05:00

Solution 1

Because you are using the same connection for cmd and cmd2, you do not need to open it again when you set up the sql2 command:

C#

SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Connection.Open();

SqlDataReader ddlValues;
...
              SqlCommand cmd2 = new SqlCommand(sql2, connection);
               cmd2.Connection.Open();

Just remove the Open call from cmd2 and it should be fine

Posted 17-Dec-12 21:05pm

OriginalGriff

Comments

singping 18-Dec-12 3:16am

if i remove the other open connection, then another error will occur as "There is already an open DataReader associated with this Command which must be closed first." at the "adapter.Fill(ds, "TempMovementRecord");" . Ｉ　ｆｏｕｎｄ　ｔｈａｔ　ｉｆ　ｗａｎｔ　ｔｏ　ｓｏｌｖｅ　ｔｈｉｓ　ｅｒｒｏｒ　ｈａｖｅ　ｔｏ　ｄｏ　ｉｎ　ｄｉｆｆｅｒｅｎｔ　ｃｏｎｎｅｃｔｉｏｎ．　Ｓｏ　ｈｏｗ　ｃａｎ　ｉ　ｓｏｌｖｅ　ｔｈｉｓ　ｐｒｏｂｌｅｍ？