Trying to do sql insert inside foreach loop, getting variable name declared error

[rbemiller]

Hi...new here. :) Trying a sql insert inside a foreach loop, getting "Variable name has already been declared...must be unique" error. I think the structure of my code or my parameter statements are incorrect.
Any help would be greatly appreciated! Thanks!

// open connection
                        mySqlConnection.Open();
                        
                        //enumerate the items in the list
                        foreach (SPListItem curItem in curItems)
                        {

                            string sql = "INSERT INTO ImportNotifications";
                            sql += " (SharepointID,Title,WorkOrderNumber,Status,Department,ClientName,WorkOrderDueDate,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy,Late,LateReview,TrueLate,Body)";
                            sql += " VALUES (@ID,@Title,@WorkOrderNum,@Status,@Department,@ClientName,@WorkOrderDue,@Created,@Author,@Modified,@Editor,@Late,@LateReview,@TrueLate,@Body)";
                            mySqlCommand.CommandText = sql;

                            mySqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Float)).Value = curItem["ID"];
                            mySqlCommand.Parameters.Add(new SqlParameter("@Title", SqlDbType.NVarChar, 200)).Value = curItem["Title"].ToString();
                            mySqlCommand.Parameters.Add(new SqlParameter("@WorkOrderNum", SqlDbType.NVarChar, 50)).Value = curItem["Work_x0020_Order_x0020_Number"].ToString();
                            mySqlCommand.Parameters.Add(new SqlParameter("@Status", SqlDbType.NVarChar, 50)).Value = curItem["Status"].ToString();
                            mySqlCommand.Parameters.Add(new SqlParameter("@Department", SqlDbType.NVarChar, 100)).Value = curItem["Department"].ToString();
                            mySqlCommand.Parameters.Add(new SqlParameter("@ClientName", SqlDbType.NVarChar, 255)).Value = curItem["Client_x0020_Name"].ToString();
                            mySqlCommand.Parameters.Add(new SqlParameter("@WorkOrderDue", SqlDbType.DateTime)).Value = curItem["Work_x0020_Order_x0020_Due_x0020"];
                            mySqlCommand.Parameters.Add(new SqlParameter("@Created", SqlDbType.DateTime)).Value = curItem["Created"];
                            mySqlCommand.Parameters.Add(new SqlParameter("@Author", SqlDbType.NVarChar, 50)).Value = curItem["Author"].ToString();
                            mySqlCommand.Parameters.Add(new SqlParameter("@Modified", SqlDbType.DateTime)).Value = curItem["Modified"];
                            mySqlCommand.Parameters.Add(new SqlParameter("@Editor", SqlDbType.NVarChar, 50)).Value = curItem["Editor"];
                            mySqlCommand.Parameters.Add(new SqlParameter("@Late", SqlDbType.NVarChar, 50)).Value = curItem["Late"];
                            mySqlCommand.Parameters.Add(new SqlParameter("@LateReview", SqlDbType.NVarChar, 50)).Value = curItem["Late_x0020_Review"];
                            //mySqlCommand.Parameters.Add(new SqlParameter("@ArchiveDate", SqlDbType.DateTime)).Value = curItem["Archive_x0020_Date"];
                            mySqlCommand.Parameters.Add(new SqlParameter("@TrueLate", SqlDbType.NVarChar, 50)).Value = curItem["TrueLate"];
                            mySqlCommand.Parameters.Add(new SqlParameter("@Body", SqlDbType.NVarChar, -1)).Value = curItem["Body"].ToString();
                            //mySqlCommand.Parameters.Add(new SqlParameter("@Expires", SqlDbType.DateTime)).Value = curItem["Expires"];

                            // execute the command
                            mySqlCommand.ExecuteNonQuery();

                            // write inserted titles to screen
                            Console.Write(curItem["Title"].ToString());    
                        }
// close the connection
mySqlConnection.Close();

Thanks again for any help!

[kvprajapati]

You are adding parameters on each iteration. Remove all the parameter instances from the parameter collection after the execution of ExecuteNonQuery statement.

[mail2saion]

On which line you get this error?

[dnanetwork]

<URL SNIPPED>
try gridview tab...rest is down there..

[sknake]

