no such column: _id SQLite error Android.

I'm getting a no such column: _id error. YES there is a column "_id" in my journals table.

I'm getting the error with this code:

 mDb.execSQL("UPDATE "+DATABASE_PLANTS_TABLE+" SET "+ KEY_PLANT_DAYS+ 
            " = (SELECT COUNT(*) FROM "+DATABASE_PLANTS_TABLE+" WHERE "
              +DATABASE_JOURNAL_TABLE+"."+KEY_JROWID+" = "+DATABASE_PLANTS_TABLE +"."
                        +KEY_PLANT_HOMEID+")");

The following code DOES work:

mDb.execSQL("UPDATE "+DATABASE_JOURNAL_TABLE+" SET "+KEY_JOURNAL_PLANTS+
                    " = (SELECT COUNT(*) FROM "+DATABASE_PLANTS_TABLE+" WHERE "
                    +DATABASE_JOURNAL_TABLE+"."+KEY_JROWID+" = "+DATABASE_PLANTS_TABLE +"."
                    +KEY_PLANT_HOMEID+")");

LogCat:

08-21 00:07:46.614: ERROR/Database(12943): Failure 1 (no such column: journals._id) on 0x421470 when preparing 'UPDATE plants SET days = (SELECT COUNT(*) FROM plants WHERE journals._id = plants.homeid)'.
08-21 00:07:46.614: DEBUG/AndroidRuntime(12943): Shutting down VM
08-21 00:07:46.614: WARN/dalvikvm(12943): threadid=3: thread exiting with uncaught exception (group=0x4001b390)
08-21 00:07:46.614: ERROR/AndroidRuntime(12943): Uncaught handler: thread main exiting due to uncaught exception
08-21 00:07:46.624: ERROR/AndroidRuntime(12943): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.growjournal.beta/com.grower.beta.plantsList}: android.database.sqlite.SQLiteException: no such column: journals._id: UPDATE plants SET days = (SELECT COUNT(*) FROM plants WHERE journals._id = plants.homeid)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2596)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2621)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at android.app.ActivityThread.access$2200(ActivityThread.java:126)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1932)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at android.os.Handler.dispatchMessage(Handler.java:99)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at android.os.Looper.loop(Looper.java:123)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at android.app.ActivityThread.main(ActivityThread.java:4595)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at java.lang.reflect.Method.invokeNative(Native Method)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at java.lang.reflect.Method.invoke(Method.java:521)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at dalvik.system.NativeStart.main(Native Method)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943): Caused by: android.database.sqlite.SQLiteException: no such column: journals._id: UPDATE plants SET days = (SELECT COUNT(*) FROM plants WHERE journals._id = plants.homeid)
08-21 00:07:46.624: ERROR/AndroidRuntime(12943):     at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method)

Not sure what you mean by this. In the code that does work i reference two separate tables.

Brian 2010-08-21 05:07:42

No, you don't. Like I said, FROM determines which table you access. You said "FROM plants", so you will only be able to access data from the table "plants". Anything from "journals" will be completely inaccessible to you. You need to use a JOIN statement, or have two tables in your FROM part.

EboMike 2010-08-21 06:41:54

while this is the correct anwser could you explain why i am still able to compare to the plants table in the WHERE statment?

Brian 2010-08-21 19:58:56

The second approach has the journal table in the outer part of the query (the UPDATE) and the plants table in the inner part (the SELECT), so both tables are being accessed. In UPDATE statements, the table after your UPDATE directive specifies the table being read and modified.

EboMike 2010-08-22 07:13:48

ansaurus

tags:

views:

answers:

no such column: _id SQLite error Android.

related questions