CODEWITHSUNDEEP
javaandroidsqlite
Ross
Ross

Reputation: 112

Android SQLiteException: no such column when executing raw INSERT

I'm not sure why I'm getting this error:

android.database.sqlite.SQLiteException: no such column: ExampleIngrd (code 1): , while compiling: INSERT INTO ingredients(_ingredient_id, _ingredient) VALUES (2, ExampleIngrd );

This is my database create statement and I've done the onUpgrade override correctly with new db version number:

private static final String DATABASE_CREATE = "create table "
    + TABLE_INGREDIENTS + "(" + COLUMN_ID
    + " integer primary key autoincrement, " + COLUMN_INGREDIENT_ID + " integer not null, " + COLUMN_INGREDIENT
    + " TEXT);";

then I execute db.execSQL(DATABASE_CREATE);

This all executes normally but but then when I try to insert into the table with this raw query:

public String createIngredient(String ingredientName, int ingredientID) {
    Cursor cursor = database.rawQuery("INSERT INTO "+MySQLiteHelper.TABLE_INGREDIENTS+"("+MySQLiteHelper.COLUMN_INGREDIENT_ID+", "
        +MySQLiteHelper.COLUMN_INGREDIENT+") VALUES ("
        +ingredientID+", "+ingredientName+");", null);
    cursor.moveToFirst();
    Ingredient newIngredient = cursorToIngredient(cursor);
    cursor.close();
    return newIngredient.getName();
}

I get the error no such column exists for whatever value in ingredientName, I'm not really sure what I'm doing wrong here. Is there some mistake in my SQL that I missed? Thanks.

Upvotes: 0

Views: 338

Answers (2)

Harry
Harry

Reputation: 422

SQL Query string in rawQuery() should not be ; terminated. Check below link. http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery(java.lang.String, java.lang.String[], android.os.CancellationSignal)

Remove ; from your query string.

Upvotes: -1

laalto
laalto

Reputation: 152927

You need to quote your literals in SQL in 'single quotes', or better yet, use parameters:

Cursor cursor = database.rawQuery("INSERT INTO "+MySQLiteHelper.TABLE_INGREDIENTS+"("+MySQLiteHelper.COLUMN_INGREDIENT_ID+", "
    +MySQLiteHelper.COLUMN_INGREDIENT+") VALUES (?, ?);",
    new String[] { String.valueOf(ingredientID), ingredientName });

Without the quotes the string is taken as an identifier e.g. column name.

Also, while using rawQuery() like this technically works, it's confusing. Use execSQL() for raw queries that don't return values.

Upvotes: 2

Related Questions