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