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?

Alex K. · Accepted Answer · 2010-04-15 16:24:27Z

2

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.

answered Apr 15, 2010 at 16:24

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dave Over a year ago

ok, thank you for the information. I was trying to maximize performance by using parameterized queries, but I guess parameterizing the column name isn't possible. I need to read up on this stuff.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related