There is already an open Datareader associated in C#

Question

I can't seem to find the reason behind this error. Can someone look into my codes?

private void button2_Click_1(object sender, EventArgs e)
{
    con.Open();

    string check
        = "Select block, section, size from interment_location where block='"
          + textBox12.Text
          + "' and section='"
          + textBox13.Text
          + "' and size='"
          + textBox14.Text
          + "'";

    SqlCommand cmd1 = new SqlCommand(check, con);
    SqlDataReader rdr;
    rdr = cmd1.ExecuteReader();

    try
    {
        if (textBox1.Text == ""
            || textBox2.Text == ""
            || textBox3.Text == ""
            || textBox4.Text == ""
            || textBox7.Text == ""
            || textBox8.Text == ""
            || textBox9.Text == ""
            || textBox10.Text == ""
            || dateTimePicker1.Value.ToString("yyyyMMdd HH:mm:ss") == ""
            || dateTimePicker2.Value.ToString("yyyyMMdd HH:mm:ss") == ""
            || textBox11.Text == ""
            || dateTimePicker3.Value.ToString("yyyyMMdd HH:mm:ss") == ""
            || textBox12.Text == ""
            || textBox13.Text == ""
            || textBox14.Text == "")
        {
            MessageBox.Show(
                "Please input a value!",
                "Error",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation);
        }
        else if (rdr.HasRows == true)
        {
            MessageBox.Show("Interment Location is already reserved.");
            textBox12.Clear();
            textBox13.Clear();
            textBox14.Clear();
            textBox12.Focus();
        }
        else if (MessageBox.Show(
            "Are you sure you want to reserve this record?",
            "Reserve",
            MessageBoxButtons.YesNo,
            MessageBoxIcon.Question) == DialogResult.Yes)
        {
            SqlCommand cmd4 = new SqlCommand(
                "insert into owner_info(ownerf_name, ownerm_name,"
                    + " ownerl_name, home_address, tel_no, office)"
                    + " values('"
                    + textBox1.Text
                    + "', '"
                    + textBox2.Text
                    + "', '"
                    + textBox3.Text
                    + "', '"
                    + textBox4.Text
                    + "', '"
                    + textBox7.Text
                    + "', '"
                    + textBox8.Text
                    + "')",
                con);

            cmd.ExecuteNonQuery();

            SqlCommand cmd5 = new SqlCommand(
                "insert into deceased_info(deceased_id, name_of_deceased, "
                    + "address, do_birth, do_death, place_of_death, "
                    + "date_of_interment, COD_id, place_of_vigil_id, "
                    + "service_id, owner_id) values('"
                    + ownerid
                    + "','"
                    + textBox9.Text
                    + "', '"
                    + textBox10.Text
                    + "', '"
                    + dateTimePicker1.Value.ToString("yyyyMMdd HH:mm:ss")
                    + "', '"
                    + dateTimePicker2.Value.ToString("yyyyMMdd HH:mm:ss")
                    + "', '"
                    + textBox11.Text
                    + "', '"
                    + dateTimePicker3.Value.ToString("yyyyMMdd HH:mm:ss")
                    + "', '"
                    + ownerid
                    + "', '"
                    + ownerid
                    + "', '"
                    + ownerid
                    + "', '"
                    + ownerid
                    + "')",
                con); 

            cmd.ExecuteNonQuery();

            SqlCommand cmd6 = new SqlCommand(
                "insert into interment_location(lot_no, block, section, "
                    + "size, garden_id) values('"
                    + ownerid
                    + "','"
                    + textBox12.Text
                    + "', '"
                    + textBox13.Text
                    + "', '"
                    + textBox14.Text
                    + "', '"
                    + ownerid
                    + "')",
                con);

            cmd.ExecuteNonQuery();

            MessageBox.Show(
                "Your reservation has been made!",
                "Reserve",
                MessageBoxButtons.OK,
                MessageBoxIcon.Information); 
        }
    }
    catch (Exception x)
    {
        MessageBox.Show(x.Message);
    }

    con.Close();    
}

This is the only code that I edited after.

