locked
"Timeout expired This may have occurred becuse of max pool size was reached" RRS feed

  • Question

  • User-172769993 posted

    Hello i have this problem occur when i execute the following code

    "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

     protected void gridoutofstock_RowDataBound(object sender, GridViewRowEventArgs e)
    {
    StoreClass s = new StoreClass();
    try
    {
    for (int index = 0; index < this.gridoutofstock.Rows.Count; index++)
    {
    //here each method connect to the database and close it in StoreClass
    string sales= s.countproductinorders(this.gridoutofstock.Rows[index].Cells[0].Text);
    string service=s.countproductinservice(this.gridoutofstock.Rows[index].Cells[0].Text);
    string agreement=s.countproductinagreement(this.gridoutofstock.Rows[index].Cells[0].Text);
    this.gridoutofstock.Rows[index].Cells[3].Text = sales;
    this.gridoutofstock.Rows[index].Cells[4].Text = agreement;
    this.gridoutofstock.Rows[index].Cells[5].Text = service;
    int balance = Convert.ToInt32(this.gridoutofstock.Rows[index].Cells[2].Text) - (Convert.ToInt32(sales) + Convert.ToInt32(service) + Convert.ToInt32(agreement));
    this.gridoutofstock.Rows[index].Cells[7].Text = Convert.ToString(balance);
    }

    }

    catch { };
    }

    and in the grid view i generate the following columns (Pro_ID,Product_Name and PurchaseQty) using below code

     protected void Page_Load(object sender, EventArgs e)
            {
                string strQuery = "Select * from Store";
                SqlCommand cmd = new SqlCommand(strQuery);
                DataTable dt = GetData(cmd);
                gridoutofstock.DataSource = dt;
                gridoutofstock.DataBind();
            }
            private DataTable GetData(SqlCommand cmd)
            {
                gridoutofstock.DataBind();
                DataTable dt = new DataTable();
                String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AhlhaGowConnString"].ConnectionString;
                SqlConnection con = new SqlConnection(strConnString);
                SqlDataAdapter sda = new SqlDataAdapter();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                try
                {
                    con.Open();
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                    sda.Dispose();
                    con.Dispose();
                }
            }

     <asp:GridView ID="gridoutofstock"  AutoGenerateColumns="false" CssClass="table table-bordered text-nowrap" runat="server" OnRowDataBound="gridoutofstock_RowDataBound">
                              <Columns>
                                <asp:BoundField DataField="Pro_ID" HeaderText="Pro_ID" ItemStyle-CssClass="hiddencol" HeaderStyle-CssClass="hiddencol" SortExpression="Pro_ID" />
                                <asp:BoundField DataField="Product_Name" HeaderText="Product Name" SortExpression="Product_Name" />
                                <asp:BoundField DataField="PurchaseQty" HeaderText="Opening Balance" SortExpression="PurchaseQty" />
                                <asp:BoundField  HeaderText="Sales" NullDisplayText="0"/>
                                <asp:BoundField  HeaderText="Agreement" NullDisplayText="0"/>
                                <asp:BoundField  HeaderText="Service" NullDisplayText="0"/>
                                <asp:BoundField DataField="Quantity" HeaderText="Stock" SortExpression="Quantity"  />
                                <asp:BoundField  HeaderText="Balance"/>
                              </Columns>
                              </asp:GridView>

    Monday, August 31, 2015 3:44 PM

