Error when using SqlCommand and foreach

Question

Error when using SqlCommand and foreach

Navigation

#1 by (2 votes)

1

I'm trying to navigate a list of objects and persist their data in SqlServer:

foreach (var item in pedidos)
{
   cmd.CommandText = "INSERT INTO MinhaTabela (Coluna_A, Coluna_B, Coluna_C) " +
            "VALUES (@Val_A, @Val_B, @Val_C)";

   cmd.Parameters.AddWithValue("@Val_A", item.AttributoA);
   cmd.Parameters.AddWithValue("@Val_B", item.AttributoB);
   cmd.Parameters.AddWithValue("@Val_C", item.AttributoC);
   cmd.ExecuteNonQuery();
}

I have received the following Exception :

The variable name '@Val_A' has already been declared. Variable names must be unique within a batch or stored procedure.

How to solve?

sql-server c# foreach

asked by anonymous 20.11.2017 / 13:54

1 answer

What extensions are supported by $ _FILES? AddComponent () with parameters in Unity3D?

score 2 · Accepted Answer

If you have to add the parameter and then delete it to add again because in your case it is an item interaction and you can still change the CommandText out of foreach , you only need to know this once, example :

cmd.CommandText = "INSERT INTO MinhaTabela (Coluna_A, Coluna_B, Coluna_C) " +
            "VALUES (@Val_A, @Val_B, @Val_C)";

foreach (var item in pedidos)
{       
   cmd.Parameters.AddWithValue("@Val_A", item.AttributoA);
   cmd.Parameters.AddWithValue("@Val_B", item.AttributoB);
   cmd.Parameters.AddWithValue("@Val_C", item.AttributoC);
   cmd.ExecuteNonQuery();
   cmd.Parameters.Clear(); // adicione essa linha para limpar os parametros existentes.
}

has another way that would be to use the same parameter created and change its value, but, your code would have to be checking for the existence of the parameter created, I believe it is more practical to clear the parameters and add again, but, a example addition as well:

cmd.CommandText = "INSERT INTO MinhaTabela (Coluna_A, Coluna_B, Coluna_C) " +
            "VALUES (@Val_A, @Val_B, @Val_C)";

foreach (var item in pedidos)
{
    if (command.Parameters.Contains("@Val_A"))
    {
        command.Parameters["@Val_A"].Value = item.AttributoA;
    }
    else 
    {
        command.Parameters.AddWithValue("@Val_A", item.AttributoA);
    }
    if (command.Parameters.Contains("@Val_B"))
    {
        command.Parameters["@Val_B"].Value = item.AttributoB;
    }
    else 
    {
        command.Parameters.AddWithValue("@Val_B", item.AttributoB);
    }
    if (command.Parameters.Contains("@Val_C"))
    {
        command.Parameters["@Val_C"].Value = item.AttributoC;
    }
    else 
    {
        command.Parameters.AddWithValue("@Val_C", item.AttributoC);
    }
}