http://stackoverflow.com/questions/2519379/close-reader-before-closing-connection Please refer link — rach, Oct 18 '13 at 15:28
ive already closed the method. did i place it on the wrong place? — Celine, Oct 18 '13 at 15:34
Please see the following answer: http://stackoverflow.com/a/4472414/2424 It shows the **best practice** way of using connection, command, reader and parameter objects. As your code currently stands, it is likely to leak memory, sql connections and allow sql injection attacks. — NotMe, Oct 18 '13 at 15:51
possible duplicate of [Exception: There is already an open DataReader associated with this Connection which must be closed first](http://stackoverflow.com/questions/5440168/exception-there-is-already-an-open-datareader-associated-with-this-connection-w) — BartoszKP, Aug 02 '14 at 11:14

Tarun · Answer 1 · 2013-10-18T17:08:27.050

private void button2_Click_1(object sender, EventArgs e)
{
  con.Open();

    string check
    = "Select block, section, size from interment_location where block='"
      + textBox12.Text
      + "' and section='"
      + textBox13.Text
      + "' and size='"
      + textBox14.Text
      + "'";

SqlCommand cmd1 = new SqlCommand(check, con);
SqlDataReader rdr;
rdr = cmd1.ExecuteReader();

try
{
    if (textBox1.Text == ""
        || textBox2.Text == ""
        || textBox3.Text == ""
        || textBox4.Text == ""
        || textBox7.Text == ""
        || textBox8.Text == ""
        || textBox9.Text == ""
        || textBox10.Text == ""
        || dateTimePicker1.Value.ToString("yyyyMMdd HH:mm:ss") == ""
        || dateTimePicker2.Value.ToString("yyyyMMdd HH:mm:ss") == ""
        || textBox11.Text == ""
        || dateTimePicker3.Value.ToString("yyyyMMdd HH:mm:ss") == ""
        || textBox12.Text == ""
        || textBox13.Text == ""
        || textBox14.Text == "")
    {
        MessageBox.Show(
            "Please input a value!",
            "Error",
            MessageBoxButtons.OK,
            MessageBoxIcon.Exclamation);
    }
    else if (rdr.HasRows == true)
    {
        MessageBox.Show("Interment Location is already reserved.");
        textBox12.Clear();
        textBox13.Clear();
        textBox14.Clear();
        textBox12.Focus();
    }
    else if (MessageBox.Show(
        "Are you sure you want to reserve this record?",
        "Reserve",
        MessageBoxButtons.YesNo,
        MessageBoxIcon.Question) == DialogResult.Yes)
    {
        if (!rdr.IsClosed) 
                rdr.Close(); //close the data reader
        SqlCommand cmd4 = new SqlCommand(
            "insert into owner_info(ownerf_name, ownerm_name,"
                + " ownerl_name, home_address, tel_no, office)"
                + " values('"
                + textBox1.Text
                + "', '"
                + textBox2.Text
                + "', '"
                + textBox3.Text
                + "', '"
                + textBox4.Text
                + "', '"
                + textBox7.Text
                + "', '"
                + textBox8.Text
                + "')",
            con);

        cmd.ExecuteNonQuery();

        SqlCommand cmd5 = new SqlCommand(
            "insert into deceased_info(deceased_id, name_of_deceased, "
                + "address, do_birth, do_death, place_of_death, "
                + "date_of_interment, COD_id, place_of_vigil_id, "
                + "service_id, owner_id) values('"
                + ownerid
                + "','"
                + textBox9.Text
                + "', '"
                + textBox10.Text
                + "', '"
                + dateTimePicker1.Value.ToString("yyyyMMdd HH:mm:ss")
                + "', '"
                + dateTimePicker2.Value.ToString("yyyyMMdd HH:mm:ss")
                + "', '"
                + textBox11.Text
                + "', '"
                + dateTimePicker3.Value.ToString("yyyyMMdd HH:mm:ss")
                + "', '"
                + ownerid
                + "', '"
                + ownerid
                + "', '"
                + ownerid
                + "', '"
                + ownerid
                + "')",
            con); 

        cmd.ExecuteNonQuery();

        SqlCommand cmd6 = new SqlCommand(
            "insert into interment_location(lot_no, block, section, "
                + "size, garden_id) values('"
                + ownerid
                + "','"
                + textBox12.Text
                + "', '"
                + textBox13.Text
                + "', '"
                + textBox14.Text
                + "', '"
                + ownerid
                + "')",
            con);

        cmd.ExecuteNonQuery();

        MessageBox.Show(
            "Your reservation has been made!",
            "Reserve",
            MessageBoxButtons.OK,
            MessageBoxIcon.Information); 
    }
}
catch (Exception x)
{
    MessageBox.Show(x.Message);
}
if (!rdr.IsClosed)
    rdr.Close(); //close the datareader if it is not closed already
con.Close();

}

I've tried that asweel but I keep on getting "Object reference not set to an instance of an object. — Celine, Oct 18 '13 at 15:57
Ok just got the solution for your error "There is already an open Datareader associated....." First you assigned the connection to a command which opens a DataReader, and later you use the same connection for inserting the record. solution: 1. close the datareader by dr.Close() before inserting record. 2. change the connection object for insert command. — Tarun, Oct 18 '13 at 16:22

score 0 · Answer 2 · answered Oct 18 '13 at 16:36

Try the following code and see if that works for you.

 else if (rdr.HasRows == true)
    {
        MessageBox.Show("Interment Location is already reserved.");
        textBox12.Clear();
        textBox13.Clear();
        textBox14.Clear();
        textBox12.Focus();
    }
rdr.close() // close your reader here!

There is already an open Datareader associated in C#

2 Answers2