How to reuse SqlCommand parameter through every iteration?

Question

I want to implement a simple delete button for my database. The event method looks something like this:

private void btnDeleteUser_Click(object sender, EventArgs e) {     if (MessageBox.Show("Are you sure?", "delete users",MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)     {         command = new SqlCommand();         try         {             User.connection.Open();             command.Connection = User.connection;             command.CommandText = "DELETE FROM tbl_Users WHERE userID = @id";             int flag;             foreach (DataGridViewRow row in dgvUsers.SelectedRows)             {                 int selectedIndex = row.Index;                 int rowUserID = int.Parse(dgvUsers[0,selectedIndex].Value.ToString());                  command.Parameters.AddWithValue("@id", rowUserID);                 flag = command.ExecuteNonQuery();                 if (flag == 1) { MessageBox.Show("Success!"); }                  dgvUsers.Rows.Remove(row);             }         }         catch (SqlException ex)         {             MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);         }         finally         {             if (ConnectionState.Open.Equals(User.connection.State))                 User.connection.Close();         }     }     else     {         return;     } } 

but I get this message:

A variable @id has been declared. Variable names must be unique within a query batch or stored procedure.

Is there any way to reuse this variable?

Answer 1

Parameters.AddWithValue adds a new Parameter to the command. Since you're doing that in a loop with the same name, you're getting the exception "Variable names must be unique".

So you only need one parameter, add it before the loop and change only it's value in the loop.

command.CommandText = "DELETE FROM tbl_Users WHERE userID = @id"; command.Parameters.Add("@id", SqlDbType.Int); int flag; foreach (DataGridViewRow row in dgvUsers.SelectedRows) {     int selectedIndex = row.Index;     int rowUserID = int.Parse(dgvUsers[0,selectedIndex].Value.ToString());     command.Parameters["@id"].Value = rowUserID;     // ... } 

Another way is to use command.Parameters.Clear(); first. Then you can also add the parameter(s) in the loop without creating the same parameter twice.

Answer 2

Rather than:

command.Parameters.AddWithValue("@id", rowUserID); 

Use something like:

System.Data.SqlClient.SqlParameter p = new System.Data.SqlClient.SqlParameter(); 

Outside the foreach, and just set manually inside the loop:

p.ParameterName = "@ID"; p.Value = rowUserID;