Update query in C# executes, but doesn't update in SQL

Question

The parameters passed are good. I even ran the query within SQL to see if I'm off, but it updated the database. But when I run my program while debugging, it will run the query, but not update my database.

    public void UpdateRowValueQuery<T>(T table, string columnName,
                    string columnValue, string whereColumn, string whereValue,
                    Config config)
    {
        // Store the output query
        StringBuilder query = new StringBuilder();

        // Insert query that adds the database name
        // and table name passed through
        query.Append("UPDATE ");
        query.Append(config.DatabaseName);
        query.Append(".dbo.");
        query.Append(typeof(T).Name);
        query.Append(" SET ");
        query.Append("@columnName");
        query.Append(" = ");
        query.Append("@columnValue");
        query.Append(" WHERE ");
        query.Append("@whereColumn");
        query.Append(" = ");
        query.Append("@whereValue");

        // Execute the update
        using (SqlConnection conn = DBConnection.GetSqlConnection())
        {
            using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
            {
                cmd.Parameters.Add(new SqlParameter("@columnName", columnName));
                cmd.Parameters.Add(new SqlParameter("@columnValue", columnValue));
                cmd.Parameters.Add(new SqlParameter("@whereColumn", whereColumn));
                cmd.Parameters.Add(new SqlParameter("@whereValue", whereValue));

                cmd.ExecuteNonQuery();
            }
        }
    }

Please show your connection string. The most likely cause is you are not updating the database you think you are updating. — Scott Chamberlain, Aug 24 '15 at 21:26
Same as [here](http://stackoverflow.com/questions/32184389/c-sharp-using-sql-parameters-which-now-wont-work/32184467#32184467) — Lukasz Szozda, Aug 24 '15 at 21:28
You cannot use a parameter to express a column name (neither a datatable) — Steve, Aug 24 '15 at 21:28
Steve was right. I removed the columnName and whereColumn parameters and had them go directly in, and the query works. Thanks everyone for the quick responses. — jcameron47, Aug 24 '15 at 21:32

score 0 · Answer 1 · answered Aug 24 '15 at 21:37

Removing the column names from the parameters is what allowed the query to work. Interesting how it doesn't inform you of any errors and runs, though not actually working. Thank you all for the help.

public void UpdateRowValueQuery(T table, string columnName, string columnValue, string whereColumn, string whereValue, Config config) { // Store the output query StringBuilder query = new StringBuilder();

        // Insert query that adds the database name
        // and table name passed through
        query.Append("UPDATE ");
        query.Append(config.DatabaseName);
        query.Append(".dbo.");
        query.Append(typeof(T).Name);
        query.Append(" SET ");
        query.Append(columnName);
        query.Append(" = ");
        query.Append("@columnValue");
        query.Append(" WHERE ");
        query.Append(whereColumn);
        query.Append(" = ");
        query.Append("@whereValue");

        // Execute the update
        using (SqlConnection conn = DBConnection.GetSqlConnection())
        {
            using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
            {
                cmd.Parameters.Add(new SqlParameter("@columnValue", columnValue));
                cmd.Parameters.Add(new SqlParameter("@whereValue", whereValue));

                cmd.ExecuteNonQuery();
            }
        }
    }

score 0 · Accepted Answer · answered Aug 24 '15 at 21:47

Your query ends up being something like :

UPDATE {table} 
SET @columnName = @columnValue
WHERE @whereColumn = @whereValue

Which is perfectly legal syntax - it sets the value of one variable to the value of another, where the value of @whereColumn (the actual value of the variable, not the value of that colume in {table} is equal to the value of the variable @whereValue.

Since @whereColumn is most likely NOT equal to @whereValue, nothing changes.

That is why adding the column names to the query as text rather than parameters gets it to work. It changes your query to

UPDATE {table} 
SET {columnName} = @columnValue
WHERE {whereColumn} = @whereValue

which does reference the columns in the table, not parameter values.

Update query in C# executes, but doesn't update in SQL

2 Answers2