Answers

  • User-986267747 posted

    Hi asp.net4

    protected void Page_Load(object sender, EventArgs e) { string strQuery = "Select * from Store"; SqlCommand cmd = new SqlCommand(strQuery); DataTable dt = GetData(cmd); gridoutofstock.DataSource = dt; gridoutofstock.DataBind(); }

    According to your above code, you should close your sqlcommand. You could try with the code below.

    protected void Page_Load(object sender, EventArgs e)
            {
                string strQuery = "Select * from Store";
                SqlCommand cmd = new SqlCommand(strQuery);
                DataTable dt = GetData(cmd);
                cmd.Close();
                cmd.Dispose();
                gridoutofstock.DataSource = dt;
                gridoutofstock.DataBind();
            }
            private DataTable GetData(SqlCommand cmd)
            {
                gridoutofstock.DataBind();
                DataTable dt = new DataTable();
                String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AhlhaGowConnString"].ConnectionString;
                SqlConnection con = new SqlConnection(strConnString);
                SqlDataAdapter sda = new SqlDataAdapter();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                try
                {
                    con.Open();
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {  
                    con.Close();
                    cmd.Close();
                    cmd.Dispose();
    
                    sda.Dispose();
                    con.Dispose();
                }
            }

    Besides, There are a similar problem, you could refer to the following link so that you could get more information.

    http://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server

    I hope it's helpful to you.

    Best Regards

    Klein zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 18, 2015 5:45 AM
  • User753101303 posted

    Hi,

    Dispose the comman as well? Check what StoreClass does. Using an empty catch block is the last thing you want to do (ie if you have an exception in this code, NOBODY knows about it and as best you could painfully guess you have one when the app seems to behave strangely).

    In short the error message happens when connection are not closed properly. So double check you are closing all that needs to be closed and that uses a connection. The using statement might help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 18, 2015 9:41 AM

All replies

  • User753101303 posted

    Hi,

    Unless you have a high traffic site it happens when connections are not properly closed (maybe in StoreClass ?). Basically the max size for the connection pool is reached, there is then a delay in case a connection would be freed and if not you have then this timeout message on this last chance wait. Triple check connection are freed.

    Not directly related but:
    - using an empty catch clause is likely a bad idea, you likely never want to hide entirely any exception that could happen
    - while I'm at it, may I ask a question? I see sometimes a throw ex in a catch clause but never understood the purpose of doing that. Could it be a habit for those used to Java because of "checked exsceptions" ?

    Monday, August 31, 2015 4:34 PM
  • User-986267747 posted

    Hi asp.net4

    protected void Page_Load(object sender, EventArgs e) { string strQuery = "Select * from Store"; SqlCommand cmd = new SqlCommand(strQuery); DataTable dt = GetData(cmd); gridoutofstock.DataSource = dt; gridoutofstock.DataBind(); }

    According to your above code, you should close your sqlcommand. You could try with the code below.

    protected void Page_Load(object sender, EventArgs e)
            {
                string strQuery = "Select * from Store";
                SqlCommand cmd = new SqlCommand(strQuery);
                DataTable dt = GetData(cmd);
                cmd.Close();
                cmd.Dispose();
                gridoutofstock.DataSource = dt;
                gridoutofstock.DataBind();
            }
            private DataTable GetData(SqlCommand cmd)
            {
                gridoutofstock.DataBind();
                DataTable dt = new DataTable();
                String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AhlhaGowConnString"].ConnectionString;
                SqlConnection con = new SqlConnection(strConnString);
                SqlDataAdapter sda = new SqlDataAdapter();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                try
                {
                    con.Open();
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {  
                    con.Close();
                    cmd.Close();
                    cmd.Dispose();
    
                    sda.Dispose();
                    con.Dispose();
                }
            }

    Besides, There are a similar problem, you could refer to the following link so that you could get more information.

    http://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server

    I hope it's helpful to you.

    Best Regards

    Klein zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 18, 2015 5:45 AM
  • User753101303 posted

    Hi,

    Dispose the comman as well? Check what StoreClass does. Using an empty catch block is the last thing you want to do (ie if you have an exception in this code, NOBODY knows about it and as best you could painfully guess you have one when the app seems to behave strangely).

    In short the error message happens when connection are not closed properly. So double check you are closing all that needs to be closed and that uses a connection. The using statement might help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 18, 2015 9:41 AM