0

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?

Gilad Green
  • 36,708
  • 7
  • 61
  • 95
ja13
  • 89
  • 1
  • 8

1 Answers1

3

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("\"", "\"\"") + "\"";
dan04
  • 87,747
  • 23
  • 163
  • 198
  • 1
    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