Why this error occors in my ASP.NET project?

Question

1.00/5 (2 votes)

See more:

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.

C#

sing System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class UserHome : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("Data Source=.\\sql2008;initial catalog=ProductivePlusDB;user id=sa;password=123456");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["emailid"] == null || Session["emailid"].ToString() == "")
        {
            Response.Redirect("Default.aspx");
        }
        
       // string user = System.Environment.UserName;
       // Response.Write(user);

        try
        {

            con.Open();
            string qry = "select * from ShopOwnMast where EmailId='" + Session["emailid"].ToString() + "'";
            SqlDataAdapter adp = new SqlDataAdapter(qry, con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            Session["ShopId"] = dt.Rows[0][8].ToString();


            string shopid = Session["ShopId"].ToString();
            string qry1 = "Select * from ShopMast where shopid ='" + shopid + "'";
            SqlDataAdapter adp1 = new SqlDataAdapter(qry1, con);
            DataTable dt1 = new DataTable();
            adp1.Fill(dt1);
            lblShopName.Text = dt1.Rows[0][1].ToString();
            getdata();

        }
        catch (Exception ex)
        {
            System.Console.WriteLine(ex);
        }
        finally 
        {
            con.Close();
        }

        
        //Response.Write(Session["ShopId"].ToString());

    }

What I have tried:

i have no idea what to do. my project is not running now..

did i made mistake in closing and opening connection?
what should i do?

Posted 19-Apr-16 5:41am

FireMonkey018

Updated 19-Apr-16 16:46pm

Patrice T

v4

Add a Solution

Comments

Richard Deeming 19-Apr-16 12:11pm

So you want us to tell you whether you've made a mistake in your code, without seeing your code?!

Click "Improve question" and update your question with the relevant parts of your code.

FireMonkey018 19-Apr-16 12:17pm

there is no error in my code but i think this is happening because i did not closed connection properly..

Richard MacCutchan 19-Apr-16 12:30pm

Quite possibly, as the message suggests.

You also need to use proper parameterised queries for your SQL text, not concatenated textboxes; it leaves you open to SQL injection (Google will explain it).

Richard Deeming 19-Apr-16 13:52pm

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Richard Deeming 19-Apr-16 13:52pm

Also, don't store passwords in plain-text:
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

Richard Deeming 19-Apr-16 13:53pm

And you still haven't shown the relevant parts of your code. Where is con defined?

3 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)

OriginalGriff · Answer 1 · 2016-04-19T08:15:00

Your reposted question shows a little more info - don't do that, edit your existing question using the Improve Question to provide better info - and it's possible that that shows what the problem is.

C#

try
        {
 
            con.Open();
            string qry = "select * from ShopOwnMast where EmailId='" + Session["emailid"].ToString() + "'";
            SqlDataAdapter adp = new SqlDataAdapter(qry, con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            Session["ShopId"] = dt.Rows[0][8].ToString();
 

            string shopid = Session["ShopId"].ToString();
            string qry1 = "Select * from ShopMast where shopid ='" + shopid + "'";
            SqlDataAdapter adp1 = new SqlDataAdapter(qry1, con);
            DataTable dt1 = new DataTable();
            adp1.Fill(dt1);
            lblShopName.Text = dt1.Rows[0][1].ToString();
            getdata();
 
        }
        catch (Exception ex)
        {
            System.Console.WriteLine(ex);
        }
        finally 
        {
            con.Close();
        }

Couple of things to check:
First off, don't use SELECT * FROM if you are only going to use one row and one column - it's very inefficient. Instead use

SQL

SELECT TOP 1 MYColumnName FROM

The TOP 1 part restricts it to one row (which is what you would hopefully get anyway).
The named column means you don't fetch information you aren't going to use.
The second thing that worries me is that when you have used the one data item, you call a method called getdata - and I'm wondering if that method is the one you are in. I.e.

C#

private void getdata()
{
    try
    {

        con.Open();
        string qry = "select * from ShopOwnMast where EmailId='" + Session["emailid"].ToString() + "'";
        SqlDataAdapter adp = new SqlDataAdapter(qry, con);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        Session["ShopId"] = dt.Rows[0][8].ToString();


        string shopid = Session["ShopId"].ToString();
        string qry1 = "Select * from ShopMast where shopid ='" + shopid + "'";
        SqlDataAdapter adp1 = new SqlDataAdapter(qry1, con);
        DataTable dt1 = new DataTable();
        adp1.Fill(dt1);
        lblShopName.Text = dt1.Rows[0][1].ToString();
        getdata();

    }
    catch (Exception ex)
    {
        System.Console.WriteLine(ex);
    }
    finally
    {
        con.Close();
    }
}

If so, then your code will recursively call itself, and that will use the entire SQL connection pool very, very quickly - which is one of the things that could easily cause the effect you have.

And as Richard has said: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Jason Henderson · Answer 2 · 2016-04-19T09:35:00

I won't comment on your style or other bad things I see in your code. I will assume you are just trying to get something working.

My suggestion would be to try a using block for the connection and sqldataadapter which will ensure that these objects get disposed and all connections get closed.

C#

using (con = new SqlConnection("DataSource=.\\sql2008;initialcatalog=ProductivePlusDB;user id=sa;password=123456")
{
    con.Open();
    string qry = "select * from ShopOwnMast where EmailId='" +  
        Session["emailid"].ToString() + "'";

    using (SqlDataAdapter adp = new SqlDataAdapter(qry, con))
    {
        // all your other code
    }
}

Jason Henderson · Answer 3 · 2016-04-19T16:22:00

Solution 3

Using it will ensure that your connections are closed.

I'm not sure its even using a pooled connection. You normally need to put pool parameters in your connection string from what I understand.

Posted 19-Apr-16 16:22pm

Jason Henderson

Why this error occors in my ASP.NET project?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0