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

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?

Answers


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.


Need Your Help

ASP.Net MVC3 VB.Net Custom LabelFor HtmlHelper not working

vb.net asp.net-mvc-3 razor extension-methods html-helper

I am trying to create a custom LabelFor helper to apply by default instead of the standard LabelFor helper include in System.Web.Mvc.Html. I want my LabelFor to take model properties that are Pasc...

java - search sorted list of rectangles

java algorithm search 2d sortedlist

I have a list of Rectangles, created in the usual way with: