asp.net ExecuteReader requires an open and available Connection. The connection's current state is open

Question

My ERP web application written in c#.net throwing this error frequently

I have Globals.cs file under App_Code folder like:

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Data.Sql;

public static class Globals
{
public static string mycon = ConfigurationManager.ConnectionStrings["WebConnStr"].ConnectionString;
public static SqlConnection con = new SqlConnection(mycon);
public static SqlCommand cmd = new SqlCommand();
public static SqlDataReader dr;

public static void Initialize(string CmdType, string CmdText)
{
    try
    {
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        cmd.Connection = con;
        if (CmdType == "StoredProcedure")
        {
            cmd.CommandType = CommandType.StoredProcedure;
        }
        else
        {
            cmd.CommandType = CommandType.Text;
        }
        cmd.CommandText = CmdText;
        cmd.Parameters.Clear();
    }
    catch (Exception ex)
    {
    }

}
}

my connection string in web.config looks like:

<connectionStrings>
<add name=WebConnStr connectionString="Data Source=servername;Initial Catalog=dbname;User ID=username; Password=12345" providerName="System.Data.SqlClient"/>
</connectionStrings>

my sample aspx.cs codes like:

Gridview Bind:

DataTable dt = new DataTable();
Globals.Initialize("StoredProcedure", "[sp_getemp]");
Globals.dr = Globals.cmd.ExecuteReader();
dt.Load(Globals.dr);
GridView1.DataSource = dt;
GridView1.DataBind();

Insert Query:

Globals.Initialize("StoredProcedure", "[sp_insertemp]");
Globals.cmd.Parameters.AddWithValue("@EmpID", txtempid.Text);
Globals.cmd.ExecuteNonQuery();

Data Reading:

private string GetEmpName(string empcode)
{
    Globals.Initialize("Text", "SELECT EmpName from tbl_emp where EmpID=@EmpID");
    Globals.cmd.Parameters.AddWithValue("@EmpID", empcode);
    Globals.dr = Globals.cmd.ExecuteReader();
    if (Globals.dr.Read() == true)
    {
        return Globals.dr["EmpName"].ToString();
    }
    else
    {
        return "";
    }
}

May I know what might have gone wrong in Globals.cs file and how do I fix it?

What is wrong is that your code is *absolutely terrible*. Please read up on connection pooling at least. — Ian Kemp, Nov 03 '15 at 10:54
The error messages tells you that you need an open and available connection and the connection state is open. I guess that means that the connection is not available. — Lajos Arpad, Nov 03 '15 at 11:00
Please look at the link that provided. It's _almost_ the same thing that you faced. Don't stuck with `Open` and `Connecting` words.. — Soner Gönül, Nov 03 '15 at 11:00

score 1 · Answer 1 · answered Nov 03 '15 at 10:57

That is why you should be using the using statement to get rid of this type of error. Something like

using (SqlConnection connection = new SqlConnection(connectionString))
{
   //some code
}

You can refer this MSDN and read about connection pooling:

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

score 0 · Answer 2 · answered Nov 03 '15 at 10:56

0

Looks like you need to open a connection before executing code, and example being conn.Open();

answered Nov 03 '15 at 10:56

JiraiyaUK

58
5

asp.net ExecuteReader requires an open and available Connection. The connection's current state is open

2 Answers2