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
– dpaul1994, Commented Mar 15, 2015 at 21:18
Probably because you used ExecuteNonQuery while reader was still open. Which isn't allowed. — bokibeg
– bokibeg, Commented Mar 15, 2015 at 21:26
Aaaa...actually, I used ExecuteNonQuery everywhere for update.. — dpaul1994
– dpaul1994, Commented Mar 15, 2015 at 21:28

Abdul Saleem · Accepted Answer · 2015-03-15 22:31:20Z

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, 2015 at 22:31

answered Mar 15, 2015 at 22:20

Abdul Saleem

10.7k6 gold badges47 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

dpaul1994 Over a year ago

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

Abdul Saleem Over a year ago

comment it //connect.close();

Abdul Saleem Over a year ago

change it as int totalnu = Convert.ToInt32(upad["totalno"]);

Abdul Saleem Over a year ago

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 Over a year ago

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?

|

Philip Stuyck · Accepted Answer · 2015-03-15 21:32:17Z

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, 2015 at 21:32

Philip Stuyck

7,4673 gold badges31 silver badges40 bronze badges

Comments

Abdul Saleem · Accepted Answer · 2015-03-15 21:47:13Z

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, 2015 at 21:47

answered Mar 15, 2015 at 21:32

Abdul Saleem

10.7k6 gold badges47 silver badges47 bronze badges

7 Comments

dpaul1994 Over a year ago

I understand, but in my situation. where should I close and re-open the connection? Because I have a MysqlCommand in another MysqlCommand

dpaul1994 Over a year ago

But I don't understand one thing..that if where are these lines, should be called only when timer is = 0..

Abdul Saleem Over a year ago

Can you put the ConnConfig class?

Abdul Saleem Over a year ago

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 Over a year ago

The error happens on the load event right? Or after you click any button?

|

Collectives™ on Stack Overflow

C# DataReader error

3 Answers 3

10 Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related