Error when multiple user access same database

Question

0.00/5 (No votes)

See more:

i just deploy my website in IIS but when mutiple user access my database so it gives error..

ExecuteReader requires an open and available Connection. The connection's current state is connecting.
 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 
Exception Details: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting.
 
Source Error: 
 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

so what can i do ?

if there is solution please Tell Me....
Please Help Me..
Mitesh

Posted 25-May-12 22:49pm

Mits Machhi

Updated 26-May-12 10:51am

Nelek

v3

Add a Solution

2 solutions

Solution 1

do one thing

1. before you are opening the connection close the connection and reopen it like this

C#

if (connectionobject.State == ConnectionState.Open)
                    connectionobject.Close();
                connectionobject.Open();

or

C#

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

Posted 25-May-12 23:06pm

hitech_s

Updated 25-May-12 23:10pm

v3

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 · Accepted Answer · 2012-05-25T23:11:00

Solution 2

Depends on what kind of database you are using, and how you are connecting to it: If it is an SQLCe database, then only one user can access it at a time, for example. If it is an Access database and you connect via ODBC, then in theory multiple users can use it, - but in practice this rapidly becomes a pain and doesn't work too well.
If it is an SQL database, then assuming there are sufficient connections available at the SQL end (and there is a limit of 32,767 in total at any one time, but you will run out of resources before you get anywhere near this) you should be ok - but the DB admin can set a connections count limit if he wants to to improve SQL performance.

Check your code: Look at your stack trace, and see if the connection has been opened. If not, look at what the connection string is, and check everything is ok there.

Sorry to be vague, but with the info you gave us, I can't be more exact.

Posted 25-May-12 23:11pm

OriginalGriff

Comments

[no name] 26-May-12 5:16am

i use sql server database. but when more than two user access my database with different table it gives error...

OriginalGriff 26-May-12 5:26am

What is your connection code?

[no name] 26-May-12 5:27am

public void setcon()
{
try
{

con = new SqlConnection();
con.ConnectionString = @"Data Source=DATA-SERVER;Initial Catalog=fmweb;User ID=sa";
if (con.State == ConnectionState.Closed)
{
con.Open();
}
}
catch (Exception e1)
{
if (con.State != ConnectionState.Closed)
con.Close();
}

}

use Appcode in Class file.
and on every page load :
if (!IsPostBack)
{
c.setcon();
}

OriginalGriff 26-May-12 5:30am

Is "con" static or class instance?

[no name] 26-May-12 5:57am

public static SqlConnection con;

OriginalGriff 26-May-12 6:02am

So, you have a single connection which you are trying to use for a random (and unpredictable) number of users? Any of which might close it, and bugger up all the others? And you are surprised when you get problems? :laugh:

Don't use a static connection, don't create a connection automatically! Create it at either an instance or (preferably) as local to a method when you need it, and Dispose it when you have finished with it. Since you are dealing with human response times, you won't add any noticeable time overhead, and you will consume a lot less server resources - which may well speed up response times!

[no name] 26-May-12 6:09am

so what can i do.....? Please Tell Me... so you say use
con = new SqlConnection();
con.ConnectionString = @"Data Source=DATA-SERVER;Initial Catalog=fmweb;User ID=sa";
if (con.State == ConnectionState.Closed)
{
con.Open();
}
at every page load instead of c.setcon() method.

OriginalGriff 26-May-12 6:25am

No.
You could, if you move "con" to non static, and private, but even then you are just creatign pottential difficulties for yourself, because you don't necessarily Dispose your connections when you are finished with them.

Instead, when ever you access your DB:
using (SqlConnection con = new SqlConnection(@"Data Source=DATA-SERVER;Initial Catalog=fmweb;User ID=sa"))
{
con.Open();
//Do your database stuff
}
This closes the connection and disposes it for you. It also means that the connections cannot interfere with each other, because they are never shared - "con" goes out of scope at the end of the using block. You should also use similar code for your SqlCommand objects.

[no name] 26-May-12 6:36am

so in Appcode i would to write like..
SqlConnection con = new SqlConnection(@"Data Source=DATA-SERVER;Initial Catalog=fmweb;User ID=sa")
{
con.open();
}

so no need to write connection state

OriginalGriff 26-May-12 6:58am

Yes, each time you want to access the DB. You then use this "con" object and include all your DB access code inside the curly braces.
Oh, and get rid of your static "con" object because this replaces it.
(at the moment you haven't dumped it, and your code is still not working as a result)

[no name] 26-May-12 7:06am

but gives error when more than two user or more access the same database. so which way all the Website Build.... Please Help Me..

OriginalGriff 26-May-12 7:34am

Copy and paste a method that accesses the database so I can see what you have now.

[no name] 26-May-12 8:00am

there is log out button on click event and there is no database in my website. i use directly from the server.
protected void LinkButton2_Click(object sender, EventArgs e)
{
if(Class1.document_reader != null)
{
Class1.sign_out=System.Convert.ToString(DateTime.Now);
string s = "insert into User_log values('" +lbl_uname.Text+ "','" +string.Format("{0:dd/MM/YYYY}",Class1.sign_in)+ "','" + string.Format("{0:dd/MM/YYYY}",Class1.sign_out) + "','" + (Class1.document_reader.Substring(0, Class1.document_reader.Length - 1)) + "')";
SqlCommand cmd = new SqlCommand(s, c.getcon());
cmd.ExecuteNonQuery();
Session.Abandon();
Response.Redirect("default.aspx");
}
else
{
Class1.document_reader = "No Document Read";
Class1.sign_out=System.Convert.ToString(DateTime.Now);
string s = "insert into User_log values('" +lbl_uname.Text+ "','" + string.Format("{0:dd/MM/YYYY}",Class1.sign_in)+ "','" + string.Format("{0:dd/MM/YYYY}",Class1.sign_out) + "','" + (Class1.document_reader) + "')";
SqlCommand cmd = new SqlCommand(s, c.getcon());
cmd.ExecuteNonQuery();
Session.Abandon();
Response.Redirect("default.aspx");
}

}

but it works fine when single user use the website but problem when multiple access user to the website.

OriginalGriff 26-May-12 9:33am

Good grief!
There are so many things wrong there that it is hard to work out where to start!
Why are you passing dates as strings?
Why are you storing them in your Class1 object as strings? Is DateTime not working where you are?
Should I assume as well that Class1 is a static variable? Or is Class1.Sign_out static?
Did you decide to ignore the advice you asked for because it wasn't what you wanted? Why are you still using a static con object?
Have you never heard of parametrized queries?

[no name] 28-May-12 0:31am

dear i can not use the static variable like sign_in,sign_out,etc. as string.but the main problem is that error of execute Reader that i not use in entire application.if you understand the problem , so please tell me. i just remove the static connection object. but when i use the select query and in that i use the following method to retrive the connection.

public SqlConnection getcon()
{
return con;
}

so please help me some more....
Thanks

[no name] 26-May-12 6:48am

when three user access the database at different place and different table it gives error.

Nelek 26-May-12 16:52pm

+5