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

[Sunny]

G'day Developers and Professionals,, :)


NOTE: When I created a user <MYUser> i have selected a SQL
Authentication in SQL Server 2005.

connStr = "Server=MYSQL2K5Server;Database=MYDatabase;User
ID=MYUser;Password=MYUserPWD;Trusted_Connection=True;";

try
{
myConnection = new SqlConnection(connStr);
myConnection.Open();
}
catch (SqlException sqlExp)
{
.............................
}
catch (Exception genExp)
{
.............................
}

Now here I have added a method called IsUserHasAccess(which takes
username as string)
and returns true or false by checking whether the username exists in
one of the table
called "tblUsers" in MyDatabase.

queryStr = "SELECT [PKUserID] FROM tblQuoteUser WITH (NOLOCK) " +
" WHERE [NetworkLoginName] = '" + sUsername.Trim()
+ "'";

try
{
_mSqlCmd = new SqlCommand(queryStr, myConnection);
SqlDataReader tDReader2 = _mSqlCmd.ExecuteReader();

if (tDReader2.HasRows)
{
retVal = true; // Record Exists having same User Name
}
else
{
erString = "User Does not have access to Customer Quoting
DB !!";
retVal = false;
}

tDReader2.Close();
this._mSqlCmd.Dispose();

}
catch (SqlException sqlExp)
{
erString = sqlExp.ToString();
retVal = false;
}
catch (Exception genExp)
{
erString = genExp.ToString();
retVal = false;
}

above is a very simple method just to check whether user has
appropriate access to
the app or not but in my case it throws following exception.


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

I need little help regarding above error.

Here I am using SQL Server 2005 DB, VS 2K5 for development and .NET
Framework 2.0 based C# windows application.


Thanks,,,,

[Cerebrus]

As the exception clearly mentions, you need an open (and available)
connection. From your post, it is unclear how the two code snippets
relate to each other, where is the IsUserHasAccess method (as an
aside, shouldn't it be named "DoesUserHaveAccess", I mean
grammatically speaking ?)

It appears that the myConnection variable is global and you open it in
a separate method. I would consider that treatment of the connection
object cavalier to say the least.

[Sunny]

Cerebrus,

You are right about the grammatical mistake about IsUserHasAccess
method but now I cant do anything as this is not
my project, it is someone who left it in between and being a senior to
him and none was ready to take this project
there was no choice for me,,.

Now back to the main point,,, Okie in above code both snippets are in
one class called SQLConnect,
and the first snippet has been called when you use this component
class I mean when you create a new
object of that class.

Second snippet is (which is that IsUserHasAccess) method, it actually
runs the query and retrieve a
checkpoint (using DataReader.HasRows() method) which says whether the
query returns any row or not
and as if it is,, it means the table (called tblStaff) has user
inserted into it.

Now I have checked the connection state before using EXECUTE READER
if (myConnection.State == ConnectionState.Open)

so ultimately the second code snippet look like this -->

queryStr = "SELECT [PKUserID] FROM tblQuoteUser WITH (NOLOCK) " +
" WHERE [NetworkLoginName] = '" +
sUsername.Trim()
+ "'";


try
{

if (myConnection.State == ConnectionState.Open)

{
_mSqlCmd = new SqlCommand(queryStr, myConnection);
SqlDataReader tDReader2 = _mSqlCmd.ExecuteReader();


if (tDReader2.HasRows)
{
retVal = true; // Record Exists having same User Name
}
else
{
erString = "User Does not have access to Customer Quoting
DB !!";
retVal = false;
}


tDReader2.Close();
this._mSqlCmd.Dispose();

}
}
catch (SqlException sqlExp)
{
erString = sqlExp.ToString();
retVal = false;
}
catch (Exception genExp)
{
erString = genExp.ToString();
retVal = false;
}

and I have tried to DEBUG it and it fails at
SqlDataReader tDReader2 = _mSqlCmd.ExecuteReader();

So dont have clue mate,, why it is coming suddenly,, as the database
previously was on SQL 2K and now moved to SQL 2K5.

Thanks in Advance.
Sunny

[Andrew Badera]

You should never, EVER have to check a connection's state of openness before executing. Your data transactions should be atomic. Like I said in another thread recently, acquire late, release early. If you're using a connection for multiple readers, I hope it's part of a SqlTransaction to boot.

--
--Andy Badera
http://andrew.badera.us/
and...@badera.us
(518) 641-1280
Google me: http://www.google.com/search?q=andrew+badera

[Cerebrus]

Even so, I fail to understand how a Connection can be open on one
line, and closed on the next(to next) line. Unless we're still not
seeing the part of the code that is the miscreant.

[Andrew Badera]

If it were atomic, we'd know we were seeing everything, now wouldn't we? ;)

--
--Andy Badera
http://andrew.badera.us/ http://flipbitsnotburgers.blogspot.com/

[Sunny]

Got it,,

Here I have found out that there is nothing to do with any code
change,, as VS 2005 Exception library does
not have different types of detailed exception handling or messages,,
sometimes a user who is accessing
database using a connection string with default database user added
into ConnStr, has to have Mixed group
authentication access to SQL Server 2005, and due to that it shows
that connection not open.

Step I have taken to resolve the probem :
------------------------------------------------------------

1. Create a Database user which everyone can access through connstr.
2. To have that server access i have created a group under MyComputer
(right click) -> Manage
-> Users and Groups -> Create New Group -> CustQuoteAccess (with
no other than a just a std Users Group access)
3. Now I have added all the users wanted to use the application to
that CustQuoteAccess group.
4. Last step,, add that group to SQL Server 2005 -> Security -> Logins
-> Add in As Groups.

And now its working fine. What I understand from the above steps I
have taken, is when .NET app tries to connect
to sql2k5, it lays the connection channel with an open connection but
with having an attribute authentication failed or
may be access not allowed which ultimately does not allow that user to
run ExecuteReader through App as that
user does not have access enabled to that database.

Guys I might be wrong with this but I have nearly tried everything wht
you guys have suggested me to do and the changes
I have done as above worked fine with that whole group (consists of
around 3400 users to use that app).

I will create a new exception library based on general .NET
Exceptions, and will add it here or may be on CodeProject
website which helps other user in case if they have this kind of
problem.

Thanks guys for your help.
Cheers
Sunny :)


What I understand from the steps I have taken was