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

Question

krishnamurtrhy 0 Newbie Poster

16 Years Ago

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

.net microsoft:sql-server session

Edited 13 Years Ago by mike_2000_17 because: Fixed formatting

2 Contributors
3 Replies
495 Views
22 Hours Discussion Span
Latest Post 16 Years Ago Latest Post by Lusiphur

Dani AI

Generated 9 Months Ago

Brief summary and focused fixes for the error shown by (and the advice from ): the exception means a reader/command/connection is being reused or left open. The safest, most reliable pattern is to open the connection only for the short block that needs it, dispose readers/commands with using blocks, and avoid reusing a single SqlCommand instance for both the check and the insert. Also avoid building SQL with string interpolation for the existence check — use parameters.

Example pattern (illustrates the minimal changes needed: short-lived connection, parameterized existence check, then a separate command for the stored procedure):

string connString = "...";

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        // populate day/month/year lists once
    }
}

protected void Button1_Click(object sender, EventArgs e)
{
    string uid = txtuserid.Text.Trim();

    using (var con = new SqlConnection(connString))
    {
        using (var check = new SqlCommand("SELECT COUNT(1) FROM member WHERE uid = @uid", con))
        {
            check.Parameters.AddWithValue("@uid", uid);
            con.Open();
            int exists = (int)check.ExecuteScalar();
            if (exists > 0)
            {
                // notify: uid already exists
                return;
            }
        }

        using (var insert = new SqlCommand("member_login", con))
        {
            insert.CommandType = CommandType.StoredProcedure;
            insert.Parameters.AddWithValue("@uid", uid);
            // add other parameters here (do not re-use the previous command or its parameters)
            insert.ExecuteNonQuery();
        }
    }
}

Key troubleshooting notes:

A DataReader left open (or reusing the same SqlCommand with leftover parameters) causes the original exception; using blocks eliminate that class of bugs.
For a simple “exists?” test prefer ExecuteScalar or COUNT(*) instead of reading a DataReader and then reusing the same command.
Calling buffer.GetValue(1) after SELECT uid is incorrect (single column is index 0).
If a connection must be long-lived, always check Connection.State before Open and always Close/Dispose in a finally block — but short-lived connections are simpler and safer.
MARS is not a substitute for proper disposal; enable it only when multiple concurrent result sets are truly required.

These changes resolve the concurrent reader/connection issues and remove the need to juggle manual Open/Close across Page_Load and event handlers.

All 3 Replies

Lusiphur 185 Practically a Posting Shark

16 Years Ago

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!!

Edited 16 Years Ago by Lusiphur because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

krishnamurtrhy 0 Newbie Poster · Answer 1 · 2010-07-03T11:41:30+00:00

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 185 Practically a Posting Shark Team Colleague Featured Poster · Answer 2 · 2010-07-03T11:50:58+00:00

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.

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

Dani AI

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers