There is already an open DataReader associated with this Command which must be closed

[krishnamurtrhy]

Im receiving the above msg even after using MultipleActiveResultSets=True to connection string

public partial class registration : System.Web.UI.UserControl
{
    string gender;

    SqlCommand cmd = null;
    SqlConnection con = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection("Server=.; database=nkm; user id=sa; pwd=karthavya");
        con.Open();
        cmd = new SqlCommand();
        cmd.Connection = con;

        int years = DateTime.Now.Year;

        int i = 1;
        while (i < 32)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            days.Items.Add(L);
            i++;
        }

        for (i = 1; i < 13; i++)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            month.Items.Add(L);

        }

        for (i = 1950; i <= years; i++)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            year.Items.Add(L);
        }


    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if(RadioButton1.Checked)
            gender="Male";
        else if(RadioButton2.Checked)
            gender="Female";

        cmd.CommandText = string.Format("SELECT uid FROM member WHERE uid='{0}'", txtuserid.Text);
        SqlDataReader buffer = cmd.ExecuteReader();
        if (buffer.Read())
        {
            Response.Write("<script> alert('User Id already exist " + buffer.GetValue(1).ToString() + " ...') </script>");
            buffer.Close();         
        }
        else
        {
            cmd.CommandText = "member_login";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@uid", txtuserid.Text);
            cmd.Parameters.AddWithValue("@password", txtpassword.Text);
            cmd.Parameters.AddWithValue("@name", txtname.Text);
            cmd.Parameters.AddWithValue("@fname", txtfname.Text);
            cmd.Parameters.AddWithValue("@gender", gender);
            cmd.Parameters.AddWithValue("@dob", string.Concat(month.SelectedItem.Value, "/", days.SelectedItem.Value, "/", year.SelectedItem.Value));
            cmd.Parameters.AddWithValue("@bgroup", bgroup.SelectedItem.Value);
            cmd.Parameters.AddWithValue("@address1", txtaddress1.Text);
            cmd.Parameters.AddWithValue("@address2", txtaddress2.Text);
            cmd.Parameters.AddWithValue("@city", txtcity.Text);
            cmd.Parameters.AddWithValue("@district", txtdistrict.Text);
            cmd.Parameters.AddWithValue("@state", txtstate.Text);
            cmd.Parameters.AddWithValue("@pincode", txtpincode.Text);
            cmd.Parameters.AddWithValue("@email", txtemail.Text);
            cmd.Parameters.AddWithValue("@phone", txtphone.Text);
            cmd.Parameters.AddWithValue("@mobile", txtmobile.Text);
            cmd.Parameters.AddWithValue("@nominee_name", txtnomineename.Text);
            cmd.Parameters.AddWithValue("@nominee_address", txtnomineeaddress.Text);
            cmd.Parameters.AddWithValue("@nominee_relationship", txtnomineerealtionship.Text);
            cmd.Parameters.AddWithValue("@date", DateTime.Now.ToString("MM/dd/yyyy"));
            cmd.Parameters.AddWithValue("@session", "Null");
            cmd.Parameters.AddWithValue("@flag", 0);
            cmd.Parameters.AddWithValue("@status", "0");
            Label1.Text = " Record added successully";
            cmd.ExecuteNonQuery();            
        }
        con.Close();
    }

}

Please help me regarding this, since two days i hang up here only
pls pls psls

[Lusiphur]

Ok... here goes...

Your connection is established on Page_Load() but never utilized and is left open at that point.

The only point where I see the connection being closed is at the end of Button1_Click.

If your SQL connection is not needed at Page_Load() to properly manage loading of page information then don't include it in Page_Load().
Your code:

public partial class registration : System.Web.UI.UserControl
{
    string gender;
    SqlCommand cmd = null;
    SqlConnection con = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection("Server=.; database=nkm; user id=sa; pwd=karthavya");
        con.Open();
        cmd = new SqlCommand();
        cmd.Connection = con;

        int years = DateTime.Now.Year;

