Syntax error in SQLite.net when passing column name as parameter

Question

I am trying to rename a field in my database through SQLite.net and am getting a syntax error. My SQL that I have tested is:

UPDATE candy SET name="Godiva" WHERE name="Sees";

and that works fine. I am using an SQLiteCommand with a CommandText of:

UPDATE candy SET @field_name=@new_name WHERE @field_name=@old_name;

and my code snippet that sets the values looks like this:

Connection.Open();
transaction = Connection.BeginTransaction();
UpdatePropertyQuery.Parameters.AddWithValue( "@field_name", "name");
UpdatePropertyQuery.Parameters.AddWithValue( "@old_name", "Sees");
UpdatePropertyQuery.Parameters.AddWithValue( "@new_name", "Godiva");
UpdatePropertyQuery.ExecuteNonQuery();
transaction.Commit();
Connection.Close();

I am no database expert, so from a newbie's standpoint, this seems like it should work, but maybe it's something with SQL in general that I'm not understanding. What's the correct way to do this, and can anyone explain why this approach wouldn't work?

Answer 1

Typically statements like that are only possible when you use parameters that relate to actual formal parameters in the underlying query i.e:

UPDATE candy SET name=@new_name WHERE name=@old_name;

The idea being that all database objects like table and field names must be stated implicitly in the statement, so that the underlying database access library can perform checks along the lines of "is the value given for candy.name of the correct type".

In your example the field name is not known in advance and so these types of checks are not possible.

Some more info here.

If the field name is variable, you can just build a string containing the SQL and execute it directly, or adjust what you have to include the field name in the command text.