CODEWITHSUNDEEP
c#asp.netsql-server
umrktk
umrktk

Reputation: 5

A {"There is already an open DataReader associated with this Command which must be closed first."}

Basically when I run this code, I get the following error and I don't know how to resolve it. I get this error when I click on button then I am redirected back to Visual Studio with the following error. Please help me solve this error, help is appreciated.

There is already an open DataReader associated with this command which must be closed first.

public partial class forgot : System.Web.UI.Page
{
    protected void resetpass_Click(object sender, EventArgs e)
    {
        SqlDataReader reader = null;

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
        con.Open();

        SqlCommand cmd = new SqlCommand("select * from reg where Username =@username", con);
        cmd.Parameters.AddWithValue("@username", username.Text);
        cmd.Parameters.AddWithValue("@security1", sec1.Text);
        cmd.Parameters.AddWithValue("@security2", sec2.Text);

        reader = cmd.ExecuteReader();

        if (reader != null && reader.HasRows)
        {
            newpass.Visible = true;
            confpass.Visible = true;
            Label1.Text = "New Password";
            Label2.Text = "Confirm New Password";

            SqlDataAdapter updates = new SqlDataAdapter("update reg set Password='" + newpass.Text + "'", con);

            DataSet ds = new DataSet();
            updates.Fill(ds);

            ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('The Password has been Changed')</script>");
            con.Close();
        }
    }
}

Upvotes: 0

Views: 254

Answers (3)

Saif
Saif

Reputation: 2679

the error said that you have opened DataReader and you opened another SqlDataAdapter before closing DataReader. So in .Net you need to close the first command in order to open a new command.

change this:

 if (reader != null && reader.HasRows)
        {
            newpass.Visible = true;
            confpass.Visible = true;
            Label1.Text = "New Password";
            Label2.Text = "Confirm New Password";
            SqlDataAdapter updates = new SqlDataAdapter("update reg set Password='" + newpass.Text + "'", con);
            DataSet ds = new DataSet();
            updates.Fill(ds);
            ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('The Password has been Changed')</script>");
            con.Close();
        }

to 

 if (reader != null && reader.HasRows)
        {
            reader.Close();
            newpass.Visible = true;
            confpass.Visible = true;
            Label1.Text = "New Password";
            Label2.Text = "Confirm New Password";
            SqlDataAdapter updates = new SqlDataAdapter("update reg set Password='" + newpass.Text + "'", con);
            DataSet ds = new DataSet();
            updates.Fill(ds);
            ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('The Password has been Changed')</script>");
            con.Close();
        }

Upvotes: 1

John Wu
John Wu

Reputation: 52210

At the time you attempt to run Fill, the connection con is already busy trying to retrieve data to keep the SqlDataReader happy. A database connection can only handle one resultset at a time.

You have two options:

  1. Initialize updates with a second database connection instance, so you will have two open at the same time.
  2. Use reader to get the complete resultset first, storing it in an array or DataTable. Then, when you are done with the reader, iterate through the array/table and perform the updates to reg.

Upvotes: 0

CodingYoshi
CodingYoshi

Reputation: 26989

You are getting that error because you have nested data readers. Here is your code, simplified, to show what you are doing:

protected void resetpass_Click(object sender, EventArgs e) {
   // Here is data reader
   SqlDataReader reader = null;
   // ...
   reader = cmd.ExecuteReader();
   if( reader != null && reader.HasRows ) {
      // ...
      // and here is another one within the above data reader
      SqlDataAdapter updates = new SqlDataAdapter( "update reg set Password='" + newpass.Text + "'", con );
   }
}

To do that, you need to enable MARS. You can do so in your connection string:

Server=.\SQLEXPRESS;Database=master;Integrated Security=SSPI;
  MultipleActiveResultSets=true;

Of course you that is just an example, you will need to use your own connection string.

Upvotes: 1

Related Questions