CODEWITHSUNDEEP
c#sql-server
Travis
Travis

Reputation: 687

C# SQL Insert saying "duplicate variable"

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);
            }
        }
    }
}

Upvotes: 1

Views: 390

Answers (2)

Steve
Steve

Reputation: 216273

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)

Upvotes: 2

Tomas Chabada
Tomas Chabada

Reputation: 3019

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);

Upvotes: 0

Related Questions