Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: connectionstring
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
 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.
 

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 21:49pm
Comments
Abhishek Pant at 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 at 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");" . I found that if want to solve this error have to do in different connection. So how can i solve this problem?
Rate this: bad
good
Please Sign up or sign in to vote.

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!
  Permalink  
v3
Comments
singping at 18-Dec-12 3:26am
   
Thanks to your opinion but there is error for your link, can't display out the page.
Zoltán Zörgő at 18-Dec-12 3:41am
   
Updated.
Rate this: bad
good
Please Sign up or sign in to vote.

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:
        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
  Permalink  
Comments
singping at 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");" . I found that if want to solve this error have to do in different connection. So how can i solve this problem?

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

  Print Answers RSS
0 OriginalGriff 273
1 Maciej Los 255
2 Aajmot Sk 234
3 Richard MacCutchan 200
4 Marcin Kozub 195
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 18 Dec 2012
Copyright © CodeProject, 1999-2014
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