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?

Answer 1

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

Answer 2

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);