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.

Solution 1

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.

Solution 2

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

Solution 3

error is throw by create table, not get data.

check your create table sql statement

Comments

[secre.swallowtail]

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.