Insert MYSQL into Web form C# - I cannot insert anything

Question

I’m trying to get an insight into my comics of a college app I’m developing. However, I’m having difficulties.

Taking the values of the texbox

 protected void button1_cad_cliente(object sender, EventArgs e)
    {
        Clientes cl = new Clientes();
        cl.Nome = nome_clientes.Text;
        cl.Cpf = Convert.ToInt32(cpf_clientes.Text);
        cl.Rg = Convert.ToInt32(rg.Text);
        cl.Endreco = endereco.Text;
        cl.Email = email_clientes.Text;

        ClienteDAL.cadastra(cl);

    }

DAL that performs the Insert or at least this is the intention

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Geax.Model;
using MySql.Data;
using MySql.Data.MySqlClient;

 namespace Geax.DAL
 {
public class ClienteDAL
{
    public static void cadastra(Clientes obj)
    {
        Conexao conn1 = new Conexao();
        conn1.AbrirConexao();
        String InsertCliente = ("INSERT INTO tab_cliente (nome,cpf,rg,endereco,telefone,email) VALUES('Cl.Nomes','Cl.Cpf','Cl.Rg','Cl.Endereco','Cl.Telefone','Cl.Email')");

        MySqlCommand cmd = new MySqlCommand(InsertCliente);

        cmd.ExecuteNonQuery();
    }
 }
}

Class connecting to the bank:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data;
using MySql.Data.MySqlClient;


namespace Geax.DAL
{
public class Conexao
{
private static MySqlConnection objConexao = null;
   private String conn_db = "server=localhost; Database=xpto; User=root;    Password='';";

    public void AbrirConexao()
   {
        objConexao = new MySqlConnection();
        objConexao.ConnectionString = conn_db;
        objConexao.Open();
   } 
 }   
}

---

    protected void button1_cad_cliente(object sender, EventArgs e)
    {
        Clientes cl = new Clientes();
        cl.Nome = nome_clientes.Text;
        cl.Cpf = Convert.ToInt32(cpf_clientes.Text);
        cl.Rg = Convert.ToInt32(rg.Text);
        cl.Endreco = endereco.Text;
        cl.Email = email_clientes.Text;

        ClienteDAL.cadastra(cl);

    }

How have I tried to associate the cmd.Parameters.AddWithValue("@nome", stringComNome); with the data I picked up.

Pus: cmd.Parameters.AddWithValue("@nome", nome_clientes.txt); but does not recognize

Answer 1

Never fire an SQL this way:

String InsertCliente = ("INSERT INTO tab_cliente (nome,cpf,rg,endereco,telefone,email) VALUES('Cl.Nomes','Cl.Cpf','Cl.Rg','Cl.Endereco','Cl.Telefone','Cl.Email')");

Parametrize at all times values, even if it is only a test:

String InsertCliente = ("INSERT INTO tab_cliente (nome,cpf,rg,endereco,telefone,email) VALUES(@nome, @cpf, @rg, @endereco, @telefone, @email)");

MySqlCommand cmd = new MySqlCommand(InsertCliente);
cmd.Parameters.AddWithValue("@nome", stringComNome);
cmd.Parameters.AddWithValue("@cpf", stringComCpf);
cmd.Parameters.AddWithValue("@rg", stringComRg);
cmd.Parameters.AddWithValue("@endereco", stringComEndereco);
cmd.Parameters.AddWithValue("@telefone", stringComTelefone);
cmd.Parameters.AddWithValue("@email", stringComEmail);

Another thing: the execution block must be inside a try so you can intercept exceptions if they occur:

try 
{
    Conexao conn1 = new Conexao();
    conn1.AbrirConexao();
    String InsertCliente = ("INSERT INTO tab_cliente (nome,cpf,rg,endereco,telefone,email) VALUES(@nome, @cpf, @rg, @endereco, @telefone, @email)");

    MySqlCommand cmd = new MySqlCommand(InsertCliente);
    cmd.Parameters.AddWithValue("@nome", stringComNome);
    cmd.Parameters.AddWithValue("@cpf", stringComCpf);
    cmd.Parameters.AddWithValue("@rg", stringComRg);
    cmd.Parameters.AddWithValue("@endereco", stringComEndereco);
    cmd.Parameters.AddWithValue("@telefone", stringComTelefone);
    cmd.Parameters.AddWithValue("@email", stringComEmail);

    cmd.ExecuteNonQuery();
}
catch (Exception e) {
    throw e;
}

Another thing is that the way it is, you’re not relating the connection to the command, so it’s never going to work. Place the command inside the use block of a connection:

using (var conn = new MySqlConnection("server=localhost; Database=xpto; User=root;    Password='';")) 
{
    try 
    {
        conn.Open();
        String InsertCliente = ("INSERT INTO tab_cliente (nome,cpf,rg,endereco,telefone,email) VALUES(@nome, @cpf, @rg, @endereco, @telefone, @email)");

        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = InsertCliente;
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("@nome", stringComNome);
        cmd.Parameters.AddWithValue("@cpf", stringComCpf);
        cmd.Parameters.AddWithValue("@rg", stringComRg);
        cmd.Parameters.AddWithValue("@endereco", stringComEndereco);
        cmd.Parameters.AddWithValue("@telefone", stringComTelefone);
        cmd.Parameters.AddWithValue("@email", stringComEmail);

        cmd.ExecuteNonQuery();
    }
    catch (Exception e) {
        throw e;
    }
}

The method at the end looks like this:

public static void Cadastra(Clientes obj)
{
    using (var conn = new MySqlConnection("server=localhost; Database=xpto; User=root;    Password='';")) 
    {
        try 
        {
            conn.Open();
            String InsertCliente = ("INSERT INTO tab_cliente (nome,cpf,rg,endereco,telefone,email) VALUES(@nome, @cpf, @rg, @endereco, @telefone, @email)");

            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = InsertCliente;
            cmd.Connection = conn;
            cmd.Parameters.AddWithValue("@nome", obj.Nome);
            cmd.Parameters.AddWithValue("@cpf", obj.Cpf);
            cmd.Parameters.AddWithValue("@rg", obj.Rg);
            cmd.Parameters.AddWithValue("@endereco", obj.Endereco);
            cmd.Parameters.AddWithValue("@telefone", obj.Telefone);
            cmd.Parameters.AddWithValue("@email", obj.Email);

            cmd.ExecuteNonQuery();
        }
        catch (Exception e) {
            throw e;
        }
    }
}