C# SQL Insert saying "duplicate variable"

Question

The code below is supposed to iterate through a list of components and insert them for a specific bill of materials. The code executes the loop once, and then sends out an error message,

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

Im not sure the best way to change this or why this is being thrown. Any thoughts?

Would I just have to move the foreach outside of the second using statement?

public static void insert_rework(string product, List<string> component_list, string sn, DateTime today, string connectionString)
{
    string sql = 
      @"INSERT INTO table (
          Product, 
          Component, 
          Serial_Num, 
          Cur_Date) 
        VALUES (
          @product, 
          @component, 
          @sn, 
          @date)";

    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        connection.Open();
        foreach (string component in component_list)
        {
            command.Parameters.AddWithValue("@product", product);
            command.Parameters.AddWithValue("@component", component);
            command.Parameters.AddWithValue("@sn", sn);
            command.Parameters.AddWithValue("@date", today);
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException sq)
            {
                Console.WriteLine(sq.Message);
            }
        }
    }
}

Does this answer your question? [How to reuse SqlCommand parameter through every iteration?](https://stackoverflow.com/questions/12100157/how-to-reuse-sqlcommand-parameter-through-every-iteration) — PaulF, Feb 12 '20 at 14:09

Steve · Accepted Answer · 2020-02-12T15:06:49.550

You have your AddWithValue calls inside the foreach loop. Of course this means that at each loop you readd the same parameters with different values.

What about declaring them outside the loop and just changing the value inside the loop

   command.Parameters.Add("@product", SqlDbType.NVarChar);
   command.Parameters.Add("@component", SqlDbType.NVarChar);
   command.Parameters.Add("@sn", SqlDbType.NVarChar);
   command.Parameters.Add("@date", SqlDbType.Date);       
   foreach (string component in component_list)
   {
       command.Parameters["@product"].Value = product;
       command.Parameters["@component"].Value = component;
       command.Parameters["@sn"].Value = sn;
       command.Parameters["@date"].Value = today;
       .....

Of course you can use Clear to remove any parameter from the collection but this seems to be unneeded. Also, some values seems to never change inside the loop. These value could be added outside the loop just one time.

   command.Parameters.Add("@product", SqlDbType.NVarChar);
   command.Parameters.Add("@component", SqlDbType.NVarChar).Value = component;
   command.Parameters.Add("@sn", SqlDbType.NVarChar).Value = sn;
   command.Parameters.Add("@date", SqlDbType.Date).Value = today;       
   foreach (string component in component_list)
   {
       command.Parameters["@component"].Value = component;
       command.ExecuteNonQuery();
   }

Notice also that you should always use Add instead of AddWithValue because this method, while handy, is well known to hit the performances and sometime it can also give wrong results if the value passed to AddWithValue is not of the type expected by the underlying table. (localized strings instead of dates, localized strings instead of decimals,double,etc)

Thanks for the answer, I read that the Add feature of command.Parameters was deprecated. Is that true? — Travis, Feb 12 '20 at 14:19
It is deprecated the version with the signature (parametername, object) but not the version that takes a SqlDbType — Steve, Feb 12 '20 at 14:25
More info: [Add vs AddWithValue](https://stackoverflow.com/questions/9999751/difference-between-parameters-addstring-object-and-parameters-addwithvalue) and [Can we stop using AddWithValue already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Steve, Feb 12 '20 at 14:28

score 0 · Answer 2 · answered Feb 12 '20 at 14:14

You are adding the same parameter every time in foreach loop. Either clear parameters before adding them or check existence and perform accordingly:

if (command.Parameters.Contains("@product"))
    command.Parameters["@product"].Value = product;
else
    command.Parameters.AddWithValue("@product", product);

C# SQL Insert saying "duplicate variable"

2 Answers2