CODEWITHSUNDEEP
androidsqlite
Ryan Gray
Ryan Gray

Reputation: 824

Android SqLite no such column _id exception

Don't immediately flag me for a duplicate question. My issue is different because I have a correctly formatted SQL query.

public static final String TABLE_NAME = "log";
public static final String COLUMN_ID = "_id";
public static final String LOG_TEXT = "logtext";
private static final String TABLE_CREATE = "CREATE TABLE " + TABLE_NAME + " (" +
        COLUMN_ID + " integer primary key autoincrement, " +
        LOG_TEXT + " TEXT not null);";

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(TABLE_CREATE);

}

and I query here

String[] columns = {LOG_TEXT,COLUMN_ID};
    Cursor cursor = helper.getReadableDatabase().query(TABLE_NAME, columns, null, null, null, null, COLUMN_ID + " desc");

and I catch this the exception generated containing the sql query.

catch(Exception e){
Log.D("sql Exception",e.getMessage());}

and it returns

no such column: _id: , while compiling: SELECT logtext, _id FROM log ORDER BY _id desc

I'm familar with Oracle SQL and relational databases in general. Is it my ORDER BY clause? I was certain you can ALWAYS use order by. It doesn't have the same behavior as GROUP BY.

Any ideas on why the exception? Incase anyone wants to see i'm updating with my ArrayAdaptor statements. I'm using the cursor in a listview

String[] data = query();


    adapter = new ArrayAdapter<String>(this,
            android.R.layout.simple_list_item_1, android.R.id.text1, data);
    listView.setAdapter(adapter);}

Upvotes: 1

Views: 4355

Answers (3)

seBaka28
seBaka28

Reputation: 960

Had the same problem, meaning it should have worked but didn't (had some typos in the create command that I fixed but that still didn't help). A colleague then told me to try clearing the data (just at AppInfo and then "Clear Data") which solved my problem, apparently the old database (that didn't work) was still there and had to be cleared out first.

I just put this answer here in case anybody else like me (android beginner) stumbles across this problem, because I went through dozens of stackoverflow threads with this problem but not one offered this possibility/solution and it bothered me for quite some time.

Upvotes: 2

Sam
Sam

Reputation: 86948

Rewrite

Whenever you change the schema in TABLE_CREATE you must inform you app of these changes, they will not happen automatically when you change TABLE_CREATE. The easiest way to do this is to increment your database_version in your extended SQLiteOpenHelper class. You discovered you can also uninstall / reinstall the app, for the same results. If you are savvy with SQL you could ALTER the table. But whatever the method you must make sure that you app makes the schema changes before trying to access the new columns...

Also for SQLite: 

_id integer primary key

is synonymous with:

_id integer primary key autoincrement not null

And queries use descending as the default order, so ORDER BY _id is the same as ORDER BY _id DESC.

Upvotes: 5

devunwired
devunwired

Reputation: 63293

Did you add the definition of the _id column to your create statement later on, i.e. after the code had already been run once? Databases are persisted files, so if you modify the table structure in your code you need to make sure you clear your application's data so the database file can ge re-created with the correct table/column data.

Upvotes: 1

Related Questions