c# - 变量名 '@VarName' 已经被声明 Issue

Question

我根据下拉列表中的选择将多个项目插入表中。当我从下拉列表中选择一项时，一切正常，但是当我选择多项时，出现此错误

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

我做错了什么？谢谢这是我的代码

protected void DV_Test_ItemInserting(object sender, DetailsViewInsertEventArgs e)
    {
        foreach (ListItem listItem in cblCustomerList.Items)
        {
            if (listItem.Selected)
            {
                string Name= listItem.Value;
                sqlcon.Open();
                string CompName= ((TextBox)DV_Test.FindControl("txtCompName")).Text.ToString();
                string Num = ((TextBox)DV_Test.FindControl("txtNum")).Text.ToString();
                SqlCommand cmd = new SqlCommand("select CompNamefrom MyTable where CompName= '" + CompName+ "' and Num = '" + Num + "' and Name= '" + Name+ "'    ", sqlcon);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    lblmsg.Text = "Not Valid";
                }
                else
                {
                    dr.Close();

                    sqlcmd.CommandText = "INSERT INTO MyTable(CompName, Num, Name) VALUES(@CompName, @Num, @Name)";
                    sqlcmd.Parameters.Add("@CompName", SqlDbType.VarChar).Value = CompName;
                    sqlcmd.Parameters.Add("@Num", SqlDbType.VarChar).Value = Num;
                    sqlcmd.Connection = sqlcon;                 
                    sqlcmd.ExecuteNonQuery();
                    DV_Test.ChangeMode(DetailsViewMode.Insert);
                    sqlcon.Close();
                }
                sqlcon.Close();               
            }

         }
 }

Answer 1

您在每次循环中都将参数 @CompName 和 @Num 添加到您的查询中。

您需要将对 Add 的调用移到循环外并使用以下内容更新它们:

sqlcmd.Parameters["@CompName"].Value = CompName;
sqlcmd.Parameters["@Num"].Value = Num;

所以你的代码变成:

sqlcmd.CommandText = "INSERT INTO MyTable(CompName, Num, Name) VALUES(@CompName, @Num, @Name)";
sqlcmd.Parameters.Add("@CompName", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@Num", SqlDbType.VarChar);

foreach (ListItem listItem in cblCustomerList.Items)
{
    if (....)
    {
        ....
    }
    else
    {
        dr.Close();

        sqlcmd.Parameters["@CompName"].Value = CompName;
        sqlcmd.Parameters["@Num"].Value = Num;
        sqlcmd.Connection = sqlcon;                 
        sqlcmd.ExecuteNonQuery();
        DV_Test.ChangeMode(DetailsViewMode.Insert);
        sqlcon.Close();
    }

}