0

Visual Studio 2012 .Net 4.5

C# MySqlException: There is already an open DataReader associated with this Connection which must be closed first

It seems like a common problem, but as the location of the opening DataReader is not clear, it sucks for each specific time.

No problem with connection, or it would not reach the part of the code throwing exception. Connection is closed for each formClosing event.

Here's the line which has the problem:

cmd1.ExecuteNonQuery();

Here's the code for submitting a registration:

    private void btnSubmit_Click(object sender, EventArgs e)
    {
        lblEmail.Text = "";
        lblPwd.Text = "";
        lblPwd2.Text = "";

        string chkDuo = "SELECT * FROM f_user24.KancolleCust " + 
            "where Email = '" + txtEmail.Text + "';";

        string addCust = "INSERT INTO `f_user24`.`KancolleCust` " + 
            "(`Email`, `Pwd`, `Last_Name`, `First_Name`) " + 
            "VALUES (@email, @pwd, @fn, @ln);";
        string addCard = "INSERT INTO `f_user24`.`KancollePayment` " + 
            "(`Card_Num`, `Email`, `Pwd`) VALUES " + 
            "(@card, @email, @pwd);";

        cmd1 = new MySqlCommand(addCust, conn);
        cmd2 = new MySqlCommand(addCard, conn);
        cmd3 = new MySqlCommand(chkDuo, conn);

        cmd1.Parameters.AddWithValue("@email", txtEmail.Text);
        cmd1.Parameters.AddWithValue("@pwd", txtPwd.Text);
        cmd1.Parameters.AddWithValue("@fn", txtFN.Text);
        cmd1.Parameters.AddWithValue("@ln", txtLN.Text);

        cmd2.Parameters.AddWithValue("@card", txtCardNo.Text);
        cmd2.Parameters.AddWithValue("@email", txtEmail.Text);
        cmd2.Parameters.AddWithValue("@pwd", txtPwd.Text);

        if (txtEmail.Text == "")
            lblEmail.ForeColor = System.Drawing.Color.Red;
        if (txtPwd.Text == "")
            lblPwd.ForeColor = System.Drawing.Color.Red;

        if (txtPwd2.Text != txtPwd.Text)
            lblPwd2.Text = "Password doesn't match";



        if(lblEmail.Text == "" && lblPwd.Text == "" && lblPwd2.Text == "")
            try
            {
                cmd3.ExecuteNonQuery();
                if (!cmd3.ExecuteReader().HasRows)
                {
                    cmd1.ExecuteNonQuery();
                    MessageBox.Show("Registered successfully");
                    reged = true;
                }
                else
                    MessageBox.Show("The email is registered");


            }
            catch (MySqlException se)
            {
                MessageBox.Show(se.ToString());
            }

        if(txtCardNo.Text != "")
            try
            {
                cmd2.ExecuteNonQuery();
                MessageBox.Show("Payment Method Added");
            }
            catch (MySqlException se)
            {
                MessageBox.Show(se.ToString());
            }

        if (reged)
        {
            frmAcct f5 = new frmAcct();
            f5.EMAIL = txtEmail.Text;
            f5.Show();
            this.Close();
        }
    }

There's only one form before the registration page; other forms are supposed to be opened after log in, which I think would not be causing this exception.

    private void btnLogin_Click(object sender, EventArgs e)
    {
        lblEmail.Text = "";
        lblPwd.Text = "";

        string find = "SELECT Pwd FROM f_user24.KancolleCust " + 
            "where Email = '" + txtEmail.Text + "';";
        cmd = new MySqlCommand(find, conn);
        reader = cmd.ExecuteReader();
        reader.Read();
        if (!reader.HasRows)
            lblEmail.Text = "Invalid email";
        else if (txtPwd.Text != reader.GetString(0))
            lblPwd.Text = "Password incorrect";
        else
        {
            frmAcct f5 = new frmAcct();
            f5.EMAIL = txtEmail.Text;
            f5.Show();
            this.Close();
        }
        reader.Close();
    }

    private void txtPwd_KeyUp(object sender, KeyEventArgs e)
    {
        if (e.KeyCode == Keys.Enter)
            btnLogin_Click(sender, e);
    }

    private void btnReg_Click(object sender, EventArgs e)
    {
        frmReg f4 = new frmReg();
        f4.Show();
        this.Close();
    }
hanabi_noir
  • 197
  • 1
  • 1
  • 15
  • 2
    Instead of `reader.Close()` wrap it in a `using`-statement. On that way it is ensured that unmanaged resources are disposed and closed even in case of an error. – Tim Schmelter Dec 01 '15 at 14:41
  • Where is your connection created? It's quite a good idea to have your connections closed as soon as you are finished using them rather than have them opened at class level. – Daniel Casserly Dec 01 '15 at 14:42
  • Your code is too long and contains many unnecessary details. Close all your umanaged resources (connection, commands, etc) or use the [using](https://msdn.microsoft.com/en-us/library/yh598w02.aspx) statement on them. – Sergii Zhevzhyk Dec 01 '15 at 14:44
  • Does your MySql connector support MARS (Multiple active result sets)? Instead of using a `DataReader` you could fill a `DataTable` with a `MySqlDataAdapter` and then loop the roows. – Tim Schmelter Dec 01 '15 at 14:45
  • Thanks everybody. Now I understand that my problem is caused by duplication of connection, which I tried to avoid at the beginning. – hanabi_noir Dec 01 '15 at 15:36

0 Answers0