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

Question

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

When I execute this code below, above error comes on my sql parameters on second @LockState.

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; }
  }
}

Answer 1

You are adding multiple times the same parameters in every iteration of loop.

Add rwd.command.Parameters.Clear() after each loop iteration:

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();
}

Answer 2

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 cardNumber as well.

Answer 3

Use rwd.command.ExecuteScalar(); rwd.command.Parameters.clear();