Android: android.database.sqlite.SQLiteException: no such column: _id: , while compiling: SELECT _id, category, summary, description, status FROM todo

Question

I'm new in android. I try to use fragments to implement a to-do-list app. Used a listadpater to display the listview. However, after I built the database, and used another datasource file to implement operations, one of these is getAllTodos(), then I came with the error: no column _id: And then I copy the code to a new project, used Activity to create the listview of datasource, it seems fine, no "unfortunately stopped". Here are my codes: first is onCreate in fragment class, second is datasource functions, third is my databaseHelper, last is the error.

These onCreate function seems have big problem.

        public View onCreateView(LayoutInflater inflater, ViewGroup container,
            Bundle savedInstanceState) {

        View rootView = inflater.inflate(R.layout.fragment_todo, container, false);
        listTask=(ListView) rootView.findViewById(android.R.id.list);


        return rootView;

    @Override
    public void onActivityCreated(Bundle savedInstanceState) {
        super.onActivityCreated(savedInstanceState);

        Activity activity = getActivity();

        if (activity != null) {
            // Create an instance of the custom adapter for the GridView. A static array of location data
            // is stored in the Application sub-class for this app. This data would normally come
            // from a database or a web service.
            datasource = new TodoDataSource(getActivity());
            datasource.open();

            todoList = datasource.getAllTodos();
            adapt= new MyCustomAdapter(getActivity(), R.layout.todo_list, todoList);
            //ListView listTask =(ListView) rootView.findViewById(android.R.id.list);
            listTask.setAdapter(adapt); 
        }
    }

Here is the datasource getAllTodos() and allColumns values

      private String[] allColumns = { 
                DatabaseHelper.COLUMN_ID, 
                DatabaseHelper.COLUMN_CATEGORY, 
                DatabaseHelper.COLUMN_SUMMARY, 
                DatabaseHelper.COLUMN_DESCRIPTION, 
                DatabaseHelper.COLUMN_STATUS };

            public List<Todo> getAllTodos() {
            List<Todo> todos = new ArrayList<Todo>();



            Cursor cursor = database.query(DatabaseHelper.TABLE_TODO, allColumns, null, null, null, null, null); // here came with the error

            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                Todo todo = cursorToTodo(cursor);
                todos.add(todo);
                cursor.moveToNext();
            }
            // Make sure to close the cursor
            cursor.close();
            return todos;
        }

public class DatabaseHelper extends SQLiteOpenHelper {

    // Database table
          public static final String TABLE_TODO = "todo";
          public static final String COLUMN_ID = "_id";
          public static final String COLUMN_CATEGORY = "category";
          public static final String COLUMN_SUMMARY = "summary";
          public static final String COLUMN_DESCRIPTION = "description";
          public static final String COLUMN_STATUS = "status";

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

    // Database creation SQL statement
          private static final String DATABASE_CREATE = "CREATE TABLE IF NOT EXISTS " 
              + TABLE_TODO
              + " (" 
              + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
              + COLUMN_CATEGORY + " TEXT NOT NULL, " 
              + COLUMN_SUMMARY + " TEXT NOT NULL, " 
              + COLUMN_DESCRIPTION + " TEXT, " 
              + COLUMN_STATUS + " INTEGER NOT NULL);";


      public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }

      // Method is called during creation of the database
      @Override
      public void onCreate(SQLiteDatabase database) {
          database.execSQL(DATABASE_CREATE);
      }

      // Method is called during an upgrade of the database,
      // e.g. if you increase the database version
      @Override
      public void onUpgrade(SQLiteDatabase database, int oldVersion,
          int newVersion) {
        //TodoTable.onUpgrade(database, oldVersion, newVersion);
          Log.w(DatabaseHelper.class.getName(),
                    "Upgrading database from version " + oldVersion + " to "
                            + newVersion + ", which will destroy all old data");
            database.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO);
            onCreate(database);
      }

}

error:

03-06 01:02:41.318: E/AndroidRuntime(9091): FATAL EXCEPTION: main
03-06 01:02:41.318: E/AndroidRuntime(9091): android.database.sqlite.SQLiteException: no such column: _id: , while compiling: SELECT _id, category, summary, description, status FROM todo
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:68)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:127)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:94)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:53)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1564)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1449)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1405)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1485)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at kidslist.sqlite.helper.TodoDataSource.getAllTodos(TodoDataSource.java:93)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at com.example.kidslist.TodoFragment.onActivityCreated(TodoFragment.java:68)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.app.FragmentManagerImpl.moveToState(FragmentManager.java:847)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.app.FragmentManagerImpl.moveToState(FragmentManager.java:1032)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.app.BackStackRecord.run(BackStackRecord.java:622)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1382)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.app.FragmentManagerImpl$1.run(FragmentManager.java:426)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.os.Handler.handleCallback(Handler.java:605)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.os.Handler.dispatchMessage(Handler.java:92)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.os.Looper.loop(Looper.java:137)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at android.app.ActivityThread.main(ActivityThread.java:4424)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at java.lang.reflect.Method.invokeNative(Native Method)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at java.lang.reflect.Method.invoke(Method.java:511)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:787)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:554)
03-06 01:02:41.318: E/AndroidRuntime(9091):     at dalvik.system.NativeStart.main(Native Method)

what is the value of allcolumns? – drulabs Mar 06 '15 at 01:40 — drulabs, Mar 06 '15 at 01:40

score 3 · Accepted Answer · answered Mar 06 '15 at 02:37

Did you add the column '_id' after running the apps (ie the database is already created without the column _id) ?

The easiest way is to clear app's data in Setting (If your apps has not yet been launched) to delete the database.

if your app has been launched already , you can try following

private static final int DATABASE_VERSION = 2; // indicate database update

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2)
        db.execSQL("ALTER TABLE "+ TABLE_TODO +" ADD "+ COLUMN_ID +" INTEGER PRIMARY KEY AUTOINCREMENT");
}

That's it, it seems I have changed column name after running on the phone. It really helps me, thank you, man! — Aprilyn, Mar 06 '15 at 03:43

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

In android's sqlite, there is a _id column which can not be omitted when you are using this db for adpters. Since you have not published your APP, you can consider add this column to your database.
You can refer this question for further research.
About "_id" field in Android SQLite

edited May 23 '17 at 11:45

Community

1
1

answered Mar 06 '15 at 03:14

Bob

34
5

Yes, I named COLUMN_ID as "todo_id" at first, then I changed it to" _id". Then phone has already created the table without "_id", where error comes. – Aprilyn Mar 06 '15 at 03:46

Android: android.database.sqlite.SQLiteException: no such column: _id: , while compiling: SELECT _id, category, summary, description, status FROM todo

2 Answers2