advanced web statistics

Using SqlCommand Parameters with Multiple Inserts

1/11/2007 2:33:53 PM

I`ve received the following error today while working on a method that INSERTS multiple records into a database. I am using the SqlCommand class and parameterized queries. Keep in mind this is a simple vanilla insert, hence the lack of a SQL stored procedure.

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

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The variable name `@dayId` has already been declared. Variable names must be unique within a query batch or stored procedure.

The code below ( pseudo ) will give you the previous error.

private void UpdateDatabase(int dayId, string bar)
{
   using ( SqlConnection cn = connection )
   using ( SqlCommand cm = new SqlCommand("INSERT @dayId, @bar", cn) )
   {
      cn.Open();

      for ( int i = 0; i < 5; ++i )
      {
         cm.Parameters.AddWithValue("dayId", dayId + i);
         cm.Parameters.AddWithValue("bar", bar);
         cm.ExecuteNonQuery();
      }
   }
}

If you run into this same error when doing multiple inserts you simple need to clear your parameters after performing your ExecuteNonQuery().

private void UpdateDatabase(int dayId, string bar)
{
   using ( SqlConnection cn = connection )
   using ( SqlCommand cm = new SqlCommand("INSERT @dayId, @bar", cn) )
   {
      cn.Open();

      for ( int i = 0; i < 5; ++i )
      {
         cm.Parameters.AddWithValue("dayId", dayId + i);
         cm.Parameters.AddWithValue("bar", bar);
         cm.ExecuteNonQuery();
         cm.Parameters.Clear();
      }
   
   }
}

Easy. If I help only 1 person with this then I feel that I have done my job!

A little aside; I couldn't find any documentation for SqlCommand.Parameters.Clear() on MSDN. To me that seems strange.

.NET, C#, Code, Programming

kick it on DotNetKicks.com

Leave a Comment

   

  Enter the text to proceed!