3

My variable names, database tables are right, I get error like this;

MySql.Data.MySqlClient.MySqlException: 'There is already an open DataReader associated with this Connection which must be closed first.'

public string urun_adi_bul(string urun_id)// find product name 
{
     if (genel.baglanti.State == ConnectionState.Closed)
     {
         genel.baglanti.Open();
     }
     string urun_adi = "";
     genel.sqlgonder.Connection = genel.baglanti;
     genel.sqlgonder.CommandText = "Select * from urunler where urun_id="+urun_id;
     MySqlDataReader oku = genel.sqlgonder.ExecuteReader();
     while (oku.Read())
     {
         urun_adi = oku["urun_id"].ToString();
     }
     oku.Close();
     return urun_adi;// product name
}

public void hesapGetir(ListView lvSiparis, string masa_id)
{
        genel.baglanti.Open();
        MySqlCommand sqlgonder = new MySqlCommand();
        sqlgonder.Connection = genel.baglanti;
        sqlgonder.CommandText = "Select * from adisyonlar where masa_id = "+masa_id;
        MySqlDataReader oku = sqlgonder.ExecuteReader();
        lvSiparis.Items.Clear();
        string urun_adi = "",urun_id="";
        while (oku.Read())
        {
            urun_id = oku["urun_id"].ToString();
            decimal fiyat = Convert.ToDecimal(urun_fiyati_bul(urun_id)); // price
            decimal adet = Convert.ToDecimal(oku["urun_adet"]); // piece
            decimal toplam = fiyat * adet; // total
            urun_adi = urun_adi_bul(urun_id);

            ListViewItem item = new ListViewItem(urun_adi);
            item.SubItems.Add(adet.ToString());
            item.SubItems.Add(toplam.ToString());
            lvSiparis.Items.Add(item);
        }
        oku.Close();
        genel.baglanti.Close();
}

How can I use 2 datareaders in a loop?

Zoe
  • 27,060
  • 21
  • 118
  • 148
Birol çağlar
  • 31
  • 5
  • 2
    To avoid SQL injection, you should use Parameterized Queries – S.Dav Apr 03 '18 at 14:40
  • @S.Dav it depends if the input is user provided. As a general rule is a good advice, not only for security reasons but also for performance reasons (execution plans). – Cleptus Apr 03 '18 at 14:42
  • What line you get the error? – Juan Carlos Oropeza Apr 03 '18 at 14:44
  • @bradbury9 **always** use parameterized queries. You never know when your Data library will be called with use provided data – maccettura Apr 03 '18 at 14:45
  • decimal fiyat = Convert.ToDecimal(urun_fiyati_bul(urun_id)); // price in this line @JuanCarlosOropeza – Birol çağlar Apr 03 '18 at 14:46
  • You didnt provide the code to `urun_fiyati_bul` function. You should join your data to do a single query tthat retrieves al the data you want, check René's answer for a example on how to join. – Cleptus Apr 03 '18 at 14:55

1 Answers1

4

You can have only one open reader (or any kind of query) per connection. But you actually only need one reader if you combine the two queries with a JOIN:

SELECT adisyonlar.urun_id, urun_adet, urunler.urun_id
FROM adisyonlar
LEFT JOIN urunler
ON adisyonlar.urun_id = urunler.urun_id

This way you let the db do the work to find the matching records, and you only need one query instead of asking the db again and again for every row in adisyonlar.

Note that it's better practice to explicity name the columns you want to fetch instead of fetching all columns with SELECT *.

Plus: your queries are open to SQL injection! Please use parameterized queries.

René Vogt
  • 43,056
  • 14
  • 77
  • 99