The connection's current state is Closed??

Hi all,

I'm trying to use a sqlDataReader to load data into a DropDownList on a .aspx page, but get the following error:

Server Error in '/YLCPDTdb' Application
ExecuteReader requires an open and available connection. The connection's current state is Closed.

But havn't I opened it with the conn.Open(); statement
Here's my code:

private void Page_Load(object sender, System.EventArgs e)
        {
            / Load country names into countryCboBx
            try
            {
                / Instantiate the connection
                SQLConnect();
                conn.Open();
                /Read Data
                SqlDataReader rdrCountry = countryLoad.ExecuteReader();
                /Display in countryCboBx
                countryCboBx.DataSource = rdrCountry;
                countryCboBx.DataMember = "countryName";
                /Close reader
                rdrCountry.Close();
            }
            finally
            {
                / Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        / SQL Connection
        public void SQLConnect()
        {
            conn = new SqlConnection("Server=(local);DataBase=YLCdbSQL;Integrated Security=SSPI");
        }

Any ideas what the problem might be

Thanks

Answer this question

The connection's current state is Closed??

Stut
Thanks again guys.

Vikram, tried your suggestion, still not working.

I'm thinking the error is related to 'sa' login. the sa login properties is set to 'SQL Server Authentication', i am unable to set it to 'Windows Authentication', the radio button is not enabled. Does 'sa' need to have 'Windows Authentication' checked to work in this instance if so how can it be done

Thanks.
Mostafa Omar
what about the command is it properly using the connection
Curt Carpenter
Just found this buried piece of code in the initialise component section, thought it might have something to do with the error.

this.conn.ConnectionString = "workstation id=WORK;packet size=4096;user id=sa;data source=WORK;persist security" +
" info=False;initial catalog=YLCdbSQL";

Should it have a password entry
AndrewB
Yes it should!

Vikram
Rahul Ohri
I've been playing around with the SqlConnection string changing it to

SqlConnection conn = new SqlConnection("Server=(local);DataBase=YLCdbSQL;User ID=sa;Password=myPassword");

But I continue getting the 'login failed for user 'sa'' error. Ialso get this error if i replace the sa login account with a different user accounts login details.

Any more ideas
Mareen Philip MSFT
does your sql server used mix authentication or SQL authentication if it just uses sql change it to mixed and the issue should get fixed, reason you get the error is because you have no sql user in your con string or password for that matter
simon mcinnes
I do not know... all the documentation i read says its an auth issue and to change the mode. I did see somewhere about re-installing the SQL server but I do not think that is it.
PhilipLanier
Quick Question: Are u able to connect using the SQL Query Analyzer

Regards,
Vikram
StevenR2
change your connect to have Integrated Security=true
Vytas
Thanks again wacko, tried that, but still getting the same error.

Any more ideas
bodalal
Ok, seems i didn't set the command up properly, i've added the code

SqlCommand countryLoad = new SqlCommand ("SELECT countryName FROM tblCountry", conn);

above the

SqlDataReader rdrCountry = countryLoad.ExecuteReader();

line and that's solved the above error.

However now i'm getting another error 'Login failed for user 'sa'', any ideas

Thanks.
debeerBiz
Hi,

Now that you have changed it to Windows Authentication make sure your Windows UserId is part of the SQL Server Logins. For this in Enterprise Manager goto Security and open the Logins. The current User id with which you have logged on must be listed there.

Also click on the user id and goto the Database Access Tab and see which are the databases for which the user has access to.

Regards,
Vikram
Simon_E
Yes, can connect using either windows authentication or SQL sa + password login.
djchapin
Thanks wacko.

I'm opening Enterprise Manager, selecting my server --> Properties, then Security tab and it already shows authentication as SQL Server and Windows as the selected option. Is this what you mean by using mixed authentication if not how do I set it

Thanks again.
The connection's current state is Closed??

Answer this question