What is wrong with this sqllite alter table query?

Question

I need to add column to table, for now I have this code:

public void InsertParameter(string ColumnName)
{
    string sql = "ALTER TABLE table_name ADD :value1";

    SQLiteCommand cmd = new SQLiteCommand(sql,conn);

    cmd.Parameters.AddWithValue("value1", ColumnName);

    cmd.ExecuteNonQuery();
}

But this give me syntax error:

near ":value1":syntax error

I really can't figure out what is wrong with this query?

Possible duplicate of [Insert new column into table in sqlite ?](http://stackoverflow.com/questions/4253804/insert-new-column-into-table-in-sqlite) — Gilad Green, Oct 04 '16 at 22:54

score 3 · Accepted Answer · answered Oct 04 '16 at 23:40

The reason it doesn't work is that the syntax for SQLite's ALTER TABLE statement requires a column-name here instead of an arbitrary string-typed expr. This means you can't use a bind-parameter with it.

(Apparently, the implementation of prepared statements requires the table and column names to be known at “compile” time, so it can't be a variable.)

If you need a C# function that dynamically selects a column name at runtime, you need to dynamically create the SQL statement with a hard-coded column name. (Use double-quoting to prevent SQL injection attacks.)

string sql = "Alter Table table_name ADD "\"" + ColumnName.Replace("\"", "\"\"") + "\"";

I removed my answer since you posted yours from the comments :-) This is much more clear and explains the approach. Thank you for the useful info! — vanlee1987, Oct 04 '16 at 23:45

What is wrong with this sqllite alter table query?

1 Answers1