Save data from a datagridview to a database

Question

I am wanting to save more than one line of my datagridview in the database, but it saves only the first line and after the error saying that the parameters have already been set, wanted to know where I am missing

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

        cmd = new MySqlCommand("INSERT INTO TesteCauaVendas VALUES(@Nome, @Cor, @Especie, @Quantidade, @Vendedor, @Data, @Cliente)", conn);

        try
        {
            for (int i = 0; i < dtCompra.Rows.Count - 1; i++)
            {
                cmd.Parameters.AddWithValue("@Nome", dtCompra.Rows[i].Cells[0].Value);
                cmd.Parameters.AddWithValue("@Cor", dtCompra.Rows[i].Cells[1].Value);
                cmd.Parameters.AddWithValue("@Especie", dtCompra.Rows[i].Cells[2].Value);
                cmd.Parameters.AddWithValue("@Quantidade", dtCompra.Rows[i].Cells[3].Value);
                cmd.Parameters.AddWithValue("@Vendedor", dtCompra.Rows[i].Cells[4].Value);
                cmd.Parameters.AddWithValue("@Data", dtCompra.Rows[i].Cells[5].Value);
                cmd.Parameters.AddWithValue("@Cliente", dtCompra.Rows[i].Cells[6].Value);                        
           
               MessageBox.Show("Venda Realizada com Sucesso");
            }

            cmd.ExecuteNonQuery();
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);                   
        }
        finally
        {
            conn.Close();
        }

Answer 1

For each insert you must have a query execution, ie each time you want to do the Insert you create the command, assign the parameters and execute,

In your code the solution would be to pass the query declaration (cmd) inside the for and also the execution inside the for (Executenonquery)

And by the way in a scenario that inserts several lines and want to have the guarantee that they are all saved or in case of failure not to record, you can use transactions.

  try
    {
        for (int i = 0; i < dtCompra.Rows.Count - 1; i++)
        {
            cmd = new MySqlCommand("INSERT INTO TesteCauaVendas VALUES(@Nome, @Cor, @Especie, @Quantidade, @Vendedor, @Data, @Cliente)", conn);
            cmd.Parameters.AddWithValue("@Nome", dtCompra.Rows[i].Cells[0].Value);
            cmd.Parameters.AddWithValue("@Cor", dtCompra.Rows[i].Cells[1].Value);
            cmd.Parameters.AddWithValue("@Especie", dtCompra.Rows[i].Cells[2].Value);
            cmd.Parameters.AddWithValue("@Quantidade", dtCompra.Rows[i].Cells[3].Value);
            cmd.Parameters.AddWithValue("@Vendedor", dtCompra.Rows[i].Cells[4].Value);
            cmd.Parameters.AddWithValue("@Data", dtCompra.Rows[i].Cells[5].Value);
            cmd.Parameters.AddWithValue("@Cliente", dtCompra.Rows[i].Cells[6].Value);                        
            cmd.ExecuteNonQuery();       
        }

           MessageBox.Show("Venda Realizada com Sucesso");

A note informing the user that the registration has been saved before storing it, in order not to mislead the user you should only notify the user when the registration was actually entered.