Android SQLiteDatabase Insert Syntax Error

Question

I've searched around and found people with similar issues, yet not quite the same. I'm receiving an SQLite syntax error, but I cannot locate the bad syntax. Please help!

I created an SQLite Database in DatabaseAdapter.java, seen here:

static class DatabaseHelper extends SQLiteOpenHelper{
        private static final String DATABASE_NAME = "mealdatabase";
        private static final String TABLE_INGREDIENT = "INGREDIENTS";
        private static final int DATABASE_VERSION = 1;
        private static final String UID = "_id";
        private static final String INGREDIENT_NAME = "Ingredient Name";
        private static final String SERVING_UNITS = "Serving Units";
        private static final String SPC = "Servings Per Container";
        private static final String CREATE_TABLE_INGREDIENT = "CREATE TABLE "+TABLE_INGREDIENT+
                " ("+UID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+INGREDIENT_NAME+" VARCHAR(255), "
                +SERVING_UNITS+" VARCHAR(255), "+SPC+" VARCHAR(255));";

        private static final String DROP_TABLE_INGREDIENT = "DROP TABLE IF EXISTS "+TABLE_INGREDIENT;
        private Context context;

        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            this.context=context;
            Message.message(context, "Constructor Called");
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            try {
                db.execSQL(CREATE_TABLE_INGREDIENT);
                Message.message(context, "onCreate Called");
            } catch (SQLException e) {
                Message.message(context, ""+e);
            }

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            try {
                Message.message(context, "onUpgrade Called");
                db.execSQL(DROP_TABLE_INGREDIENT);
                //context.deleteDatabase(DATABASE_NAME);
                onCreate(db);
            } catch (SQLException e) {
                Message.message(context, ""+e);
            }

        }
    }

I'm trying to insert new data into an existing SQLite Database. The database was created with no errors. However, when my insert function is called, I get the following syntax error:

03-06 20:44:27.559: E/SQLiteLog(968): (1) near "Per": syntax error
03-06 20:44:27.619: E/SQLiteDatabase(968): Error inserting Servings Per Container=8.0 Serving Units=oz Ingredient Name=Spaghetti Noodle

Here's where I call the insert function that is in NewIngredient.java:

public void saveIngredientData() {

        try {
            String ingredient = ingredient_editText.getText().toString();
            String servingUnits = spinner.getSelectedItem().toString();
            double servPerContainer = Double.parseDouble(SPC_editText.getText().toString());

            long id = databaseHelper.insertIngredientData(ingredient, servingUnits, servPerContainer);
            if (id < 0)
            {
                Message.message(this, "Insert was Unsuccessful");
            }
            else
                Message.message(this, "Successfully entered a row");
        } catch (NumberFormatException e) {
            Message.message(getApplicationContext(), ""+e);
        }

And here's the insertIngredientData function that contains a syntax error, located in DatabaseAdapter.java:

public long insertIngredientData(String ingredientName, String units, double spc){
    SQLiteDatabase db = helper.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(DatabaseHelper.INGREDIENT_NAME, ingredientName);
    contentValues.put(DatabaseHelper.SERVING_UNITS, units);
    contentValues.put(DatabaseHelper.SPC, spc);
    long id = db.insert(DatabaseHelper.TABLE_INGREDIENT, null, contentValues);
    db.close();
    return id;
}

Phantômaxx · Accepted Answer · 2015-03-07 08:09:21Z

1

You are using spaces in field names.

You better use underscores (_) instead. I.e.: private static final String SPC = "Servings_Per_Container";.
OR (if you really want to insist using spaces), you must include the field names in brackets ([ and ]). I.e.: private static final String SPC = "[Servings Per Container]";.

Correct ALL your field names, not only the one in the example.

answered Mar 7, 2015 at 8:09

Phantômaxx

38.1k21 gold badges88 silver badges122 bronze badges

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

2 Comments

Luke Stuemke Over a year ago

Worked like a charm. I don't know why it took me so long to see that. Thanks!

Phantômaxx Over a year ago

Perfect. Now it would be good if you accept my answer, in order to remove this post from the Unanswered Question Queue.

Collectives™ on Stack Overflow

Android SQLiteDatabase Insert Syntax Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related