Unable to display database in ListView

Question

I am trying to get my application to display information that is in a table I have set up in my database. From what I gather, the database is setup correctly and the code it's correct but I don't know what is causing the error. I will post all the code so to see if anyone here can help me figure is out.

Progress.class:

public class WorkoutProgress extends ListActivity {
   private DataBaseHelper datasource;
   TextView goal;
   @Override
   public void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      requestWindowFeature(Window.FEATURE_NO_TITLE);
      getWindow().setFlags(WindowManager.LayoutParams.FLAG_FULLSCREEN,WindowManager.LayoutParams.FLAG_FULLSCREEN);
      setContentView(R.layout.progress);
      goal = (TextView)findViewById(R.id.goal);
      datasource = new DataBaseHelper(this);
      datasource.open();
      fillData();
      datasource.close();
  }
  private void fillData() {
    Cursor c = datasource.getAllActs();
    startManagingCursor(c);
    String[] from = new String[] {DataBaseHelper.KEY_DATE,  
    DataBaseHelper.KEY_STEPS,DataBaseHelper.KEY_CALs };
    int[] to = { R.id.code, R.id.Days, R.id.BMI };
    SimpleCursorAdapter notes = new SimpleCursorAdapter (this, R.layout.notes_row, c,  from, to);
    setListAdapter(notes);               
  }
}

Database Helper:

public class DataBaseHelper
{
    public static final String KEY_ROWID = "_id";
    public static final String KEY_GOAL = "goal";
    public static final String KEY_Current = "cweight";
    public static final String KEY_Target = "nweight"; 

    public static final String KEY_ROWID2 = "_id";

    public static final String KEY_DATE = "date";
    public static final String KEY_STEPS = "steps";
    public static final String KEY_CALs = "calories";
    public static final String KEY_CALID = "_id";
    public static final String KEY_TOTALCALS = "totalcals";    
    private static final String TAG = "DBAdapter";
    private static final String DATABASE_NAME = "workout";
    private static final String DATABASE_TABLE = "goals";
    private static final String DATATABLE = "acts";
    private static final String CALTABLE = "cals";
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_CREATE =
     "create table goals (_id integer primary key autoincrement, "
     + "goal text not null, cweight number not null, " 
     + "nweight number not null);";
    private static final String DATABASE_CREATE2 =
        "create table acts (_id integer primary key autoincrement, "
                + "date text not null, steps text not null, " 
                + "calories number not null);"
    private final Context context; 
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;
    public DataBaseHelper(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
           super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE);
            db.execSQL(DATABASE_CREATE2);
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
        int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                + " to "
                + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS titles");
            onCreate(db);
        }
    }    
    public DataBaseHelper open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }
    public void close() 
    {
        DBHelper.close();
    }
    public long insertTitle(String goal, int current, int target) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_GOAL, goal);
        initialValues.put(KEY_Current, current);
        initialValues.put(KEY_Target, target);
        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    public long insertActivity(String date, String steps, double Calories) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_DATE, date);
        initialValues.put(KEY_STEPS, steps);
        initialValues.put(KEY_CALs, Calories);
        return db.insert(DATATABLE, null, initialValues);
    }
    public boolean deleteTitle(long rowId) 
    {
        return db.delete(DATABASE_TABLE, KEY_ROWID + 
            "=" + rowId, null) > 0;
    }
    public void deleteFirst()
    {
        Cursor cursor = db.query(DATABASE_TABLE, null, null, null, null, null, null); 
        if(cursor.moveToFirst()) {
        long rowId = cursor.getLong(cursor.getColumnIndex(KEY_ROWID)); 
        db.delete(DATABASE_TABLE, KEY_ROWID +  "=" + rowId, null);
   }
}

public boolean deleteAct(long rowId) 
{
    return db.delete(DATATABLE, KEY_ROWID + 
            "=" + rowId, null) > 0;
}
//---retrieves all the titles---
public Cursor getAllGoals() 
{
    return db.query(DATABASE_TABLE, new String[] {
            KEY_ROWID, 
            KEY_GOAL,
            KEY_Current,
            KEY_Target}, 
            null, 
            null, 
            null, 
            null, 
            null);
}
public Cursor getAllActs() 
{
    return db.query(DATATABLE, new String[] {

            KEY_ROWID2, 

            KEY_DATE,
            KEY_STEPS,
            KEY_CALs}, 
            null, 
            null, 
            null, 
            null, 
            null);
}