Here is a query aggregator class I use to aggregate upserts. A fair amount of time can be wasted if you send updates one at a time but sometimes this isn't practical.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace daniweb
{
  /// <summary>
  /// This class is designed to aggregate large upserts in to a single transaction
  /// </summary>
  internal sealed class QueryAggregator
  {
    /// <summary>
    /// Maximum number of params SQL Server allows in a transaction
    /// </summary>
    private const int MaxParametersAllowed = 2010;
    /* -------------------------------------------------------------------- */
    private List<string> _query;
    private List<SqlParameter> _parameters;
    private SqlParameter[] _startupParameters;
    private SqlConnection _connection;
    private bool _executed;
    private UInt64 _pCnt;
    /* -------------------------------------------------------------------- */
    /// <summary>
    /// True if at least one query was ran
    /// </summary>
    public bool Executed { get { return _executed; } }
    /* -------------------------------------------------------------------- */
    public QueryAggregator(SqlConnection Connection)
    {
      if (Connection == null)
        throw new ArgumentNullException("Connection");
      _query = new List<string>();
      _parameters = new List<SqlParameter>();
      _startupParameters = null;
      _executed = false;
      _connection = Connection;
      _pCnt = default(UInt64);
    }
    /* -------------------------------------------------------------------- */
    /// <summary>
    /// Aggregates a query for large upserts
    /// </summary>
    /// <param name="Parameters">Parameters that are common to all queries</param>
    public QueryAggregator(SqlConnection Connection, SqlParameter[] Parameters)
      : this(Connection)
    {
      _startupParameters = Parameters;
      SetDefaults();
    }
    /* -------------------------------------------------------------------- */
    private void SetDefaults()
    {
      if (_query == null)
        _query = new List<string>();
      else
        _query.Clear();

      if (_parameters == null)
        _parameters = new List<SqlParameter>();
      else
        _parameters.Clear();

      if ((_startupParameters != null) && (_startupParameters.Length > 0))
        _parameters.AddRange(_startupParameters);
    }
    /* -------------------------------------------------------------------- */
    public void AddQuery(string Query)
    {
      AddQuery(Query, (null as List<SqlParameter>));
    }
    /* -------------------------------------------------------------------- */
    public void AddQuery(string Query, SqlParameter Parameter)
    {
      if (Parameter == null)
        throw new ArgumentNullException("Parameter");
      List<SqlParameter> lst = new List<SqlParameter>();
      lst.Add(Parameter);
      AddQuery(Query, lst);
    }
    /* -------------------------------------------------------------------- */
    public void AddQuery(string Query, List<SqlParameter> Parameters)
    {
      if (Parameters == null)
      {
        _query.Add(Query);
      }
      else
      {
        if ((_parameters.Count + Parameters.Count) > MaxParametersAllowed)
          RunQuery();
        _query.Add(Query);
        _parameters.AddRange(Parameters.ToArray());
      }
    }
    /* -------------------------------------------------------------------- */
    public void RunQuery()
    {
      if (_query.Count > 0)
      {
        _executed = true;
        using (SqlCommand cmd = new SqlCommand(GetText(_query), _connection))
        {
          cmd.Parameters.AddRange(_parameters.ToArray());
          cmd.ExecuteNonQuery();
        }
      }

      SetDefaults();
    }
    /* -------------------------------------------------------------------- */
    public string GetParameterName()
    {
      if (_pCnt == UInt64.MaxValue)
      {
        _pCnt = 0;
      }

      return "@P" + _pCnt++.ToString();
    }
    /* -------------------------------------------------------------------- */
    public static string GetText(List<string> sc)
    {
      StringBuilder sb = new StringBuilder();
      for (int i1 = 0; i1 < sc.Count; i1++)
      {
        sb.AppendLine(sc[i1]);
      }
      return sb.ToString();
    }
    /* -------------------------------------------------------------------- */
  }
}

How to call it:

private void button1_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        {
          QueryAggregator agg = new QueryAggregator(conn);
          for (int i1 = 1; i1 <= 5000; i1++)
          {
            const string query = "Insert Into Test (InvNumber, CustNumber) Values ({0}, {1})";
            List<SqlParameter> lst = new List<SqlParameter>();

            SqlParameter pInvNumber = new SqlParameter(agg.GetParameterName(), SqlDbType.Int);
            pInvNumber.Value = i1 * 100;
            
            SqlParameter pCustNumber = new SqlParameter(agg.GetParameterName(), SqlDbType.Int);
            pCustNumber.Value = i1 * 100;
            
            lst.Add(pInvNumber);
            lst.Add(pCustNumber);
            agg.AddQuery(
              string.Format(query, pInvNumber.ParameterName, pCustNumber.ParameterName),
              lst);
          }

          agg.RunQuery();
        }
      }
    }

This will keep appending queries and parameters with unique parameter names until you hit the hard limit of 2010 (max parameters allowed in mssql) at which point it will execute and start collection again.