Alex Rivera | Logout

How to reuse SqlCommand parameter through every iteration?

Asked 2012-08-23T21:21:38.983
24

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?

Edit
Report

1 Answer

4

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;
answered 2012-08-23T21:29:57.737

Your Answer