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

Question

1.00/5 (1 vote)

See more:

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

The site works fine on my localhost server but it sometimes gives this error on server. what's the problem?

C#

public class Database
   {
       public static string _connectionstring = "Provider=\"Microsoft.Jet.OLEDB.4.0\";Mode=Share Deny None;Data Source=\"" +
          HttpContext.Current.Request.PhysicalApplicationPath + "/App_Data/Borsa_db.mdb\";User ID=Admin;Password=;";

       static OleDbConnection cnn = new OleDbConnection(_connectionstring);

       public static DataSet FillDataSet(string sql, string Tablo)
       {
           DataSet ds = new DataSet();

           OleDbDataAdapter da = new OleDbDataAdapter(sql, cnn);

           da.Fill(ds, Tablo);

           return ds;
       }

       public static int ExecutenonQuery(string sql, bool sonuc)
       {
           int deger = -1;

           if (sql != "")
           {
               OleDbCommand cmd = new OleDbCommand(sql, cnn);

               if (cnn.State == ConnectionState.Closed) cnn.Open();

               try
               {
                   if (sonuc)
                   {
                       cmd.ExecuteNonQuery();
                       OleDbCommand cmd1 = new OleDbCommand("SELECT @@IDENTITY AS SAYI", cnn);
                       deger = Convert.ToInt32(cmd1.ExecuteScalar());
                   }

                   else
                   {
                       deger = cmd.ExecuteNonQuery();
                   }
               }
               finally
               {
                   cnn.Close();
               }
           }

           return deger;
       }

       public static object ExecuteScalar(string sql)
       {
           object deger = null;

           OleDbCommand cmd = new OleDbCommand(sql, cnn);

           if (cnn.State == ConnectionState.Closed) cnn.Open();

           try
           {
               deger = cmd.ExecuteScalar();
           }
           finally
           {
               cnn.Close();
           }

           return deger;
       }

       public static OleDbDataReader DataReader(string sql)
       {
           OleDbCommand cmd = new OleDbCommand(sql, cnn);
           OleDbDataReader dr;

           if (cnn.State == ConnectionState.Closed) cnn.Open();
           try
           {
               dr = cmd.ExecuteReader();
           }
           finally
           {
               cnn.Close();
           }

           return dr;
       }
   }

Posted 3-Nov-15 23:19pm

oumarr

Add a Solution

Comments

Herman<T>.Instance 4-Nov-15 5:37am

in which method and at which line?

Gautham Prabhu K 4-Nov-15 7:16am

Does the server instance have access to the MSAccess file?
Looks like a permission issue to me since its working in local.

Rojalin Sahoo 4-Nov-15 8:07am

where is your connection open and close statement for FillDataSet() ?

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 4-Nov-15 12:22pm

Rojalin, DataAdapter automatically opens and closes the connection.

oumarr 4-Nov-15 9:18am

if (cnn.State == ConnectionState.Closed) cnn.Open();
try
{

DataSet ds = new DataSet();

OleDbDataAdapter da = new OleDbDataAdapter(sql, cnn);

da.Fill(ds, Tablo);

return ds;
}
finally
{

cnn.Close();

}

}
add "open and close" but this time sometimes give "this connection closed" error.what can i do?

oumarr 4-Nov-15 9:50am

in addition to when refresh page,it's ok. but sometimes repeat error especially when many users connect the web site

1 solution

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)

Richard Deeming · Answer 1 · 2015-11-04T05:28:00

Don't store connection objects in static variables, particularly in an ASP.NET application. That single connection will be shared between every single request to your application, across multiple threads, which will cause lots of difficult to find errors.

Instead, create the connection each time you need it, and make sure you wrap it in a using block to ensure that it's always disposed of correctly.

You can avoid the HttpContext.Current.Request.PhysicalApplicationPath reference by using the |DataDirectory| placeholder. In an ASP.NET application, this always points to the App_Data directory.

plain

private const string Connectionstring = "Provider=\"Microsoft.Jet.OLEDB.4.0\";Mode=Share Deny None;Data Source=\"|DataDirectory|\\Borsa_db.mdb\";User ID=Admin;Password=;";

public static OleDbConnection CreateConnection()
{
    var result = new OleDbConnection(ConnectionString);
    result.Open();
    return result;
}

Your methods suggest that you are writing code which is vulnerable to SQL Injection[^]. You need to modify them to accept parameters, and update your code to pass the parameters correctly, instead of using string concatenation.

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[^]
SQL injection attack mechanics | Pluralsight [^]