DataReader Associated with this Command Error

Question

    OleDbConnection baglanti = 
        new OleDbConnection("connect");
    OleDbCommand komut = new OleDbCommand();
    OleDbDataReader dr;
    DataSet ds = new DataSet();
    OleDbDataAdapter da = new OleDbDataAdapter();
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        if(textBox1.Text=="" || textBox2.Text=="" || textBox3.Text=="" || maskedTextBox1.Text=="" || dateTimePicker1.Text=="" || maskedTextBox2.Text=="" || textBox6.Text=="")
        {
            MessageBox.Show("Lütfen Boş alanları doldurunuz !!");
        }
        else
        {
            baglanti.Open();
            komut.Connection = baglanti;
            komut.CommandText=("Select TcKimlik from Hasta");
            dr = komut.ExecuteReader();
            while(dr.Read())
            {
                if(dr["TcKimlik"].ToString()==textBox1.Text.ToString())
                {
                    MessageBox.Show("aaaaa");
                }
                else
                {
                    komut.CommandText = ("insert into Hasta (TcKimlik,h_adı,h_soyadı,tel_no,ran_tar,ran_saati,sikayet_nedeni) values('" + textBox1.Text.ToString() + "','" + textBox2.Text.ToString() + "','" + textBox3.Text.ToString() + "','" + maskedTextBox1.Text.ToString() + "','" + dateTimePicker1.Text.ToString() + "','" + maskedTextBox2.Text.ToString() + "','" + textBox6.Text.ToString() + "')");
                    komut.ExecuteNonQuery();
                    MessageBox.Show("Kayıt Başarı İle Tamamlandı", "Kayıt Başarılı", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);


                }

            }
            baglanti.Close();
            textBox1.Clear();
            textBox2.Clear();
            textBox3.Clear();
            maskedTextBox1.Clear();
            maskedTextBox2.Clear();
            textBox6.Clear();


        }
    }

I have tried a lot of ways but I could not go to a solution. If I check the data or make dr.Close() before registering the data, I can save the data, but then I get an error because I close the data reader prematurely. I want to check the data in the database but I get an error like below.

Associated with this Command, there is already an explicit DataReader that should be closed first.

You should use different command object then `komut` to perform insert operation. — Chetan, Mar 17 '18 at 15:57
Not just the command, but also a new connection object. You cannot use the current connection to do a different task when it is open to serve an OleDbDataReader — Steve, Mar 17 '18 at 16:02
Please use parameters [because...](https://stackoverflow.com/questions/601300/what-is-sql-injection) — Crowcoder, Mar 17 '18 at 16:06
Notice also that you should never concatenate user inputs to a string forming an Sql Command text like you do in the Insert statement. This is well know to be the attack vector for the Sql Injection and what if your users types in any of your textboxes a single quote? — Steve, Mar 17 '18 at 16:06
Also, why are you inserting the data within the retrieving loop? You can store the retrieved data in a list/dict and then save the data. — SelvaS, Mar 17 '18 at 16:07

Pranay Rana · Answer 1 · 2018-03-17T18:24:39.663

Issue with you code is you are making use of Command object for performing reading and inserting record at one time.

you require to perform operation one by one , you cannot do both in one time. so you code should be like this

using (OleDbconnection connection = new OleDbconnection (ConnectionString))
{
   connection.Open();
   OleDbCommand select = new OleDbCommand("..selecte query",connection);
   OleDbDataReader reader = select.ExecuteReader();

   if (reader.HasRows)
   {
      while (reader.Read())
      {
         //read data you want 

        //you might not need another connection , but i added to seperate it , you can reuse connection you created and perfrom update 
        using(OleDbconnection connection1 = new OleDbconnection (ConnectionString))
           {
               OleDbCommand insert= new OleDbCommand ("..insertquery", connection1);
            insert.ExecuteNonQuery();
           }
         }
      }

      reader.Close();
   }
}

you can use command object to perfrom multiple task but it should be one by one , example

//first complete reading and close reader 
//second do insert operation complete it 
//third do update operation

One more suggstion make use of Parameter as your insert query is open for sql injection attack, if you go with parameterise query can resolve that issue

DataReader Associated with this Command Error

1 Answers1