变量名“@”已被声明。变量名在查询批处理或存储过程中必须是唯一的。在 C# 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19535486/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The variable name '@' has already been declared. Variable names must be unique within a query batch or stored procedure. in c#
提问by yas sh
The variable name '@LockState' has already been declared. Variable names must be unique within a query batch or stored procedure.
变量名“@LockState”已被声明。变量名在查询批处理或存储过程中必须是唯一的。
When I execute this code below, above error comes on my sql parameters on second @LockState.
当我在下面执行此代码时，第二个@LockState 上的 sql 参数出现上述错误。
private void btn_lock2_Click(object sender, EventArgs e)
{
rwd.command = new SqlCommand();
rwd.command.Connection = rwd.connection;
try
{
if ((txt2.Text == "")| (txt_desc2.Text == ""))
appMessages.unCompleteFields();
else
{
long from = long.Parse(this.txt2.Text);
long to = long.Parse(this.txt3.Text);
if (from <= to)
{
for (long counter = from; counter <= to; counter++)
{
string upd = "update card set LockState=@lockstate,
card_descr=@card_descr where [cardNumber] = N'{0}'";
rwd.command.CommandText = upd;
rwd.command.Parameters.Add(new SqlParameter("@LockState",
SqlDbType.NVarChar)).Value =1;
rwd.command.Parameters.Add(new SqlParameter("@card_descr",
SqlDbType.NVarChar)).Value = txt_desc2.Text;
rwd.connection.Open();
rwd.command.ExecuteScalar();
rwd.connection.Close();
}
appMessages.successfulyUpdated();
}
else
{
appMessages.unsuccessfulyUpdated();
}
this.txt1.Text = "";
this.txt2.Text = "";
}
}
catch (Exception exp) { throw exp; }
}
}
回答by Damith
since you share the command in each iteration clear the parameters at the beginning of the loop.
由于您在每次迭代中共享命令，因此在循环开始时清除参数。
for (long counter = from; counter <= to; counter++)
{
rwd.command.Parameters.Clear();
But I would only set the value in the loop by defining it before the loop as below
但我只会通过在循环之前定义它来设置循环中的值，如下所示
rwd.command.Parameters.Add("@LockState",SqlDbType.NVarChar);
rwd.command.Parameters.Add("@card_descr",SqlDbType.NVarChar);
for (long counter = from; counter <= to; counter++)
{
rwd.command.Parameters["@LockState"].Value = 1;
rwd.command.Parameters["@card_descr"].Value = txt_desc2.Text;
// ...
}
Side Note:
边注：
you have [cardNumber] = N'{0}'"but never set value for that. you better use parameter for the cardNumberas well.
你有[cardNumber] = N'{0}'"但从未为此设定价值。你最好也使用参数cardNumber。
回答by gzaxx
You are adding multiple times the same parameters in every iteration of loop.
您在循环的每次迭代中多次添加相同的参数。
Add rwd.command.Parameters.Clear()after each loop iteration:
rwd.command.Parameters.Clear()在每次循环迭代后添加：
for (long counter = from; counter <= to; counter++)
{
rwd.command.Parameters.Clear();
string upd = "update card set LockState=@lockstate, card_descr=@card_descr where [cardNumber] = N'{0}'";
rwd.command.CommandText = upd;
rwd.command.Parameters.Add(new SqlParameter("@LockState",
SqlDbType.NVarChar)).Value =1;
rwd.command.Parameters.Add(new SqlParameter("@card_descr",
SqlDbType.NVarChar)).Value = txt_desc2.Text;
rwd.connection.Open();
rwd.command.ExecuteScalar();
rwd.connection.Close();
}
or add parameter before loop:
或在循环前添加参数：
rwd.command.Parameters.Add(new SqlParameter("@LockState", SqlDbType.NVarChar));
rwd.command.Parameters.Add(new SqlParameter("@card_descr", SqlDbType.NVarChar));
and then in loop:
然后在循环中：
for (long counter = from; counter <= to; counter++)
{
string upd = "update card set LockState=@lockstate,
card_descr=@card_descr where [cardNumber] = N'{0}'";
rwd.command.CommandText = upd;
rwd.command.Parameters["@LockState"].Value =1;
rwd.command.Parameters["@card_descr"].Value = txt_desc2.Text;
rwd.connection.Open();
rwd.command.ExecuteScalar();
rwd.connection.Close();
}
回答by Hilarion
My answer is basically an extension to other two existing answers (from @gzaxx and @Damith), which both are correct.
You do use the same SqlCommandinstance in each loop iteration, so you do not have to (and should not) perform any initialization operations in the loop.
What others already wrote is that adding parameters' definitions in the loop is the cause of your issue, as in result you are trying to define each of the parameters as many times, as the loop iterates.
What I think you should also consider, is also moving other code out of the loop, which basically means removing this from inside the loop:
我的答案基本上是对其他两个现有答案（来自@gzaxx 和@Damith）的扩展，这两个答案都是正确的。
您确实SqlCommand在每次循环迭代中使用相同的实例，因此您不必（也不应该）在循环中执行任何初始化操作。
其他人已经写的是，在循环中添加参数的定义是导致您出现问题的原因，因为结果您试图在循环迭代时多次定义每个参数。
我认为您还应该考虑的是，还将其他代码移出循环，这基本上意味着从循环内部删除它：
string upd = "update card set LockState=@lockstate,
card_descr=@card_descr where [cardNumber] = N'{0}'";
rwd.command.CommandText = up
rwd.command.Parameters.Add(new SqlParameter("@LockState", SqlDbType.NVarChar)).Value =1;
rwd.command.Parameters.Add(new SqlParameter("@card_descr", SqlDbType.NVarChar)).Value = txt_desc2.Text;
rwd.connection.Open();
and placing its equivalent before the loop:
并将其等效项放在循环之前：
string upd = "update card set LockState=@lockstate,
card_descr=@card_descr where [cardNumber] = N'{0}'";
rwd.command.CommandText = up
SqlParameter lockStateParam = rwd.command.Parameters.Add("@LockState",SqlDbType.NVarChar);
SqlParameter cardDescrParam = rwd.command.Parameters.Add("@card_descr",SqlDbType.NVarChar);
rwd.connection.Open();
You'd also need to replace the way you set parameter values, placing this in the loop, instead of the removed code:
您还需要替换设置参数值的方式，将其放置在循环中，而不是删除的代码：
lockStateParam.Value = 1;
cardDescrParam.Value = txt_desc2.Text;
As we moved opening the connection out of the loop, we also need to move the connection closing operation outside of the loop, so this instructions goes out:
当我们将打开连接移出循环时，我们还需要将连接关闭操作移到循环之外，所以这个指令就出来了：
rwd.connection.Close();
The entire operation gives us this code block:
整个操作给了我们这个代码块：
string upd = "update card set LockState=@lockstate,
card_descr=@card_descr where [cardNumber] = N'{0}'";
rwd.command.CommandText = up
SqlParameter lockStateParam = rwd.command.Parameters.Add("@LockState",SqlDbType.NVarChar);
SqlParameter cardDescrParam = rwd.command.Parameters.Add("@card_descr",SqlDbType.NVarChar);
rwd.connection.Open();
for (long counter = from; counter <= to; counter++)
{
lockStateParam.Value = 1;
cardDescrParam.Value = txt_desc2.Text;
rwd.command.ExecuteScalar();
}
rwd.connection.Close();
When looking further at this, I see, that each iteration actually uses the very same values for the parameters, so - if it's not a result of simplifying the code for StackOverflow - it means that you basically execute the very same update over and over again.
I'd assume that it actually is a simplified code, as (just as @Damith noted) you seem to be filtering the updated rows by cardNumbercolumn. What seems a bit strange in that, is that you are apparently using string formatting, to replace the {0}placeholder with a text. If it's so, then you do have to set the CommandTextattribute inside the loop, but I'd rather consider doing what @Damith suggested, so adding a third command parameter for that purpose.
当进一步查看时，我发现每次迭代实际上使用相同的参数值，因此 - 如果这不是简化 StackOverflow 代码的结果 - 这意味着您基本上一遍又一遍地执行相同的更新.
我认为它实际上是一个简化的代码，因为（正如@Damith 指出的那样）您似乎正在按cardNumber列过滤更新的行。看起来有点奇怪的是，您显然正在使用字符串格式，{0}用文本替换占位符。如果是这样，那么您必须CommandText在循环内设置属性，但我宁愿考虑按照@Damith 的建议进行操作，因此为此添加了第三个命令参数。
On a side-note: If the answers provided work for you (and basically all given should), consider accepting one of those (probably the first one that appeared here).
附带说明：如果提供的答案对您有用（并且基本上所有给出的答案都应该），请考虑接受其中一个（可能是此处出现的第一个）。
回答by Joseph hribach
Use rwd.command.ExecuteScalar(); rwd.command.Parameters.clear();
使用 rwd.command.ExecuteScalar(); rwd.command.Parameters.clear();
