ExecuteReader requires an open and available Connection. The connection's current state is connecting. in execute reader

Question

Here i use my webmethod.But when i going to connect it shows this error.But my code is good i think.

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

MyCode

public static List<CommonPages> GetCommonPagesDescription(int Type)
{
    List<CommonPages> CommonPageDescription = new List<CommonPages>();
    try
    {
        SqlCommand comGetAllFiles = new SqlCommand("GetCommonPageDescriptions", conDB);
        comGetAllFiles.CommandType = CommandType.StoredProcedure;
        if (conDB.State == ConnectionState.Closed)
            conDB.Open(); // <-- Debugger Skip this & goto next line
        comGetAllFiles.Parameters.Add("@Type", SqlDbType.Int);
        comGetAllFiles.Parameters["@Type"].Value = Type;

        SqlDataReader rdr = comGetAllFiles.ExecuteReader();//<-- error generating here
        DataTable dt = new DataTable();
        dt.Load(rdr);
        foreach (DataRow r in dt.Rows)
        {
            CommonPageDescription.Add(new CommonPages
            {
                Id = (int)r["Id"],
                Description = r["Description"].ToString(),
                Type = (int)r["Type"],
                UpdatedDate = (DateTime)r["UpdatedDate"],
                UpdatedBy = (Guid)r["UpdatedBy"]

            });
        }

    }
    catch (Exception ee)
    {
    }
    finally
    {
        conDB.Close();
    }
    return CommonPageDescription;
}

conDB Initialized here

 static SqlConnection conDB = new SqlConnection(ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString);

This is easy to avoid if you just create a new connection object, open it, do your work, then dispose of the connection. An empty catch is rarely a good thing, by the way. — LarsTech, May 09 '14 at 19:04
I'm guessing that you are being passed a connection that is in the "connecting" state, maybe it was opened asynchronously or something. Your State == Closed check doesn't execute the Open method. — MarkPflug, May 09 '14 at 19:05
The error message tells you the problem. `conDB.State == ConnectionState.Connecting`. — Rick S, May 09 '14 at 19:08
@apomene static SqlConnection conDB = new SqlConnection(ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString); — TechGuy, May 09 '14 at 19:11
@Mark Yes open method not executing here..How do i solve this ? — TechGuy, May 09 '14 at 19:17
You could try if(conn.State != ConnectionSTate.Open) conn.Open(). I don't know if that works in certain states though. — MarkPflug, May 09 '14 at 19:19
There are plenty of comments to help you solve this problem. — Rick S, May 09 '14 at 19:19
@TechGuy your edit does not provide the information that I asked for. — usr, May 09 '14 at 19:19

T McKeown · Answer 1 · 2014-05-09T19:23:41.710

conDB must be a shared connection? Probably not a good idea, leverage connection pooling.

To fix it, consider opening and closing a connection per request. Don't care if the voodoo sayers that this inefficient, in truth you want to open/close connections as little as possible but sometimes you need to go to the DB for a single reason. There are ways you could share a connection better, using a context pattern for example. But to solve your immediate problem structure your DB calls this way.

try
{
  using( System.Data.Common.DbConnection conn = CreateConnection() )
  {
     //create your command...
     //create your reader/or execute your command...
  }
}

How to solve it.. pls check my updated question – TechGuy May 09 '14 at 19:18 — TechGuy, May 09 '14 at 19:18

score 0 · Accepted Answer · answered May 09 '14 at 19:53

Instead of opening the connection when it is closed, try to open the connection when it is not opened.

public static List<CommonPages> GetCommonPagesDescription(int Type)
{
    List<CommonPages> CommonPageDescription = new List<CommonPages>();
    try
    {
        SqlCommand comGetAllFiles = new SqlCommand("GetCommonPageDescriptions", conDB);
        comGetAllFiles.CommandType = CommandType.StoredProcedure;
        if (conDB.State != ConnectionState.Open)
            conDB.Open(); // <-- Debugger Skip this & goto next line
        comGetAllFiles.Parameters.Add("@Type", SqlDbType.Int);
        comGetAllFiles.Parameters["@Type"].Value = Type;

        SqlDataReader rdr = comGetAllFiles.ExecuteReader();//<-- error generating here
        DataTable dt = new DataTable();
        dt.Load(rdr);
        foreach (DataRow r in dt.Rows)
        {
            CommonPageDescription.Add(new CommonPages
            {
                Id = (int)r["Id"],
                Description = r["Description"].ToString(),
                Type = (int)r["Type"],
                UpdatedDate = (DateTime)r["UpdatedDate"],
                UpdatedBy = (Guid)r["UpdatedBy"]

            });
        }

    }
    catch (Exception ee)
    {
    }
    finally
    {
        conDB.Close();
    }
    return CommonPageDescription;
}

ExecuteReader requires an open and available Connection. The connection's current state is connecting. in execute reader

2 Answers2