android.database.sqlite.SQLiteException: near " ": syntax error

Question

I'm having a problem while adding records in SQLite.

This is the Error:

09-18 17:47:47.586: E/AndroidRuntime(1039):
   java.lang.RuntimeException: Unable to start activity ComponentInfo{com.capstone.pinoygoodies/com.capstone.pinoygoodies.GroceryView}:
   android.database.sqlite.SQLiteException: near " ":
   syntax error:
   CREATE TABLE tblItem 
                 (_id INTEGER PRIMARY KEY AUTOINCREMENT,
                  grocery_item TEXT NOT NULL,
                  grocery_qty TEXT NOT NULL

My CREATE TABLE

db.execSQL("CREATE TABLE " + DATABASE_TABLE + "(" +
KEY_ITEMID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
KEY_ITEM + " TEXT NOT NULL, " + 
KEY_QTY + " TEXT NOT NULL ");

Whenever I hit the add button, this error is being triggered.

you are missing the ) at the end. You need to include it too — G_S
– G_S, Commented Sep 19, 2012 at 5:12

Greg Giacovelli · Accepted Answer · 2012-09-19 05:11:44Z

7

db.execSQL("
    CREATE TABLE " + DATABASE_TABLE + "(" +
    KEY_ITEMID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
    KEY_ITEM + " TEXT NOT NULL, " + 
    KEY_QTY + " TEXT NOT NULL );"
);

There are easy to mix up. You just need the close parenthesis.

Suggestion:

String createStatement = 
    String.format("CREATE TABLE %s ( %s INTEGER PRIMARY KEY
                   AUTOINCREMENT, %s TEXT NOT NULL, 
                   %s TEXT NOT NULL);",
                        DATABASE_TABLE,
                        KEY_ITEMID,
                        KEY_ITEM,
                        KEY_QTY);

If you construct your table like this, I personally think it makes the statement much easier to read for things like SQL Syntax and then you can bind the data later.

answered Sep 19, 2012 at 5:11

Greg Giacovelli

10.2k2 gold badges49 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jeet · Accepted Answer · 2012-09-19 04:56:57Z

3

Append ")" at the end of Create Table Query.

answered Sep 19, 2012 at 4:56

jeet

29.2k6 gold badges55 silver badges54 bronze badges

Comments

Mike Wei · Accepted Answer · 2012-09-19 04:52:08Z

1

error is throw by create table, not get data.

check your create table sql statement

answered Sep 19, 2012 at 4:52

Mike Wei

3472 silver badges6 bronze badges

Comments

Lucifer · Accepted Answer · 2012-09-19 05:11:55Z

0

You are missing the right parenthesis of the Create Table Syntax, correct it as follows,

CREATE TABLE tblItem 
                 (_id INTEGER PRIMARY KEY AUTOINCREMENT,
                  grocery_item TEXT NOT NULL,
                  grocery_qty TEXT NOT NULL );

answered Sep 19, 2012 at 5:11

Lucifer

29.7k25 gold badges93 silver badges144 bronze badges

Comments

V.J. · Accepted Answer · 2012-09-19 05:36:06Z

0

db.execSQL("CREATE TABLE " + DATABASE_TABLE + "(" +
KEY_ITEMID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
KEY_ITEM + " TEXT NOT NULL, " + 
KEY_QTY + " TEXT NOT NULL )");

You need to add ")" at last in your create table.

Insert Query

    String qty = "QTY";
    String item = "ITEM";
    String sql = "INSERT or replace INTO "+ DATABASE_TABLE +" (qty, item) VALUES('"+ qty +"','"+ item + "')";
    db.execSQL(sql);

edited Sep 19, 2012 at 5:36

answered Sep 19, 2012 at 5:12

V.J.

9,6004 gold badges36 silver badges50 bronze badges

1 Comment

secre.swallowtail Over a year ago

This works fine with me but I'm having new problem now. android.database.sqlite.SQLiteException: table tblItem has no column named qty: , while compiling: INSERT INTO tblItem(qty, item) VALUES(?, ?); I have declared the column for qty.

Collectives™ on Stack Overflow

android.database.sqlite.SQLiteException: near " ": syntax error

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related