Connection Problem with MySQL - Connection must be valid and open

5

I have a problem in a Windows Form in C # that uses DataGridView .

The following is done: When clicking on a row in the dataGrid the information of the respective row, saved in a database, should appear in a group of TextBoxes and from it you can change the information of these records of the DB.

There is, however, a connection error

  

(Connection must be valid and open)

with the SQL database I'm using (the tool it was created in is HeidiSQL). I do not know what it could be.

Can anyone help me?

The MySql.Data dll is inserted in the Properties. The connection error occurs on the line:

   cmd.ExecuteNonQuery();  

The source code for the form follows:

    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 = String.Format("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();

            }
        }  
    } 
}
    
asked by anonymous 11.11.2016 / 17:12

1 answer

0

From what I've noticed, you've created 2 variables;

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

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

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

    }
}  
    
11.11.2016 / 18:40