CODEWITHSUNDEEP
c#sql-serverado.net
miller
miller

Reputation: 1728

How to reuse SqlCommand parameter through every iteration?

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?

Upvotes: 24

Views: 42062

Answers (4)

OrteipX
OrteipX

Reputation: 79

I would use this:

public static class DbExtensions
{
    public static void AddParameter(SQLiteCommand command, string name, DbType type, object value)
    {
        var param = new SQLiteParameter(name, type);
        param.Value = value;
        command.Parameters.Add(param);
    }
}

Then, call this:

DbExtensions.AddParameter(command, "@" + fieldOfSearch[i], DbType.String, value[i]);

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460018

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.

Upvotes: 61

Cashley
Cashley

Reputation: 516

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;

Upvotes: 4

Shyju
Shyju

Reputation: 218702

Error is because you are adding the same parameter again and again in each iteration of the loop.

I would move that code to a seperate method so that i can call it from multiple places as needed.

public bool DeleteUser(int userId)
{
    string connString = "your connectionstring";
    try
    {
      using (var conn = new SqlConnection(connString))
      {
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "DELETE FROM tbl_Users WHERE userID = @id";
            cmd.Parameters.AddWithValue("@id", userId);
            conn.Open();
            cmd.ExecuteNonQuery();
            return true;
        }
      }
    }
    catch(Exception ex)
    {
      //Log the Error here for Debugging
      return false;
    }

}

Then call it like this

 foreach (DataGridViewRow row in dgvUsers.SelectedRows)
 {
   int selectedIndex = row.Index;
   if(dgvUsers[0,selectedIndex]!=null)
   {
     int rowUserID = int.Parse(dgvUsers[0,selectedIndex].Value.ToString());
     var result=DeleteUser(rowUserID)
   }
   else
   {
      //Not able to get the ID. Show error message to user
   } 
 }

Upvotes: 0

Related Questions