C# DataReader error

Question

I looked for a solution to this problem on the forum, but I didn't find one for my problem. On button click, I receive error:

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

So, I tried to close all DataReaders after using them, I tried to use CommandBehavior, but none of them worked. I tried to use using(MysqlCommand...) but didn't work. What can I do? The strangest thing is that the code is working, but after each button press, I receive that error again. Any ideas?

Please don't flag question as a duplicate, I know that the question exist here but the answer is missing for my problem I guess.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Drawing.Text;

namespace simulator
{
    public partial class Simulare : Form
    {
        public int corect = 0, incorect = 0;
        Timer timer;
        static string dataA = "SELECT DISTINCT * FROM questions order by rand() limit 1";
        public int r1;
        public int r2;
        public int r3;
        public Simulare()
        {
            InitializeComponent();
            this.FormClosing += Form1_FormClosing;
            label1.Text = TimeSpan.FromMinutes(30).ToString("mm\\:ss");
            label10.Text = corect.ToString();
            label12.Text = incorect.ToString();
            //FormBorderStyle = FormBorderStyle.None;
            WindowState = FormWindowState.Maximized;
        }
        private void simulare_Load(object sender, EventArgs e)
        {
            var startTime = DateTime.Now;
            timer = new Timer() { Interval = 1000 };
            timer.Tick += (obj, args) =>
            {
                TimeSpan ts = TimeSpan.FromMinutes(30) - (DateTime.Now - startTime);
                label1.Text = ts.ToString("mm\\:ss");
                if (ts.Minutes == 00 && ts.Seconds == 00)
                {
                    timer.Stop();
                    MessageBox.Show("Timpul a expirat. Ai picat!");
                    MySqlCommand upd = new MySqlCommand("select totalno from accounts where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection());
                        try
                        {
                            MySqlDataReader upad = upd.ExecuteReader();
                                while (upad.Read())
                                {
                                    int totalnu = (int)upad["totalno"];
                                    totalnu++;
                                    using (MySqlCommand update = new MySqlCommand("UPDATE accounts SET totalno=@totalnu where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection()))
                                    {
                                        update.Parameters.AddWithValue("@totalnu", totalnu);
                                        int rows = update.ExecuteNonQuery();
                                    }
                                }
                                upad.Close();
                        }
                        catch (Exception ex2)
                        {
                            MessageBox.Show(ex2.Message);
                        }
                    }
            };
            timer.Start();
            select();
        }

        private void select()
        {
            using (ConnConfig.getConnection())
            {
                MySqlCommand cmd = new MySqlCommand(dataA, ConnConfig.getConnection());
                cmd.CommandType = CommandType.Text;
                MySqlDataReader rdra = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                try
                {
                    while (rdra.Read())
                    {
                        label2.Text = rdra["question"].ToString();
                        label3.Text = rdra["answer1"].ToString();
                        label4.Text = rdra["answer2"].ToString();
                        label5.Text = rdra["answer3"].ToString();
                        r1 = (int)rdra["option1"];
                        r2 = (int)rdra["option2"];
                        r3 = (int)rdra["option3"];
                    }
                    rdra.Close();
                }
                catch (InvalidOperationException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    ConnConfig.closeConn();
                }
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            int result1 = checkBox1.CheckState == CheckState.Checked ? 1 : 0;
            int result2 = checkBox2.CheckState == CheckState.Checked ? 1 : 0;
            int result3 = checkBox3.CheckState == CheckState.Checked ? 1 : 0;
            using(ConnConfig.getConnection())
            {
                MySqlCommand cmd = new MySqlCommand(dataA, ConnConfig.getConnection());
                cmd.CommandType = CommandType.Text;
                MySqlDataReader rdr = cmd.ExecuteReader();
                try
                {
                    while (rdr.Read())
                    {
                        if (checkBox1.Checked == false && checkBox2.Checked == false && checkBox3.Checked == false)
                        {
                            MessageBox.Show("Bifati minim o casuta!");
                            return;
                        }
                        else
                        {
                            if ((result1 == r1) && (result2 == r2) && (result3 == r3))
                            {
                                corect++;
                                label10.Text = corect.ToString();
                                checkBox1.Checked = false;
                                checkBox2.Checked = false;
                                checkBox3.Checked = false;
                                select();
                            }
                            else
                            {
                                incorect++;
                                label12.Text = incorect.ToString();
                                checkBox1.Checked = false;
                                checkBox2.Checked = false;
                                checkBox3.Checked = false;
                                select();
                            }
                            if (corect + incorect == 26)
                            {
                                int totalalll;
                                timer.Stop();
                                button1.Enabled = false;
                                MySqlCommand upd = new MySqlCommand("select * from accounts where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection());
                                MySqlDataReader upad = upd.ExecuteReader();
                                while (upad.Read())
                                {
                                    totalalll = (int)upad["totalall"];
                                    totalalll++;
                                    using (MySqlCommand update = new MySqlCommand("UPDATE accounts SET totalall=@totalalll where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection()))
                                    {
                                        update.Parameters.AddWithValue("@totalalll", totalalll);
                                        Int32 rows = update.ExecuteNonQuery();
                                    }
                                }
                                upad.Close();
                            }
                            if (corect == 26)
                            {
                                MySqlCommand upd = new MySqlCommand("select totalyes from accounts where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection());
                                MySqlDataReader upad = upd.ExecuteReader();
                                while (upad.Read())
                                {
                                    int totalda = (Int32)upad["totalyes"];
                                    totalda++;
                                    using (MySqlCommand update = new MySqlCommand("UPDATE accounts SET totalyes=@totalda where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection()))
                                    {
                                        update.Parameters.AddWithValue("@totalda", totalda);
                                        int rows = update.ExecuteNonQuery();
                                    }
                                }
                                upad.Close();
                                MessageBox.Show("Bravos");
                            }
                            else
                            {
                                MySqlCommand upd = new MySqlCommand("select totalno from accounts where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection());
                                MySqlDataReader upad = upd.ExecuteReader();
                                while (upad.Read())
                                {
                                    int totalnu = (int)upad["totalno"];
                                    totalnu++;
                                    using (MySqlCommand update = new MySqlCommand("UPDATE accounts SET totalno=@totalnu where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection()))
                                    {
                                        update.Parameters.AddWithValue("@totalnu", totalnu);
                                        int rows = update.ExecuteNonQuery();
                                    }
                                }
                                upad.Close();
                                MessageBox.Show("Mai invata!");
                            }
                        }
                    }
                    rdr.Close();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    ConnConfig.closeConn();
                }
            }
        }

        private void Form1_FormClosing(Object sender, FormClosingEventArgs e)
        {
            if (e.CloseReason == CloseReason.WindowsShutDown) return;

            if (this.DialogResult == DialogResult.Cancel)
            {
                e.Cancel = false;
                timer.Stop();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

class ConnConfig
    {
        private static string conn = "string connection";
        public static MySqlConnection connect;

        private ConnConfig()
        {

        }
        public static MySqlConnection getConnection()
        {
            if(connect !=null){

                return connect;
            }
            else 
                try{
                    connect = new MySqlConnection(conn);
                    connect.Open();
                    return connect;
                }
            catch(MySqlException e){
                throw new Exception("Cannot connect",e);
            }
        }
        public static void closeConn()
        {
            connect.Close();
        }
        public static void openConn()
        {
            connect.Open();
        }
    }

The guy who just '-1', if you're that smart, can you give me a solution? — dpaul1994, Mar 15 '15 at 21:18
After is checking if the answer is right or wrong, you can see that I called 'select()' function to select another question. And when is in the function, is jumping to these lines: 'TimeSpan ts = TimeSpan.FromMinutes(30) - (DateTime.Now - startTime); label1.Text = ts.ToString("mm\\:ss"); if (ts.Minutes == 00 && ts.Seconds == 00)' and It keeps repeating them..I tried now with break point — dpaul1994, Mar 15 '15 at 21:23
@DiacuPaul have you set break points.. are you sure that the timer is not the issue..? — MethodMan, Mar 15 '15 at 21:24
I know, don't get me wrong, but in my opinion if you disagree someone, you tell him why, right?.. — dpaul1994, Mar 15 '15 at 21:25
Probably because you used `ExecuteNonQuery` while reader was still open. Which isn't allowed. — bokibeg, Mar 15 '15 at 21:26
Should I try to update that field as I did in the rest of the code? — dpaul1994, Mar 15 '15 at 21:27
Aaaa...actually, I used ExecuteNonQuery everywhere for update.. — dpaul1994, Mar 15 '15 at 21:28
I see...well can you give me an example of how should I do? I don't really know how to work with timers..I need a countdown timer only with minutes and seconds..I have to modify that line where I used update,right? — dpaul1994, Mar 15 '15 at 21:32
ExecuteNonQuery is good for update, Inserts and Deletes but you are having a connection pooling issue it looks like.. why now create stored procedure to do you updates I think it will make debugging and maintaining much easier when doing Updates, Deletes, Inserts ..The Select query could also share the same stored procedure if you set it up properly — MethodMan, Mar 15 '15 at 21:33

score 1 · Answer 1 · answered Mar 15 '15 at 21:32

1

The root cause of your exception is that you are executing other queries while you are still iterating over the results of an earlier query. Bottom line you should not nest queries like you do if you use the same connection for the nested queries.

answered Mar 15 '15 at 21:32

Philip Stuyck

6,843
3
23
36

Abdul Saleem · Answer 2 · 2015-03-15T21:47:13.273

1

You are using reader to fetch data from SQLCommand upd.

Then you are reading value.

After that you are using another SqlCommand 'update' to update the result..

Even when you use two different SQLCommands, you are using the same connection. Thats the problem. Use a sperate connection for the second SQLCommand and your problem will be solved.

Try this.

after the line

MessageBox.Show("Timpul a expirat. Ai picat!");

add like

MessageBox.Show("Timpul a expirat. Ai picat!");
MySqlConnection conn1 = ConnConfig.getConnection();
MySqlConnection conn2 = new MySqlConnection();
conn2.ConnectionString = conn1.ConnectionString;
conn2.Open();

and then in the line

MySqlCommand upd = new MySqlCommand("select totalno from accounts where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection());

change like

MySqlCommand upd = new MySqlCommand("select totalno from accounts where username = '" + Index.textBox1.Text + "'", conn1);

and in line

using (MySqlCommand update = new MySqlCommand("UPDATE accounts SET totalno=@totalnu where username = '" + Index.textBox1.Text + "'", ConnConfig.getConnection()))

change like

using (MySqlCommand update = new MySqlCommand("UPDATE accounts SET totalno=@totalnu where username = '" + Index.textBox1.Text + "'", conn2))

edited Mar 15 '15 at 21:47

answered Mar 15 '15 at 21:32

Abdul Saleem

7,999
4
35
37

I understand, but in my situation. where should I close and re-open the connection? Because I have a MysqlCommand in another MysqlCommand – dpaul1994 Mar 15 '15 at 21:40
But I don't understand one thing..that if where are these lines, should be called only when timer is = 0.. – dpaul1994 Mar 15 '15 at 21:54
Can you put the ConnConfig class? – Abdul Saleem Mar 15 '15 at 21:54
1

Hello sir. You are showing your password and login details to the public edit it as -------------- or xxxxxxxxxxxx – Abdul Saleem Mar 15 '15 at 21:59
It is free anyway, nothing important – dpaul1994 Mar 15 '15 at 22:00
I checked it with the problem before and after the solution i provided. And found it was the excact solution. The same error i got before applying the solution i provided and error gone after the solution. Now i'm gonna check with the timer. In the mean time will you please check Cleaning the solution, and also running it with release option other than debug..? – Abdul Saleem Mar 15 '15 at 22:10
The error happens on the load event right? Or after you click any button? – Abdul Saleem Mar 15 '15 at 22:13
It happens after I click button1. Is checking if the solution is right or wrong and then is trying to select another question with 'select()' but when is in function, is jumping to 'Load' function and it stucks at those 3 lines: 'TimeSpan ts = TimeSpan.FromMinutes(30) - (DateTime.Now - startTime); label1.Text = ts.ToString("mm\\:ss"); if (ts.Minutes == 00 && ts.Seconds == 00)'. Is not checking if the timer is 0 – dpaul1994 Mar 15 '15 at 22:15
Error persists after Cleaning and rebuild – dpaul1994 Mar 15 '15 at 22:16
I'll post as another answer – Abdul Saleem Mar 15 '15 at 22:19

Abdul Saleem · Accepted Answer · 2015-03-15T22:31:20.620

1

Change the getConnection function

public static MySqlConnection getConnection()
    {
        MySqlConnection connect = null;
        try
        {
            connect = new MySqlConnection(connect);
            connect.Open();
            return connect;
        }
        catch (MySqlException e)
        {
            throw new Exception("Cannot connect", e);
        }
    }

let all the other codes as it is

edited Mar 15 '15 at 22:31

answered Mar 15 '15 at 22:20

Abdul Saleem

7,999
4
35
37

Now I get: `Additional information: Object reference not set to an instance of an object.` and the error appears when I try to open the form – dpaul1994 Mar 15 '15 at 22:21
`public static void closeConn() { connect.Close(); // -> this line }` – dpaul1994 Mar 15 '15 at 22:29
1

comment it //connect.close(); – Abdul Saleem Mar 15 '15 at 22:33
Have you commented that closing line in the ConnConfig class? – Abdul Saleem Mar 15 '15 at 22:36
Yes..I know...I did as you said above, if commented connect.Close(); and connect.Open() because there was an error too. Now I receive: `Specified cast is not valid.` :)) – dpaul1994 Mar 15 '15 at 22:38
To line: `int totalnu = (int)upad["totalno"];` – dpaul1994 Mar 15 '15 at 22:39
Now. You've recovered your connection error. This one is simple\ – Abdul Saleem Mar 15 '15 at 22:39
1

change it as int totalnu = Convert.ToInt32(upad["totalno"]); – Abdul Saleem Mar 15 '15 at 22:41
Awesome! It worked! Well, now I can remove openConn() and closeConn()? With those modifications in connaction class, the connection is open all the time? – dpaul1994 Mar 15 '15 at 22:50
1

You have to take care of closing connections. You should understand what i have done.. You wrote a code of opening one connection, and allowed it to close. But here i'm making you open several connections. Those must be closed properly – Abdul Saleem Mar 15 '15 at 22:53
I see, I'll be more carefully with them, but now I get the idea. – dpaul1994 Mar 15 '15 at 22:53
1

I will tell you the idea to close it.. Instead of using ConnConfig.getConnection(), use MySqlConnection conn1 = ConnConfig.getConnection() somewhere before and use conn1 and conn2. Then after all your use at the end of the method you can simply close by conn1.Close(); You understand? – Abdul Saleem Mar 15 '15 at 22:57
Sure yeah, I used this method before but somebody told me to do as you've seen..Thank you for the tips! – dpaul1994 Mar 15 '15 at 23:00

C# DataReader error

3 Answers3