Reputation: 320

C# DataReader error

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();
        }
    }

Upvotes: 1

Answers (3)

Abdul Saleem

Reputation: 10612

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

Upvotes: 1

Abdul Saleem

Reputation: 10612

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))

Upvotes: 1

Philip Stuyck

Reputation: 7457

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.

Upvotes: 1

C# DataReader error

Answers (3)

Related Questions