Click here to Skip to main content
15,790,917 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
When iam executing the below code getting the error there is already an open datareader associated with this command which must be closed. error help me out
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class Finaldestination : System.Web.UI.Page
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)

    protected void Button1_Click(object sender, EventArgs e)
        string strSelect = "SELECT * FROM tblregistration  WHERE UserId = '" + TextBox2.Text + "' ";
        SqlCommand cmdSelect = new SqlCommand(strSelect,con);
        cmdSelect.Parameters.AddWithValue("@UserId", TextBox2.Text);

        cmdSelect.CommandText = strSelect;
        cmdSelect.Connection = con;
        SqlDataReader reader;
        reader = cmdSelect.ExecuteReader();
            if ( reader.HasRows == true)
                Label1.Text = "Username exists please try other one";

                string insert = "insert into tblregistration(Name,UserId,Email,Password,Confirmpassword) values (@Name,@UserId,@Email,@Password,@Confirmpassword) ";
                SqlCommand cmd = new SqlCommand(insert, con);
                cmd.Parameters.AddWithValue("@Name", TextBox1.Text);
                cmd.Parameters.AddWithValue("@UserId", TextBox2.Text);
                cmd.Parameters.AddWithValue("@Email", TextBox3.Text);
                cmd.Parameters.AddWithValue("@Password", TextBox4.Text);
                cmd.Parameters.AddWithValue("@Confirmpassword", TextBox5.Text);
                Label1.Text = "data saved";
Updated 10-Nov-13 22:54pm

The clue is in the error message:
there is already an open datareader associated with this command which must be closed.

You create a reader, and do not try to dispose of it before you reuse the same connection for the insert command. This is not allowed:[^]
"You should always call the Close method when you have finished using the DataReader object.
If your Command contains output parameters or return values, they will not be available until the DataReader is closed.
Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed."

So at the top of your else condition, add this:
Share this answer
raxhemanth 11-Nov-13 4:40am    
OriginalGriff 11-Nov-13 4:58am    
You're welcome!
Member 14572439 29-Aug-19 12:57pm    
thank you.
OriginalGriff 29-Aug-19 13:59pm    
You're welcome as well - six years later! :laugh:
You have to close SqlDataReader when you finished using the it.When SqlDataReader is open, the Connection is in use by it.Hence you cannot execute any commands for that particular connection.

Use reader.Close(); just inside the else block in your code.

Reference :[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900