        int i = 1;
        while (i < 32)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            days.Items.Add(L);
            i++;
        }

        for (i = 1; i < 13; i++)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            month.Items.Add(L);

        }

        for (i = 1950; i <= years; i++)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            year.Items.Add(L);
        }


    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (RadioButton1.Checked)
            gender = "Male";
        else if (RadioButton2.Checked)
            gender = "Female";

        cmd.CommandText = string.Format("SELECT uid FROM member WHERE uid='{0}'", txtuserid.Text);
        SqlDataReader buffer = cmd.ExecuteReader();
        if (buffer.Read())
        {
            Response.Write("<script> alert('User Id already exist " + buffer.GetValue(1).ToString() + " ...') </script>");
            buffer.Close();
        }
        else
        {
            cmd.CommandText = "member_login";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@uid", txtuserid.Text);
            cmd.Parameters.AddWithValue("@password", txtpassword.Text);
            cmd.Parameters.AddWithValue("@name", txtname.Text);
            cmd.Parameters.AddWithValue("@fname", txtfname.Text);
            cmd.Parameters.AddWithValue("@gender", gender);
            cmd.Parameters.AddWithValue("@dob", string.Concat(month.SelectedItem.Value, "/", days.SelectedItem.Value, "/", year.SelectedItem.Value));
            cmd.Parameters.AddWithValue("@bgroup", bgroup.SelectedItem.Value);
            cmd.Parameters.AddWithValue("@address1", txtaddress1.Text);
            cmd.Parameters.AddWithValue("@address2", txtaddress2.Text);
            cmd.Parameters.AddWithValue("@city", txtcity.Text);
            cmd.Parameters.AddWithValue("@district", txtdistrict.Text);
            cmd.Parameters.AddWithValue("@state", txtstate.Text);
            cmd.Parameters.AddWithValue("@pincode", txtpincode.Text);
            cmd.Parameters.AddWithValue("@email", txtemail.Text);
            cmd.Parameters.AddWithValue("@phone", txtphone.Text);
            cmd.Parameters.AddWithValue("@mobile", txtmobile.Text);
            cmd.Parameters.AddWithValue("@nominee_name", txtnomineename.Text);
            cmd.Parameters.AddWithValue("@nominee_address", txtnomineeaddress.Text);
            cmd.Parameters.AddWithValue("@nominee_relationship", txtnomineerealtionship.Text);
            cmd.Parameters.AddWithValue("@date", DateTime.Now.ToString("MM/dd/yyyy"));
            cmd.Parameters.AddWithValue("@session", "Null");
            cmd.Parameters.AddWithValue("@flag", 0);
            cmd.Parameters.AddWithValue("@status", "0");
            Label1.Text = " Record added successully";
            cmd.ExecuteNonQuery();
        }
        con.Close();
    }
}

I would use something more like this (though I still have my doubts about using the same command name/construct for both select and insert functions and think they should be separated into separate commands and connections):

