CODEWITHSUNDEEP
c#sqlitesystem.data.sqlite
Rev
Rev

Reputation: 6132

Setting DEFAULT value using SQLiteParameter

My query is supposed to add an additional column "_DisableAccounting" to the "Settings_Global" table, specifying a DEFAULT value using a SQLiteParemeter.

ALTER TABLE Settings_Global ADD COLUMN `_DisableAccounting` BOOL  NOT NULL  DEFAULT @defaultValue;

When the SQLiteCommand is executed, @defaultValue should be replaced by the supplied parameter.

// ....
SQLiteParameter defaultValueParam = null;

if (defaultValue != null) {
    query += " DEFAULT @defaultValue`";
    defaultValueParam = new SQLiteParameter { ParameterName = "@defaultValue", Value = defaultValue };
}

query += ";";

using (SQLiteCommand cmd = _Connection.CreateCommand()) {
    cmd.CommandText = query;

    if (defaultValueParam != null) {
        cmd.Parameters.Add(defaultValueParam);
    }

    cmd.ExecuteNonQuery();
}

// ....

However, I get the error "'SQL logic error or missing database near @defaultValue": syntax error'". So it seems that the command is never replacing @defaultValue with the actual value.

Why isn't this working? I am doing essentially the same with MySQL (my application can optionally use MySQL) and it is working.

Upvotes: 1

Views: 390

Answers (2)

CL.
CL.

Reputation: 180310

When doing ALTER TABLE, SQLite does not modify the column definition in any way and just inserts it directly at the end of the CREATE TABLE statement. This means that the parameter marker would end up in that CREATE TABLE statement, which would result in the parameter value not being available when the statement is interpreted later.

You have to insert the default value directly into the SQL command.

Upvotes: 1

MikeT
MikeT

Reputation: 57103

You appear to have a spurious grave accent. That is @defaultValue is followed grave accent, when it likely shouldn't be.

Try changing 

ALTER TABLE Settings_Global ADD COLUMN `_DisableAccounting` BOOL  NOT NULL  DEFAULT @defaultValue`;

to 

ALTER TABLE Settings_Global ADD COLUMN `_DisableAccounting` BOOL  NOT NULL  DEFAULT @defaultValue;

or to

ALTER TABLE Settings_Global ADD COLUMN `_DisableAccounting` BOOL  NOT NULL  DEFAULT `@defaultValue`;

Upvotes: 1

Related Questions