ExecuteReader requires an open and available Connection but connection is open and is not working

Question

I have a login form. Where my query runs through my database and cross checks if the entered parameters match the ones in the database then redirect you to a page. I put in a condition in my code to catch exceptions when the connection is open or its closed. I have tried looking up solutions like this one and others.

I have done the following.

• Including usings

However its still showing this error at run time

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

This is my code:

public void LWAPLogin(string username, string password)
    {
        string wrongCredentials = "Username does not exist. Or password is incorrect";
        string query = "Select Username, Password from LWAP where Username=@user AND Password=@password;";
        using (SqlCommand command = new SqlCommand(query, Connect.con))
        {
            command.Parameters.Add("@user", SqlDbType.VarChar, 50).Value = username;
            command.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = password;

            try
            {
                if (connection.con.State == ConnectionState.Open)
                {
                    using (SqlDataReader dr = command.ExecuteReader())
                    {
                        if (dr.Read())
                            Response.Redirect("LWAPHome.aspx");
                        else
                            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + wrongCredentials + "');", true);

                        dr.Close();
                    }
                    connection.con.Close();
                }


                else if (connection.con.State == ConnectionState.Closed)
                {
                    connection.con.Open();


                    using (SqlDataReader dr = command.ExecuteReader())
                    {
                        if (dr.Read())
                            Response.Redirect("LWAPHome.aspx");
                        else
                            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + wrongCredentials + "');", true);
                        dr.Close();
                    }
                    connection.con.Close();

                }
            }
            finally
            {
                //connection.con.Open();
            }
        }

    }

After getting advise from fellow programmers. I changed my code and the problem is fixed. However now l have a new problem. It is suppose to redirect to new page when all is well but it is not. The just changes the pages url to the desired page but stays on the login page.

Below is my edited code :

public void LWAPLogin(string username, string password)
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["tlcString"].ConnectionString))
        {
            string wrongCredentials = "Username does not exist. Or password is incorrect";
            string query = "Select Username, Password from LWAP where Username=@user AND Password=@password;";
            using (SqlCommand command = new SqlCommand(query, con))
            {

                command.Parameters.Add("@user", SqlDbType.VarChar, 50).Value = username;
                command.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = password;

                con.Open();

                using (SqlDataReader dr = command.ExecuteReader())
                {
                    if (dr.Read())
                        Response.Redirect("LWAPHome.aspx");
                    else
                        ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + wrongCredentials + "');", true);

                    dr.Close();
                }
                //connection.con.Close();
            }
            con.Close();

        }
    }

Please see image for the error it is now showing. I tried using different browsers but they all do the same thing.

Answer 1

You are creating a new SqlCommand(query, Connect.con)) specifying Connect.con as the connection to use, whereas you're checking the state of connection.con, a different SqlConnection instance. If you really do want to check anything, make sure you're checking the right thing.