sql server2005 Insert数据报错

Question

SWBOM.C_PART_MAP_T表有三个栏位，compal_pn,cust_pn,model,(compal_pn与cust_pn是联合主键)
插入的值
compal_pn         cust_pn     model
JITR1320022 59-014967   JITR1
JITR1320021 59-014926  JITR1
报错信息：
變數名稱 '@COMPALPN' 已經宣告。變數名稱在一個查詢批次或預存程序內必須是唯一的。public void InsertTXT(DataSet ds, SqlCommand command) 
        {
            if (ds != null)
            {
                string sql = @" INSERT INTO [SWBOM.C_PART_MAP_T]
                                ([compal_pn],[cust_pn],[model])      
                                VALUES (@COMPALPN,@CUSTPN,@SKU) ";
                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                int count=dt.Rows.Count;
                if (count > 0)
                {
                    command.CommandText = sql;
                    for (int i = 0; i <= count-1; i++)//减一是把第一行标题去掉
                    {
                        string ss = dt.Rows[i][0].ToString();
                        string ff = dt.Rows[i][1].ToString();
                        string dd = dt.Rows[i][2].ToString();
                        command.Parameters.AddWithValue("@COMPALPN", dt.Rows[i][0].ToString());
                        command.Parameters.AddWithValue("@CUSTPN", dt.Rows[i][1].ToString());
                        command.Parameters.AddWithValue("@SKU", dt.Rows[i][2].ToString());
                        command.ExecuteNonQuery();
                    }
                }
            }        }
        #endregion        #region UnionOperTxT TxT Data
        public ExecutionResult UnionOperTxT(DataSet ds) //先Delete表数据，然后Insert
        {
            ExecutionResult exeRes = new ExecutionResult();
            SqlTransaction transaction = null;
            try 
            {
                transaction = connection.BeginTransaction();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                this.InsertTXT(ds, command);
                transaction.Commit();
                exeRes.Message = "Insert TXT File Data Success!";
                exeRes.State = true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                exeRes.Message = "Insert TXT File Data Fail!"+ex.Message;
                exeRes.State = false;
            }
            
            return exeRes;        }
        #endregion

Answer 1

单步调试查看得到的SQL语句是否正确
放在查询分析器中编译一下

Answer 2

                    for (int i = 0; i <= count-1; i++)//减一是把第一行标题去掉
                    {
                        string ss = dt.Rows[i][0].ToString();
                        string ff = dt.Rows[i][1].ToString();
                        string dd = dt.Rows[i][2].ToString();
                        command.Parameters.AddWithValue("@COMPALPN", dt.Rows[i][0].ToString());
                        command.Parameters.AddWithValue("@CUSTPN", dt.Rows[i][1].ToString());
                        command.Parameters.AddWithValue("@SKU", dt.Rows[i][2].ToString());
                        command.ExecuteNonQuery();
                    }
这里你循环插入参数了

Answer 3

插入参数了，我连接ORACLE测试是OK的，但是SQL SERVER2005报错，只能插入一条记录

Answer 4

            if (count > 0)
            {
                command.CommandText = sql;
                command.Parameters.Add("@COMPALPN", System.Data.SqlDbType.NVarChar);
                command.Parameters.Add("@CUSTPN", System.Data.SqlDbType.NVarChar);
                command.Parameters.Add("@SKU", System.Data.SqlDbType.NVarChar);
                for (int i = 0; i <= count - 1; i++)//减一是把第一行标题去掉
                {
                    string ss = dt.Rows[i][0].ToString();
                    string ff = dt.Rows[i][1].ToString();
                    string dd = dt.Rows[i][2].ToString();
                    command.Parameters["@COMPALPN"].Value = ss;
                    command.Parameters["@CUSTPN"].Value = ff;
                    command.Parameters["SKU"].Value = dd;
                    command.ExecuteNonQuery();
                }
            }
随便写下没调试过
另外似乎你connection没有open()么

Answer 5

你一条commad中有3个参数,然后循环中每次都插入3个参数,所以只有第一次是对的么,Parameters.Add应该在循环外面,循环中改为只改变参数的值.
或者每次循环都new一个command