java.lang.IllegalArgumentException: column '_id' does not exist

Question

I'm trying to debug my application on a real device but I get this error:

ERROR/AndroidRuntime(981): Caused by: java.lang.IllegalArgumentException: column '_id' does not exist

When I'm testing on an emulator, the error doesn't appear. The error is given in the last line of the following code:

adapter = new SimpleCursorAdapter(this, R.layout.list_item, c, new String[] {   
            DataHandlerDB.CONTACT_NAME_COL,
            DataHandlerDB.CONTACT_NUMBER_COL,
            DataHandlerDB.CONTACT_DURATION_COL,
            DataHandlerDB.CONTACT_DATE_COL }, new int[] {
            R.id.contact_name, R.id.phone_number, R.id.duration, R.id.date });

Here is my activity:

public class MyActivity extends Activity {

    private static final String LOG_TAG = "MyActivity";
    private ListView listview;
    private SimpleCursorAdapter adapter;        
    private DataHandlerDB handler;
    private SQLiteDatabase db;
    private OpenHelper helper;
    private Cursor c;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);     
        setContentView(R.layout.main);

        helper = new OpenHelper(this);
        db = helper.getWritableDatabase();
        helper.onCreate(db);
        setBasicContent();
        c.close();
    }   


    @Override
    public void onDestroy(){

        super.onDestroy();
        DataHandlerDB.makeTheSelection(this).close();
        db.close();
        helper.close();

    }

    @Override
    public void onPause(){

        super.onPause();
        DataHandlerDB.makeTheSelection(this).close();
        db.close();
        helper.close();

    }

    @Override
    public void onStop(){

        super.onStop();
        DataHandlerDB.makeTheSelection(this).close();
        db.close();
        helper.close();

    }


    @Override
    protected void onResume(){

        super.onResume();
        setBasicContent();

    }   

    public void setBasicContent() {

        listview = (ListView) findViewById(R.id.list_view); 

        Log.i(LOG_TAG, "listview " + listview);

        c = DataHandlerDB.makeTheSelection(this);

        c.moveToFirst();

        if(db.isOpen())
            Log.i(LOG_TAG, "db is opened");

        Log.i(LOG_TAG, "cursor: " + c.getCount());

        startManagingCursor(c);

        adapter = new SimpleCursorAdapter(this, R.layout.list_item, c, new String[] {   
                DataHandlerDB.CONTACT_NAME_COL,
                DataHandlerDB.CONTACT_NUMBER_COL,
                DataHandlerDB.CONTACT_DURATION_COL,
                DataHandlerDB.CONTACT_DATE_COL }, new int[] {
                R.id.contact_name, R.id.phone_number, R.id.duration, R.id.date });

        Log.i(LOG_TAG, "before setAdapter");
        Toast.makeText(this, "Before setAdapter", Toast.LENGTH_SHORT).show();

        listview.setAdapter(adapter);

        db.close();

        if(db.isOpen()){

            Log.i(LOG_TAG, "db is opened.");

        }

        if(!c.isClosed()){

            Log.i(LOG_TAG, "cursor is opened");

        }           
    }       
}

The function that queries and returns the Cursor is in the class DataHandlerDB:

public class DataHandlerDB {

private static final String DATABASE_NAME = "calls.db";
private static final int DATABASE_VERSION = 1;

protected static String CONTACT_NAME_COL = "contact_name";
protected static String CONTACT_NUMBER_COL = "contact_number";
protected static String CONTACT_DURATION_COL = "duration";
protected static String CONTACT_DATE_COL = "date";
protected static String CONTACT_MONTH_COL = "month";

// create the DB
public static SQLiteDatabase createDB(Context ctx) {
    OpenHelper helper = new OpenHelper(ctx);
    SQLiteDatabase db = helper.getWritableDatabase();
    helper.onCreate(db);
    helper.onOpen(db);
    db.close();
    return db;
}

public static Cursor makeTheSelection(Context ctx) {

    OpenHelper helper = new OpenHelper(ctx);
    SQLiteDatabase db = helper.getWritableDatabase();

    Cursor cursor = db.query(TABLE_NAME_2, null, null, null, null, null,
            "duration desc");

    cursor.moveToFirst();
    db.close();

    return cursor;
}
    // class OpenHelper
public static class OpenHelper extends SQLiteOpenHelper {

    private final Context mContext;

    OpenHelper(Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.mContext = context;

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(LOG_TAG, "entrou no onCreate");
        String[] sql = mContext.getString(
                R.string.MyAppDatabase_OnCreate).split("\n");

        db.beginTransaction();

        try {
            execMultipleSQL(db, sql);
            db.setTransactionSuccessful();
        } catch (SQLException e) {

            Log.e("Error creating tables and debug data", e.toString());
            throw e;

        } finally {
            db.endTransaction();

        }
    }

    private void execMultipleSQL(SQLiteDatabase db, String[] sql) {

        for (String s : sql) {

            if (s.trim().length() > 0) {

                db.execSQL(s);
            }
        }

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

         Log.w("MyDB Database",
         "Upgrading database, this will drop tables and recreate.");
         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db);

    }

    @Override
    public void onOpen(SQLiteDatabase db) {

        super.onOpen(db);
    }
}
}

Here is the XML file with the SQL command:

