I am getting syntax error in c# application in inserting values using Where clause

Question

    private void button1_Click(object sender, EventArgs e)
    {
        using (SQLiteConnection con = new 
    SQLiteConnection(connectionstring))
        {
            SQLiteCommand cmd = new SQLiteCommand(connectionstring);
            string CMM = "INSERT INTO ALARM  (ALARM_ID, ALARM ) SELECT 
       1,'TEMP' WHERE NOT EXISTS(SELECT FLAG FROM ALARM WHERE FLAG = 0)";

            cmd.CommandText = CMM;
            cmd.Connection = con;
            con.Open();

            cmd.ExecuteNonQuery();
            }

i am getting this error near "SELECT": syntax error i could not resolve this issue . this query is running fine in sqlite browser.Please Help me out Thanks in advance

Could you give details about the *exact* error message? Or is it really just "Syntax error near SELECT"? — Jon Skeet, May 16 '18 at 06:57
Define the string as `Verbatim string literals`. So prefix `@`. Like `@"INSERT INTO ALARM (ALARM_ID, ALARM ) SELECT 1,'TEMP' WHERE NOT EXISTS(SELECT FLAG FROM ALARM WHERE FLAG = 0)"` — user1672994, May 16 '18 at 07:04
Are you sure you are using sql lite and not sqlserver? your query looks identical to: https://stackoverflow.com/a/19337206/1275832 — Joel Harkes, May 16 '18 at 07:06
Don't know if it's correct, but let's put a space after `Exists` after `(` .:) — user1672994, May 16 '18 at 07:11
`SQLiteCommand` is initialized with connection string instead of command text. Update the line as `SQLiteCommand cmd = new SQLiteCommand(CMM, con)`; And remove the `cmd.CommandText = CMM; cmd.Connection = con; con.Open();`. Read this doc for SQLLiteCommand object definition -- https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqlitecommand.-ctor?view=msdata-sqlite-2.0.0#Microsoft_Data_Sqlite_SqliteCommand__ctor_System_String_ — user1672994, May 16 '18 at 07:18
Can you please write the whole code because in this SQLiteCommand cmd = new SQLiteCommand(CMM, con) i have given connectionstring of databse path — R-T, May 16 '18 at 07:25

score 1 · Answer 1 · answered May 16 '18 at 06:56

1

Your query seems to miss a from where you get the data from:

INSERT INTO ALARM  (ALARM_ID, ALARM ) 
  SELECT    1,'TEMP' 
   FROM somehwere -- you where missing this.
   WHERE NOT EXISTS(SELECT FLAG FROM ALARM WHERE FLAG = 0)

Or you just want to insert a single set of values:

INSERT INTO ALARM  (ALARM_ID, ALARM ) 
VALUES (1, 'TEMP')

answered May 16 '18 at 06:56

Joel Harkes

10,975
3
46
65

Query insert values if the FLAG value is = 0 FLAG is another column .INSERT INTO ALARM (ALARM_ID, ALARM ) SELECT 1,'TEMP' from ALARM WHERE NOT EXISTS(SELECT FLAG FROM ALARM WHERE FLAG = 0) i tried this but getting same error error near "SELECT": syntax error – R-T May 16 '18 at 06:59
It's not correct, why the query require from clause when constant values are being inserted? Also, SO mentioned that query works in sqllite browser. – user1672994 May 16 '18 at 07:00
@user1672994 maybe user wants to insert x amount of values from other table, seems illogical but still it could be the case. – Joel Harkes May 16 '18 at 07:01
insert into ALARM (ALARMID, ALARM ) values (1,'TEMP') WHERE NOT EXISTS(select FLAG from ALARM where FLAG='0') This query also not working error near "WHERE": syntax error – R-T May 16 '18 at 07:04

I am getting syntax error in c# application in inserting values using Where clause

1 Answers1