//---retrieves a particular title---
public Cursor getGoal
(long rowId) throws SQLException 
{
    Cursor mCursor =
            db.query(true, DATABASE_TABLE, new String[] {
                    KEY_ROWID,
                    KEY_GOAL, 
                    KEY_Current,
                    KEY_Target
                    }, 
                    KEY_ROWID + "=" + rowId, 
                    null,
                    null, 
                    null, 
                    null, 
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}
public Cursor getAct(long rowId) throws SQLException 
{
    Cursor mCursor =
            db.query(true, DATATABLE, new String[] {
                    KEY_ROWID2,
                    KEY_DATE, 
                    KEY_STEPS,
                    KEY_CALs
                    }, 
                    KEY_ROWID2 + "=" + rowId, 
                    null,
                    null, 
                    null, 
                    null, 
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}
public boolean updateAct(long rowId, String date, 
        String steps, String cals) 
        {
            ContentValues args = new ContentValues();
            args.put(KEY_DATE, date);
            args.put(KEY_STEPS, steps);
            args.put(KEY_CALs, cals);
            return db.update(DATATABLE, args, 
                             KEY_ROWID2 + "=" + rowId, null) > 0;
        }
/*public boolean updateCals(long rowId, double cals) 
        {
            ContentValues args = new ContentValues();
            args.put(KEY_TOTALCALS, cals);
            return db.update(CALTABLE, args, 
                             KEY_CALID + "=" + rowId, null) > 0;
        }*/

//---updates a title---
public boolean updateTitle(long rowId, String isbn, 
String title, String publisher) 
{
    ContentValues args = new ContentValues();
    args.put(KEY_GOAL, isbn);
    args.put(KEY_Current, title);
    args.put(KEY_Target, publisher);
    return db.update(DATABASE_TABLE, args, 
                     KEY_ROWID + "=" + rowId, null) > 0;
} 
}

Logcat:

05-26 14:42:03.895: E/AndroidRuntime(345): FATAL EXCEPTION: main
05-26 14:42:03.895: E/AndroidRuntime(345): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.b00348312.workout/com.b00348312.workout.WorkoutProgress}: android.database.sqlite.SQLiteException: no such column: _id: , while compiling: SELECT _id, date, steps, calories FROM acts
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2663)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.app.ActivityThread.access$2300(ActivityThread.java:125)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.os.Handler.dispatchMessage(Handler.java:99)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.os.Looper.loop(Looper.java:123)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.app.ActivityThread.main(ActivityThread.java:4627)
05-26 14:42:03.895: E/AndroidRuntime(345):  at java.lang.reflect.Method.invokeNative(Native Method)
05-26 14:42:03.895: E/AndroidRuntime(345):  at java.lang.reflect.Method.invoke(Method.java:521)
05-26 14:42:03.895: E/AndroidRuntime(345):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
05-26 14:42:03.895: E/AndroidRuntime(345):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
05-26 14:42:03.895: E/AndroidRuntime(345):  at dalvik.system.NativeStart.main(Native Method)
05-26 14:42:03.895: E/AndroidRuntime(345): Caused by: android.database.sqlite.SQLiteException: no such column: _id: , while compiling: SELECT _id, date, steps, calories FROM acts
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:46)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1229)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1184)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1264)
05-26 14:42:03.895: E/AndroidRuntime(345):  at com.b00348312.workout.DataBaseHelper.getAllActs(DataBaseHelper.java:168)
05-26 14:42:03.895: E/AndroidRuntime(345):  at com.b00348312.workout.WorkoutProgress.fillData(WorkoutProgress.java:30)
05-26 14:42:03.895: E/AndroidRuntime(345):  at com.b00348312.workout.WorkoutProgress.onCreate(WorkoutProgress.java:25)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
05-26 14:42:03.895: E/AndroidRuntime(345):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
05-26 14:42:03.895: E/AndroidRuntime(345):  ... 11 more