string gender;
    //Establish connection string above Page_Load() for use in multiple functions if needed
    SqlConnection con = new SqlConnection("Server=.; database=nkm; user id=sa; pwd=karthavya");

    protected void Page_Load(object sender, EventArgs e)
    {
        int years = DateTime.Now.Year;

        int i = 1;
        while (i < 32)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            days.Items.Add(L);
            i++;
        }

        for (i = 1; i < 13; i++)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            month.Items.Add(L);

        }

        for (i = 1950; i <= years; i++)
        {
            ListItem L = new ListItem(i.ToString(), i.ToString());
            year.Items.Add(L);
        }


    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (RadioButton1.Checked)
        {
            gender = "Male";
        }
        else if (RadioButton2.Checked)
        {
            gender = "Female";
        }
        //Create initial SELECT command
        SqlCommand cmd = new SqlCommand();
        cmd = con.CreateCommand();
        cmd.CommandText = string.Format("SELECT uid FROM member WHERE uid='{0}'", txtuserid.Text);
        //Open connection to perform SELECT command
        con.Open();
        SqlDataReader buffer = cmd.ExecuteReader();
        if (buffer.Read())
        {
            Response.Write("<script> alert('User Id already exist " + buffer.GetValue(1).ToString() + " ...') </script>");
            buffer.Close();
        }
        else
        {
            //Close initial SqlDataReader as is no longer required at this stage
            buffer.Close();
            //Create 2nd command for insert via stored procedure
            SqlCommand cmd2 = new SqlCommand();
            cmd2 = con.CreateCommand();
            cmd2.CommandText = "member_login";
            cmd2.CommandType = CommandType.StoredProcedure;
            cmd2.Parameters.AddWithValue("@uid", txtuserid.Text);
            cmd2.Parameters.AddWithValue("@password", txtpassword.Text);
            cmd2.Parameters.AddWithValue("@name", txtname.Text);
            cmd2.Parameters.AddWithValue("@fname", txtfname.Text);
            cmd2.Parameters.AddWithValue("@gender", gender);
            cmd2.Parameters.AddWithValue("@dob", string.Concat(month.SelectedItem.Value, "/", days.SelectedItem.Value, "/", year.SelectedItem.Value));
            cmd2.Parameters.AddWithValue("@bgroup", bgroup.SelectedItem.Value);
            cmd2.Parameters.AddWithValue("@address1", txtaddress1.Text);
            cmd2.Parameters.AddWithValue("@address2", txtaddress2.Text);
            cmd2.Parameters.AddWithValue("@city", txtcity.Text);
            cmd2.Parameters.AddWithValue("@district", txtdistrict.Text);
            cmd2.Parameters.AddWithValue("@state", txtstate.Text);
            cmd2.Parameters.AddWithValue("@pincode", txtpincode.Text);
            cmd2.Parameters.AddWithValue("@email", txtemail.Text);
            cmd2.Parameters.AddWithValue("@phone", txtphone.Text);
            cmd2.Parameters.AddWithValue("@mobile", txtmobile.Text);
            cmd2.Parameters.AddWithValue("@nominee_name", txtnomineename.Text);
            cmd2.Parameters.AddWithValue("@nominee_address", txtnomineeaddress.Text);
            cmd2.Parameters.AddWithValue("@nominee_relationship", txtnomineerealtionship.Text);
            cmd2.Parameters.AddWithValue("@date", DateTime.Now.ToString("MM/dd/yyyy"));
            cmd2.Parameters.AddWithValue("@session", "Null");
            cmd2.Parameters.AddWithValue("@flag", 0);
            cmd2.Parameters.AddWithValue("@status", "0");
            //return integer value to reflect number of lines affected by ExecuteNonQuery()
            int result = cmd2.ExecuteNonQuery();
            //Determine success or failure of 2nd command
            if (result == 1)
            {
                Label1.Text = " Record added successully";
            }
            else
            {
                Label1.Text = " Record add failed";
            }
        }
        //Close connection
        con.Close();
    }

I could be a little off on some of the SQL connection/data stuff because, well, honestly I have no way to debug or attempt it since it's not based off of one of my local databses ;)

My general rules when dealing with SQL connections is this:

• one connection/command per function (insert, update, select) it's easier to track down errors if you know which function is having problems as opposed to combining functions and not knowing if it's the insert or select portion giving issues. Exceptions exist when performing an insert/update and requiring the response from SQL to determine required variable information such as a new userNumber after creating a new user.
• Open() and Close() your connection within the same function (button_click, Page_Load(), etc). Leaving a connection open indefinitely can cause issues as the connection may time out before the function it is needed in occurs or the function that closes the connection may never be called upon. Also, if a function that closes the connection is called upon multiple times within the same session but does not contain a connection.Open() then you may find yourself unable to complete a new select/insert/update due to a previously closed connection.

Mark as solved if this resolves your issue and Good Luck!!

[krishnamurtrhy]

Thanks for reply
but....
still im facing d same problem
The connection was not closed. The connection's current state is open.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The connection was not closed. The connection's current state is open.

Source Error:

Line 65:
Line 66: //Open connection to perform SELECT command
Line 67: con.Open();
Line 68:
Line 69: SqlDataReader buffer = cmd.ExecuteReader();

[Lusiphur]

Ok... my bad for not being clearer on the specific relevant part of it :icon_razz:

Your connection is showing open because it is never closed unless the button is clicked.

Therefor in the event of multiple loads it is trying to open an already open connection.

Same situation happens with your reader which is never disposed of in your code.

This is why I provided example code above to indicate what would be a better overall setup for your code to prevent these things from happening. ;)

Edit: This is also why I recommended placing your connection into either a separate function or into the click event so that the connection is only called as needed and not opened in the page_load() where it can attempt to re-open on postback or page reload without having closed first.