错误:变量名"@Provision"已被声明

Error: The variable name '@Provision' has already been declared
2021-07-22
  •  译文(汉语)
  •  原文(英语)

我有一个checkedlistbox,textbox并且combobox为我的形式.我收到此错误:-

The variable name '@Provision' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@BloodGroup".

在我不使用for循环之前,它工作得很好checkedlistbox.使用for循环时,mycombobox BloodGroup出现错误.

string insert = "INSERT INTO Employee(EmpID, EmpName, Address, Provision, BloodGroup) VALUES (@EmpID, @EmpName, @Address, @Provision, @BloodGroup)";

SqlCommand comm = new SqlCommand(insert, sqlCon);

comm.Parameters.AddWithValue("@EmpID", SqlDbType.Int).Value = txtEmpID.Text;
comm.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text;
comm.Parameters.AddWithValue("@Address", SqlDbType.Text).Value = txtAdd.Text;

for (int a = 0; a < chkProvision.Items.Count; a++)
{
    if (chkProvision.GetItemChecked(a))
        comm.Parameters.AddWithValue("@Provision", SqlDbType.Char).Value = chkProvision.Items[a].ToString();
    else
        comm.Parameters.AddWithValue("@Provision", "");
}          

if (cbBlood.SelectedValue == null)
    comm.Parameters.AddWithValue("@BloodGroup", "");
else
    comm.Parameters.AddWithValue("@BloodGroup", SqlDbType.Char).Value = cbBlood.SelectedItem.ToString();

comm.ExecuteNonQuery();

当我更改SqlCommand之后for循环的位置时,它将在EmpID上引发错误:-

Must declare the scalar variable "@EmpID".
速聊1:
如果chkProvision有多个选定项,您实际上要做什么?添加多行?以及什么版本的SQL Server?(实际上看起来您的架构是错误的.EmpID,Provision如果Employee可以有多个表,则应该有另一个表.
速聊2:
顺便说一句:在您的字符串插入处是按值结尾处,一个逗号不带参数
速聊3:
是的,很抱歉..输入错误
速聊4:
我使用的是SQL Server2005.chkProvision会将选中的项目保存在一列中
解决过程1

你有

for (int a = 0; a < chkProvision.Items.Count; a++)
{
    if (chkProvision.GetItemChecked(a))
        comm.Parameters.AddWithValue("@Provision", SqlDbType.Char).Value = chkProvision.Items[a].ToString();
    else
        comm.Parameters.AddWithValue("@Provision", "");
}     

因此倾向于@Provision多次添加.这是不可能的

查询引擎必须唯一地标识必须更新的每个列,因此您不能在同一参数查询中多次使用同一列.

如果每个项目有多个配置,那么您需要使用不同的配置配置多个行,则必须多次执行查询:每个配置类型一个.或者,在服务器上创建一个存储过程,在其中传递主记录和与之关联的置备列表,然后由SP进行处理.

I have a checkedlistbox, textbox and combobox in my form. I'm getting this error: -

The variable name '@Provision' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@BloodGroup".

It worked fine before when I didn't use the for loop for my checkedlistbox. When using the for loop, my combobox BloodGroup, got an error.

string insert = "INSERT INTO Employee(EmpID, EmpName, Address, Provision, BloodGroup) VALUES (@EmpID, @EmpName, @Address, @Provision, @BloodGroup)";

SqlCommand comm = new SqlCommand(insert, sqlCon);

comm.Parameters.AddWithValue("@EmpID", SqlDbType.Int).Value = txtEmpID.Text;
comm.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text;
comm.Parameters.AddWithValue("@Address", SqlDbType.Text).Value = txtAdd.Text;

for (int a = 0; a < chkProvision.Items.Count; a++)
{
    if (chkProvision.GetItemChecked(a))
        comm.Parameters.AddWithValue("@Provision", SqlDbType.Char).Value = chkProvision.Items[a].ToString();
    else
        comm.Parameters.AddWithValue("@Provision", "");
}          

if (cbBlood.SelectedValue == null)
    comm.Parameters.AddWithValue("@BloodGroup", "");
else
    comm.Parameters.AddWithValue("@BloodGroup", SqlDbType.Char).Value = cbBlood.SelectedItem.ToString();

comm.ExecuteNonQuery();

When I change the position where my for loop comes after the SqlCommand, then it throws error on EmpID: -

Must declare the scalar variable "@EmpID".
Talk1:
What are you actually trying to do if chkProvision has multiple selected items? Add multiple rows? And what version of SQL Server? (Actually looks like your schema is wrong. There should be another table with EmpID,Provision if an Employee can have multiple.
Talk2:
BTW: at your string insert is by Values at the End, one Comma without Parameter
Talk3:
Yes, sorry.. typing error
Talk4:
I'm using SQL Server 2005. chkProvision will save checkeditems in one column
Solutions1

You have

for (int a = 0; a < chkProvision.Items.Count; a++)
{
    if (chkProvision.GetItemChecked(a))
        comm.Parameters.AddWithValue("@Provision", SqlDbType.Char).Value = chkProvision.Items[a].ToString();
    else
        comm.Parameters.AddWithValue("@Provision", "");
}     

so tend to add @Provision multiple times. It's not possible

Query engine have to uniquely identify every column that has to be updated, so you can not have the same column multiple times in the same parametric query.

If there are multiple Provisions per item, so you multiple rows with different provisions, you have to execute query multiple time: one per provision type. Or, create a storedprocedure on server, where you pass the main record and list of Provisions associated with them, and SP does the stuff.

转载于:https://stackoverflow.com/questions/17361626/error-the-variable-name-provision-has-already-been-declared

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入