Looking at the logcat it seems to be telling me that I am missing a column "_id" but I have modified the database to include this column which you can see in the database helper code above. Is there something I have missed?

score 1 · Answer 1 · edited May 23 '17 at 12:11

1

With activity won't start errors, the root cause is further down in the stack. In your case, here:

05-26 14:42:03.895: E/AndroidRuntime(345): Caused by: android.database.sqlite.SQLiteException: no such column: _id: , while compiling: SELECT _id, date, steps, calories FROM acts

It seems self explanatory? See here

The key point is that your cursor must contain a column called _id in the result set, either from a real column, or one aliased.

edited May 23 '17 at 12:11

Community

1
1

answered May 26 '12 at 14:53

Simon

14,407
8
46
61

but it does contain _id. its the primary key or the table it should be pulling the information from – Darren Murtagh May 26 '12 at 14:55
if changing the primary key is not what i should have sone what should i have with the column _id – Darren Murtagh May 26 '12 at 15:05

score 1 · Accepted Answer · answered May 26 '12 at 15:06

1

You may have modified the database to include the required column but this changes will not be actually implemented in the database until the method onCreate is called again or you implement the changes in the onUpgrade method and modify the version of the database.

Try to uninstall and then install the app again on the emulator/phone so the onCreate method of your SQLiteOpenHelper implementation is called again.

answered May 26 '12 at 15:06

user

86,916
18
197
190

this does not seem to work as updating the database,reinstalling it gives the same logcat errors. i have ran this not only on my own phone but other phones and an emulator with all giving the same results after the new database was implemented – Darren Murtagh May 26 '12 at 15:11
@DarrenMurtagh Did you remove all the data(with `Clear Data`) before reinstalling the application? – user May 26 '12 at 15:16
1

The point I was trying to make is that the cursor resultset must contain _id. Have you stepped through and verified this? – Simon May 26 '12 at 15:17
@Luksprog no i did not but i didnt think that it would matter when testing it on a clean device which had not had the application installed before – Darren Murtagh May 26 '12 at 15:23
@Simon do you mean that the method i am calling must return the column with _id in it? – Darren Murtagh May 26 '12 at 15:24
@Darren, Simon is correct. The ListView (whether from ListActivity or not) requires a unique _id for each row. The default adapters automatically account for this whether you are utilizing a database or not. It is important that your query provides this as well. It must be noted this is true whether you are using a SimpleCursorAdapter or some custom database adapter. – Fuzzical Logic May 26 '12 at 15:36
Then what would you suggest i change in my database helper. as shown above the called method getAllActs has a varible called KEY_ROWID2 which has the value of _id so i believe that what is needed for the simplecursoradaptor should be present – Darren Murtagh May 26 '12 at 15:41
Got to say, your code looks OK but I still think that for whatever reason, you don't have the _id column. Put a breakpoint on "return mCursor" and work back from there. – Simon May 26 '12 at 15:51
but i do have an _id coloum. it is the primary key in thhe table i'm trying to use and it is in the variable KEY_ROWID2 which is being put into the cursor and returned. – Darren Murtagh May 26 '12 at 15:59
@DarrenMurtagh I've tested your code and it works with no problem. You're doing something that prevents the database for being built correctly with the new `_id` columns(try to remove all stuff(including the app itself) from the phone related to your app and reinstall it again(another solution is to use the current code and increment the database version)). @Simon the missing `_id` column in a `ListView` throws an `IllegalArgumentException` and not a `SQLiteException` like in his case. – user May 26 '12 at 16:00
Oh ok thank you. i thought i had the code correct but was unsure on why it was not working. i am going to try on working to get my database to automatically update when the application is run – Darren Murtagh May 26 '12 at 16:20
@DarrenMurtagh If you try to increment the database version make sure that in the `onUpgrade` method you delete the previous tables(delete both table, just to be sure) and then call `onCreate()`. – user May 26 '12 at 16:24

Unable to display database in ListView

2 Answers2