The connection was not closed. The connection's current state is open ! asp.net c#

Question

I have 3 tier web site asp.net c#. I want to select user and check his user-type for login.but my code not work. and above error shown. pleas help me what do I do. my code DAL:

public SqlDataReader select_user(string UserName)
        {
            connect.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM UserInfo WHERE UserName=@UserName", connect);
            cmd.Parameters.AddWithValue("@UserName", UserName);
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

and BL:

 public SqlDataReader Get_user()
        {
            return da.select_user(UserName);
        }

and code of interface:

 try
        {

            bll.UserName = UserNameTextBox.Text;
            Session.Add("_UserName", bll.UserName);
            bll.Get_user();
            SqlDataReader dr = bll.Get_user();
            if (dr.HasRows)
            {
                dr.Read();
                int Utyp = bll.UserTyp;
                if (Utyp == 0)
                {
                    Session.Add("Msg_", "dear user. you are unactive yet");
                    Response.Redirect("ShowMessage.aspx", false);
                }
                else
                    if (Utyp == 1)
                    {
                        Response.Redirect("index.aspx", false);
                    }

            }
            else
            {

            }
        }
         catch (Exception ex)
         {
             Session.Add("Msg_", "  : <br>" + ex.Message);
             Response.Redirect("~/ShowMessage.aspx");
         }

can you use using statement to wrap all ado.net objects like using() {} and that will take care to close and dispose the objects — Deepu Madhusoodanan, Sep 21 '17 at 21:53

mjwills · Answer 1 · 2017-09-21T21:54:27.383

1

connect.Open();

should be changed to:

if (connect.State == ConnectionState.Closed) {
    connect.Open();
}

to ensure that it tries to open the connection only once, rather than multiple times.

Note that you may also wish to consider adding using statements as per this answer.

edited Sep 21 '17 at 21:54

answered Sep 21 '17 at 21:44

mjwills

23,389
6
40
63

The connection was not closed. The connection's current state is open ! asp.net c#

1 Answers1