SqlCommand AddWithValue not working properly

Question

I have the following block of code... fieldCount always returns 0.

I suspect AddWithValue isn't forming the SELECT statement properly... Any ideas why? As you can see from the Watch, they (field, fieldId) have valid values.

public void deleteRows(string table, string field, string fieldId)
{
    int fieldCount;
     using (SqlCommand command = new SqlCommand(String.Format("SELECT COUNT(*) FROM {0} WHERE @field IN (@fieldId)", table), connection))
    {
        command.Parameters.AddWithValue("@field", field);
        command.Parameters.AddWithValue("@fieldId", fieldId);
        fieldCount = (int)command.ExecuteScalar();
    }
    if (fieldCount > 0)
    {

Debug screen

possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — Habib, Apr 21 '14 at 17:23
Look at [this answer](http://stackoverflow.com/a/337792/961113) from the linked duplicate question — Habib, Apr 21 '14 at 17:24

Steve · Accepted Answer · 2014-04-21T17:37:54.133

There are two errors in your code:

First, you cannot use a parameter to express a table name or a column name. So your field parameter is not valid in this context.

Second, you cannot use a parameter to express the whole set of values for the IN clause. In your example the parameter @fieldID will be translated in

WHERE ..... IN ('1,2,3,4')

It will be treated as a string not as the individual values to be included in your where context-

For the field part, if you are absolutely sure that the string field parameter passed to your method is not typed directly by your user then you could use a string concatenation expression (well you are already doing this for the table so the warning is good also for that value)

String.Format("SELECT COUNT(*) FROM {0} WHERE {1} IN (....)", table, field);

For the IN part, I suggest, instead of passing the string, to build, in the calling function, a list of parameters to be added at the query.

public void deleteRows(string table, string field, List<SqlParameter> inParameters)
{

    StringBuilder sb new StringBuilder();
    sb.AppendFormat("SELECT COUNT(*) FROM {0} WHERE {1} IN (", table, field));
    using(SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = connection;
        // Loop over the parameter list, adding the parameter name to the 
        // IN clause and the parameter to the SqlCommand collection
        foreach(SqlParameter p in inParameters)
        {
            sb.Append(p.Name + ",");
            cmd.Parameters.Add(p);
        }

        // Trim out the last comma
        sb.Length--;
        // Close the IN clause
        sn.Append(")";
        cmd.CommandText = sb.ToString();
        fieldCount = (int)command.ExecuteScalar();
    }

}

SqlCommand AddWithValue not working properly

1 Answers1