near "COLUMN_NAME": syntax error (code 1) in SQLite

Question

I'm Working on Android project and I'm using SQLite database in that, I have written one insert query as :

sqliteDB_Obj.execSQL("INSERT INTO tbl_order_master (user_id, order, amount)" + "VALUES(" + strUserId +","+ strOrderString +","+ dblAmount +");");

But I'm getting following error

android.database.sqlite.SQLiteException: near "order": syntax error (code 1): , while compiling: INSERT INTO tbl_order_master (user_id, order, amount) VALUES(dny, my test order, 160.0);

some people have asked same question before so according to the answer mentioned their I have tried following way :

String strTableName="tbl_order_master";
String strQuery= String.format("INSERT INTO %s (user_id, order, amount) VALUES(%s, %s, %s);", strTableName, strUserId, strOrderString, ""+dblAmount);
sqliteDB_Obj.execSQL(strQuery);

but still getting same error Please help.. Thanks..!

probably the problem is that the order name is a reserved word? try using sort_order or similar as column name or write it with backticks. — Olli, Jun 12 '14 at 12:07
[Sqlite keywords](http://www.sqlite.org/lang_keywords.html) and "order" is one of them — Pararth, Jun 12 '14 at 12:08
possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — vhu, Jun 12 '14 at 12:11

score 3 · Accepted Answer · answered Jun 12 '14 at 12:09

If you use a reserved name such as order as an identifier, put it in "double quotes". Or just rename the identifier so it isn't a reserved keyword.

Also in SQL, string literals need to be written in 'single quotes'. Yours are not quoted.

It's better to use parameters instead though, e.g.

execSQL("INSERT INTO tbl_order_master (\"user_id\", \"order\", \"amount\") VALUES (?, ?, ?)",
    new String[] { strUserId, strOrderString, Double.toString(dblAmount) } );

passing the parameter values in the second arg.

This was a life saver. Thanks! :) – Aman Alam Aug 05 '15 at 14:41 — Aman Alam, Aug 05 '15 at 14:41

Gabber · Answer 2 · 2014-06-12T12:15:52.670

1

try this

 sqliteDB_Obj.execSQL("INSERT INTO tbl_order_master (`user_id`, `order`, `amount`)" + "VALUES('" + strUserId +"','"+ strOrderString +"',"+ dblAmount +");");

change the column name "order" or using `order`

edited Jun 12 '14 at 12:15

answered Jun 12 '14 at 12:10

Gabber

7,169
3
32
46

score 0 · Answer 3 · answered Jun 12 '14 at 12:10

ORDER is reserved word in SQL so you can not use as field name

sqliteDB_Obj.execSQL("INSERT INTO tbl_order_master (user_id, order, amount)" + "VALUES(" + strUserId +","+ strOrderString +","+ dblAmount +");");

change to

sqliteDB_Obj.execSQL("INSERT INTO tbl_order_master (user_id, s_order, amount)" + "VALUES(" + strUserId +","+ strOrderString +","+ dblAmount +");");

score 0 · Answer 4 · answered Jun 12 '14 at 12:11

Try changing the name of your order column to something else. order is a reserved word in sqlite (i.e. order by).And Also single quotes are missing

So try this (Change order like m_order)

sqliteDB_Obj.execSQL("INSERT INTO tbl_order_master (user_id, m_order, amount)" + "VALUES('" + strUserId +"','"+ strOrderString +"','"+ dblAmount +"')");

score 0 · Answer 5 · answered Jun 12 '14 at 12:15

0

Try this. As a good practice, always use quotes to wrap string field values.

sqliteDB_Obj.execSQL("INSERT INTO tbl_order_master (user_id, order, amount) " + " VALUES ( '"    + strUserId +"', '"+ strOrderString +"',"+ dblAmount +") ");

answered Jun 12 '14 at 12:15

ngrashia

9,869
5
43
58

near "COLUMN_NAME": syntax error (code 1) in SQLite

5 Answers5