<string name="MyAppDatabase_OnCreate">
    "CREATE TABLE IF NOT EXISTS contact_data(_id INTEGER PRIMARY KEY AUTOINCREMENT, contact_id INTEGER, contact_name VARCHAR(50), number_type VARCHAR(50), contact_number VARCHAR(50), duration TIME, duration_sum TIME, date DATE, current_time TIME, cont INTEGER, type VARCHAR, month VARCHAR(50), day VARCHAR(50), year VARCHAR(50));"
</string>

I think the application is not creating the database when it first starts up. I think so because it can find the column _id, but it is explicitly written in the XML code to create it with the _id column. I also think that because I've explicitly written the columns in the SELECT method, including the _id. I did it like this:

Cursor cursor = db.query(TABLE_NAME_2, 
                new String[]{
                "_id", 
                "contact_id", 
                "contact_name", 
                "number_type", 
                "contact_number", 
                "duration", 
                "duration_sum", 
                "date", 
                "current_time", 
                "cont", "type", 
                "month", 
                "day", 
                "year"}, null, null, null, null,
                "duration desc");

In this case, the error I receive is almost the same:

Caused by: android.database.sqlite.SQLiteException: no such column: _id: , while compiling: SELECT _id, contact_id, contact_name, number_type, contact_number, duration, duration_sum, date, current_time, cont, type, month, day, year FROM contact_data ORDER BY duration desc

I've logged the first column of the database like so:

Log.i(LOG_TAG, "Cursor(0)" + cursor.getColumnName(0));

It printed id, not _id. As you can see, there is _id written in the statement. Any suggestions on how to solve this problem?

Uninstall the app fully from the device (e.g., Settings > Applications > Manage Applications), and try again. I suspect that your database already existed without the _id column. — CommonsWare
– CommonsWare, Commented Apr 27, 2011 at 23:56
@CommonsWare thank god for this answer... I thought it was a coding problem, but it was such a trivial case! — Bao Thai
– Bao Thai, Commented Mar 18, 2018 at 18:41

jtt · Accepted Answer · 2016-02-23 18:59:43Z

45

You are trying to use a cursor that REQUIRES a column called _id. Its as simple as editing your table creation statement and adding a column called _id.

Its declartion looks something like this:

_id INTEGER PRIMARY KEY AUTOINCREMENT

Add this and you will then be able to use it. I believe this is a requirement that is required in order to use a SimpleCursorAdapter.

UPDATE

"CREATE TABLE IF NOT EXISTS contact_data( _id INTEGER PRIMARY KEY AUTOINCREMENT, contact_id INTEGER, contact_name VARCHAR(50), number_type VARCHAR(50), contact_number VARCHAR(50), duration TIME, duration_sum TIME, date DATE, current_time TIME, cont INTEGER, type VARCHAR, month VARCHAR(50), day VARCHAR(50), year VARCHAR(50));"

Solution: add a space between the left parenthesis '(' and _id

edited Feb 23, 2016 at 18:59

answered Apr 27, 2011 at 23:58

jtt

13.6k6 gold badges35 silver badges45 bronze badges

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

3 Comments

tread Over a year ago

CursorAdapter requires a column called _id always.

Joan Casadellà Over a year ago

You can use alias instead of create it! If you already have your _id column but has other name, simply change your primary key name using AS in your sql query: { SELECT myprimarykey AS _id, name FROM Table WHERE name LIKE '%queryfilter%'; }

Prof Over a year ago

Building on @jcasadellaoller's point: db.query("tableName", new String[]{"current_id _id", ... This means you can use your existing table but the query will alias your "current_id" as the required "_id" column so it will be as if that column exists

dsharew · Accepted Answer · 2015-05-06 07:52:19Z

15

I have had similar problem because I was not adding the _id column to the projection argument, so adding _id to the projections argument of the query was the solution. (commented by @nobugs)

Example:

 String[] projections = {"_id", "name", "age"};

 Cursor cursor = db.query(domainClass.getSimpleName(), projections, 
     null, null, null, null, null);

answered May 6, 2015 at 7:52

dsharew

10.7k6 gold badges55 silver badges76 bronze badges

Comments

kwoxer · Accepted Answer · 2016-11-03 12:58:09Z

2

First of all uninstall the app, and then do following steps:

Clean the project
rebuild he project
debug the app(Shift+F9)

edited Nov 3, 2016 at 12:58

kwoxer

3,8615 gold badges45 silver badges76 bronze badges

answered Nov 3, 2016 at 11:34

sandhya

211 bronze badge

Comments

Devansh Maurya · Accepted Answer · 2018-08-25 14:59:34Z

0

CursorAdapter always requires a _id column to be present. So, in the list of projections used for creating Cursor, you need to add _id column.

answered Aug 25, 2018 at 14:59

Devansh Maurya

1,04213 silver badges15 bronze badges

Comments

Kifayat Ullah · Accepted Answer · 2020-03-01 15:43:04Z

0

I did this and solved my problem.

Before

SELECT id

After

SELECT id as _id

answered Mar 1, 2020 at 15:43

Kifayat Ullah

6191 gold badge6 silver badges14 bronze badges

Collectives™ on Stack Overflow

java.lang.IllegalArgumentException: column '_id' does not exist

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related