CODEWITHSUNDEEP
c#.netexception
Jaya Mayu
Jaya Mayu

Reputation: 17247

Connection is closed exception

I'm new to C# and trying to establish a C# db connection. But I'm getting an exception.

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

following is the code 

public void executeCommand()
{
    SqlConnection con = new SqlConnection(connectionString);

    try
    {
        con.Open();
    }
    catch (Exception ex)
    {
    }

    SqlDataReader rdr = null;
    SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", con);

    try
    {
        rdr = cmd.ExecuteReader();
    }
    catch (Exception)
    {
        throw;
    }

    rdr.Close();
   // rdr.Close();
}

and this is my connection string 

public static string connectionString = 
    "Data Source=(local);Initial Catalog=service;User Id='mayooresan';Password='password';";

Thanks for your time in advance.

Upvotes: 0

Views: 6681

Answers (5)

Tigran
Tigran

Reputation: 62248

Most probabbly connection object fails to open a connection, but as you are catching it, you can not figure out the error. To be clear: 

try
{
   con.Open();
}
catch (Exception ex)
{
   MessageBox.Show(ex.ToString()); //ADD THIS STRING FOR DEBUGGING, TO SEE IF THERE IS AN EXCEPTION.
}

Hope this helps.

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460018

You don't close the connection and reader when an exception was raised, therefor you need to use the finally-block of a try/catch or the using-statement which closes implicitely:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using(SqlCommand command = new SqlCommand(queryString, connection)
    {
        connection.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // do something with it
            }
        }
    }
}

Apart from that you should not use empty catch blocks. If a connection cannot be opened, it cannot be used. Then you should log that and throw the exception, but don't act as if nothing had happened.

Upvotes: 2

Diego
Diego

Reputation: 36126

are you debugging the code? if not, you wont be able to see the exception because you don't have anything on your catch

Also I suggest this approach to use on your scenario:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    SqlCommand command = new SqlCommand(queryString, connection);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(String.Format("{0}", reader[0]));
    }
}

Upvotes: 1

hehewaffles
hehewaffles

Reputation: 582

Try wrapping everything in one try-catch block. As it stands now, if an exception is thrown when you try to open the connection, it will fail silently. Try this code instead:

try
{
    SqlConnection con = new SqlConnection(connectionString);
    SqlDataReader rdr = null;
    SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", con);
    rdr = cmd.ExecuteReader();
}
catch(Exception)
{
    throw;
}

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67065

You are catching any exceptions when opening the connection. Most likely the connection is not opening and is throwing an error. Remove the try/catch at the opening of the connection and you will probably see why the connection is not open

Upvotes: 3

Related Questions