How to use Insert with multiple C # SQL records

Question

How to use Insert with multiple C # SQL records

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I'm new here and also in the world of programming, today I managed for the first time to make the connection from C # to SQL, when making a simple connection with BD I can insert records, but when trying to insert 2 data in the same table simultaneously he creates 2 registers or inserts only the last, please help me, follow the code below ...

    private void bt_Cadastrar_Click(object sender, EventArgs e)
    {
        if (bt_Cadastrar.Text == "Salvar")
        {
            tb_ID.Enabled = false;
            bt_Cadastrar.Text = "Cadastrar";
        }

        Int16 Cheque, Convenio;

        if (cb_Cheque.Checked) Cheque = 1;
        else Cheque = 0;
        if (cb_Convenio.Checked) Convenio = 1;
        else Convenio = 0;

        SqlConnection conn = new SqlConnection("Server= localhost; Database=Cadastros; Integrated Security=true"); //Cria a conexão com o Banco de dados devido a Dll que tem na pasta do programa.
        SqlCommand cmd = new SqlCommand("insert into ccliente (Nome) values ('" + tb_Nome.Text.ToUpper() + "')", conn); //Comando SQL para criar inserir um item em uma tabela existente

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        /*
        cmd = new SqlCommand("insert into ccliente (CPF) values ('" + tb_CPF.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (RG) values ('" + tb_RG.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (CEP) values ('" + tb_CEP.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Cidade) values ('" + tb_Cidade.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Email) values ('" + tb_Email.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Telefone1) values ('" + tb_Telefone1.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Telefone2) values ('" + tb_Telefone2.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Observacao) values ('" + tb_Observacao.Text.ToUpper() + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Cheque) values ('" + Cheque + "')", conn);
        cmd = new SqlCommand("insert into ccliente (Convenio) values ('" + Convenio + "')", conn);
        */

    }

If necessary, the link to the complete project is below: goo.gl/6P8Hwc (Please, if any moderator thinks this is wrong you can delete the link, I'm new here and I do not know the rules yet). >

c# sql-insert

asked by anonymous 22.10.2017 / 01:55

2 answers

Chart pizza is not being "sliced" using SfChart How to make notifications the same as Facebook? AngularJS or JavaScript [closed]

score 1 · Answer 1

I believe your problem is in the syntax of the insert, if you perform all the cmd that are commented out, I would make a record for each line .. one solution would be:

insert into ccliente TODAS AS PROPRIEDADES values (TODOS OS RESPECTIVOS VALORES)", conn);

But that depends on the structure of your project, for example: you can have a class DAO ( Data Access Object) for each table in your database. In my working day, when we make a class, we also make a class in a DB context, that is, the class that will be instantiated and used to transform the data that is on the screen into data for the bank, for example:

  private void bt_Cadastrar_Click(object sender, EventArgs e)
    {
        Cliente novoCliente = new Cliente(); // instância da classe cliente, a qual referencia a tabela ccliente.

        if (bt_Cadastrar.Text == "Salvar")
        {
            tb_ID.Enabled = false;
            bt_Cadastrar.Text = "Cadastrar";
        }

        if (cb_Cheque.Checked) novoCliente.Cheque = true;
        if (cb_Convenio.Checked) novoCliente.Convenio = true;


        SqlConnection conn = new SqlConnection("Server= localhost; Database=Cadastros; Integrated Security=true"); //Cria a conexão com o Banco de dados devido a Dll que tem na pasta do programa.
        SqlCommand cmd = new SqlCommand("insert into ccliente values ('" + novoCliente.Nome + "')", conn); //Comando SQL para criar inserir um item em uma tabela existente

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close(); 
    }

PS: In my code there is an important point: assign the rest of the properties in the class.

Another point, when using objects and classes for communication with the bank, I think it is best to use Querys to perform actions with the database, which makes your job much easier in conjunction with LAMBDA .

Anyway, what I've tried to pass on is study that context and your life will be much easier when it comes to capturing and recording information in DB.

I hope I have helped: D

score 0 · Answer 2

I would advise you to study SQL syntax more. You can do as M.Bertolazo said, but for this, the fields have to be in order, and, be all fields in the table.

The correct syntax is:

"INSERT INTO TABELA (Nome,CPF,RG,CEP,Cidade,...) VALUES ('Nome','CPF','RG','CEP','Cidade',...);"

and taking advantage of the question, to execute two commands, you can do this:

string sql = @"INSERT INTO TABELA1 (CAMPO1,CAMPO2,CAMPO3) VALUES (1,2,3);";
sql += @"INSERT INTO TABELA2 (CAMPOA,CAMPOB,CAMPOC) VALUES ('A','B','C');";

SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

or so:

string sql = @"INSERT INTO TABELA1 (CAMPO1,CAMPO2,CAMPO3) VALUES (1,2,3);";
string sql2 = @"INSERT INTO TABELA2 (CAMPOA,CAMPOB,CAMPOC) VALUES ('A','B','C');";

SqlCommand cmd = new SqlCommand(sql, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
conn.Open();
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
conn.Close();

There are still other ways to do it depends a lot on your need.

Recommended reading: C # - Working with parameters in SQL queries link