Mysql connection problem - Connection must be Valid and open

Question

I’m having a problem at a Windows Form in C# using Datagridview.

The following is done: by clicking on a datagrid line the information of the respective line, saved in a database, must appear in a group of Textboxes and from it you can change the information of these BD records.

Is happening, however, a connection error

(Connection must be Valid and open)

with the SQL database I am using (the tool in which it was created is Heidisql). I don’t know what could be.

Could someone help me?

The Mysql.Data dll is inserted into Properties. The connection error occurs on the line:

   cmd.ExecuteNonQuery();  

The source code of the form follows below:

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;

namespace frmTCC
{
    public partial class frmGerenciarClientes : Form
    {
        //Declarando variaveis globais
        int id;
        private MySqlConnection cone = new MySqlConnection();
        private MySqlCommand comandoSql = new MySqlCommand();
        private MySqlDataReader dados;
        public frmGerenciarClientes()
        {
            InitializeComponent();
            //Mapeando evento de seleção de celulas da tabela
            //dgvClientes.CellMouseUp += dgvClientes_CellMouseUp;
        }

        public void test()
        {
           string config = "server=localhost; user id=root; database=confeitaria; Password='';";
           string query = "SELECT cod_cli, nome_cli, end_cli, endnum_cli, bairro_cli, cid_cli, cel_cli, tel_cli, email_cli FROM clientes WHERE nome_cli LIKE '" + txtNome.Text + "%' ORDER BY nome_cli ASC";

           MySqlConnection conexao = new MySqlConnection(config);
           conexao.Open();

           MySqlCommand command = new MySqlCommand(query, conexao);
           MySqlDataAdapter adpter = new MySqlDataAdapter(command);

           DataTable data = new DataTable();
           adpter.Fill(data);
           dgvClientes.DataSource = data;
        }

        public void btnEditar_Click(object sender, EventArgs e)
        {
            try
            {
                string strSql = "select * from clientes where cod_cli=" + txtIdALT.Text;
                //criando o SQL e o comando para sua execução
                comandoSql.Connection = cone;
                comandoSql.CommandText = strSql;
                dados = comandoSql.ExecuteReader();


                //validando se encontrou algum registro na tabela
                if (!dados.HasRows)
                {
                    MessageBox.Show("Código NÃO Existente", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtIdALT.Focus();
                }
                else
                {
                    /*se o comando entrar no ELSE,significa que o executeReader esta aberto.Entao, é preciso fechá-lo para
                     * que possamos prosseguir e executar o comando insert.*/
                    if (!dados.IsClosed)
                    {
                        dados.Close();
                    }

                    //ALTERANDO OS DADOS NO BD
                    strSql = "Update cliente set";
                    strSql += " nome_cli= '" + txtNome1.Text + "',";
                    strSql += "end_cli ='" + txtEndereco.Text + "',";
                    strSql += "endnum_cli = '" + txtNum.Text + "',";
                    strSql += "bairro_cli='" + txtBairro.Text + "',";
                    strSql += "cid_cli='" + txtCidade.Text + "',";
                    strSql += "cel_cli='" + mtxtCel.Text + "',";
                    strSql += "tel_cli='" + mtxtTel.Text + "',";
                    strSql += "email_cli='" + txtEmail.Text + "'";
                    strSql += "where cod_cli=" + txtIdALT.Text;

                    //indicando qual conexão usar para executar o insert
                    comandoSql.Connection = cone;
                    //indicaando qual instrução utilizar
                    comandoSql.CommandText = strSql;
                    //após toda a execução, não retornara nada do BD
                    comandoSql.ExecuteNonQuery();
                    MessageBox.Show("Registro ALTERADO com sucesso", "Parabéns", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    test();
                }
            }
            catch (Exception erro)
            {
                MessageBox.Show("Erro" + erro.Message, "Atenção", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void dgvClientes_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            id = Convert.ToInt32(dgvClientes.Rows[e.RowIndex].Cells["cod_cli"].Value.ToString());
            MySqlCommand cmd = cone.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from clientes where cod_cli=" + id + "";
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(dt);
            foreach (DataRow dr in dt.Rows)
            {
                txtIdALT.Text = dr["cod_cli"].ToString();
                txtNome1.Text = dr["nome_cli"].ToString();
                txtEndereco.Text = dr["end_cli"].ToString();
                txtNum.Text = dr["endnum_cli"].ToString();
                txtBairro.Text = dr["bairro_cli"].ToString();
                txtCidade.Text = dr["cid_cli"].ToString();
                mtxtCel.Text = dr["cel_cli"].ToString();
                mtxtTel.Text = dr["tel_cli"].ToString();
                txtEmail.Text = dr["email_cli"].ToString();

            }
        }
    }
}

Answer 1

From what I’ve observed, you’ve created two variables;

private MySqlConnection cone = new MySqlConnection();
private MySqlCommand comandoSql = new MySqlCommand();

that you use in some of the methods and in that methods you add to comandoSql.Connection = cone; I am your connection itself.

But in the method below you create another MySqlCommand cmd = cone.CreateCommand();, and in it you are not passing the cmd.Connection = cone; Parenting your method test() is what is right.

private void dgvClientes_CellClick(object sender, DataGridViewCellEventArgs e)
{
    id = Convert.ToInt32(dgvClientes.Rows[e.RowIndex].Cells["cod_cli"].Value.ToString());
    MySqlCommand cmd = cone.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select * from clientes where cod_cli=" + id + "";
    cmd.ExecuteNonQuery();
    DataTable dt = new DataTable();
    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
    da.Fill(dt);
    foreach (DataRow dr in dt.Rows)
    {
        txtIdALT.Text = dr["cod_cli"].ToString();
        txtNome1.Text = dr["nome_cli"].ToString();
        txtEndereco.Text = dr["end_cli"].ToString();
        txtNum.Text = dr["endnum_cli"].ToString();
        txtBairro.Text = dr["bairro_cli"].ToString();
        txtCidade.Text = dr["cid_cli"].ToString();
        mtxtCel.Text = dr["cel_cli"].ToString();
        mtxtTel.Text = dr["tel_cli"].ToString();
        txtEmail.Text = dr["email_cli"].ToString();

    }
}