setMultiChoiceItems with a cursor not populating the list

Question

Can someone assist me in getting my list to populate using setMultiChoiceItems with a cursor? My AlertDialog pops up with the title and buttons, but nothing in the list. I have confirmed there is at least one item in the database and that should show up on the list but it doesn't currently. I think it has something to do with my cursor. Thank you.

String isCheckedColumn;
String labelColumn;
Cursor cursor;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    mydb = new MyDBHandler(this);
    // CHECK INSERTION TO DATABASE WORKS AND PRINT TO LOGCAT
    mydb.insertAllergy("Nuts", "0");
    ArrayList<String> s = mydb.getAllAllergies();
    System.out.println(s.get(0));

    // INITIALIZE VARIABLES FOR LIST POPUP
    isCheckedColumn = "selected";
    labelColumn = "allergy";
}

/** LIST SELECTION POPUP */
public void selectAllergens() {
    AlertDialog.Builder builder = new AlertDialog.Builder(this);
    builder.setTitle("Select Allergens");
    builder.setMultiChoiceItems(cursor, isCheckedColumn, labelColumn, new DialogInterface.OnMultiChoiceClickListener() {
        @Override
        public void onClick(DialogInterface dialog, int selectedItemId, boolean isSelected) {
            if(isSelected) {
                System.out.println("onClick if");
            } else {
                System.out.println("onClick else");
            }
        }
    })
            .setPositiveButton("OK", new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialog, int id) {

                }
            })
            .setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialog, int id) {
                    dialog.dismiss();
                }
            });

    Dialog dialog = builder.create();

    dialog.show();
}

Here's my database helper class, MyDBHandler. I thought maybe there should be a getCursor method here so when I need to initialize a cursor I can do it with that. It didn't work.

public class MyDBHandler extends SQLiteOpenHelper {
// DATABASE INFORMATION
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "AllergiesDB.db";
private static final String TABLE_NAME = "allergies_list";
private static final String COLUMN_ID = "allergy_id";
private static final String COLUMN_ALLERGY = "allergy";
private static final String COLUMN_SELECTED = "selected";
private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + TABLE_NAME + " (" + COLUMN_ID + " INTEGER PRIMARY KEY, " +
        COLUMN_ALLERGY + " TEXT, " + COLUMN_SELECTED + " TEXT)";

// INITIALIZE DATABASE
public MyDBHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

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

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

public boolean insertAllergy(String allergy, String isChecked) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_ALLERGY, allergy);
    contentValues.put(COLUMN_SELECTED, isChecked);
    db.insert(TABLE_NAME, null, contentValues);
    return true;
}

public Cursor getData(int id) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("select * from " + TABLE_NAME + " where " + COLUMN_ID + "=" + id + "", null);
    return cursor;
}

public boolean updateAllergies(Integer id, String allergy, String isChecked) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_ALLERGY, allergy);
    contentValues.put(COLUMN_SELECTED, isChecked);
    db.update(TABLE_NAME, contentValues, "id = ? ", new String[] {Integer.toString(id)});
    return true;
}

public Integer deleteAllergy(Integer id) {
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete(TABLE_NAME, "id = ? ", new String[] {Integer.toString(id)});
}

public ArrayList<String> getAllAllergies() {
    ArrayList<String> array_list = new ArrayList<>();

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("select * from " + TABLE_NAME, null);
    cursor.moveToFirst();

    while(cursor.isAfterLast() == false) {
        array_list.add(cursor.getString(cursor.getColumnIndex(COLUMN_ALLERGY)));
        cursor.moveToNext();
    }

    return array_list;
}

public ArrayList<String> getSelectedAllergies() {
    ArrayList<String> array_list = new ArrayList<>();

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("select * from " + TABLE_NAME, null);
    cursor.moveToFirst();

    while(cursor.isAfterLast() == false) {
        array_list.add(cursor.getString(cursor.getColumnIndex(COLUMN_SELECTED)));
        cursor.moveToNext();
    }

    return array_list;
}

public Cursor getCursor() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("select * from " + TABLE_NAME, null);
    cursor.moveToFirst();
    return cursor;
}
}

This is what the popup looks like: AlertDialog Popup

Here's the error when using 'cursor = mydb.getCursor();'. Clicking on the button that opens the AlertDialog produces the error. The cursor initialization is in the onCreate method of the MainActivity so the initialization isn't causing the crash. The use of the cursor in setMultiChoiceItems is the cause, leading me to believe the cursor wasn't initialized properly.

--------- beginning of crash
08-24 20:53:50.330 11277-11277/com.healthydreams.lpa.allergyscanner E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.healthydreams.lpa.allergyscanner, PID: 11277
java.lang.IllegalStateException: Could not execute method for android:onClick
    at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:390)
    at android.view.View.performClick(View.java:6294)
    at android.view.View$PerformClick.run(View.java:24770)
    at android.os.Handler.handleCallback(Handler.java:790)
    at android.os.Handler.dispatchMessage(Handler.java:99)
    at android.os.Looper.loop(Looper.java:164)
    at android.app.ActivityThread.main(ActivityThread.java:6494)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)
 Caused by: java.lang.reflect.InvocationTargetException
    at java.lang.reflect.Method.invoke(Native Method)
    at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:385)
    at android.view.View.performClick(View.java:6294) 
    at android.view.View$PerformClick.run(View.java:24770) 
    at android.os.Handler.handleCallback(Handler.java:790) 
    at android.os.Handler.dispatchMessage(Handler.java:99) 
    at android.os.Looper.loop(Looper.java:164) 
    at android.app.ActivityThread.main(ActivityThread.java:6494) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807) 
 Caused by: java.lang.IllegalArgumentException: column '_id' does not exist. Available columns: [allergy_id, allergy, selected]
    at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:340)
    at android.widget.CursorAdapter.init(CursorAdapter.java:180)
    at android.widget.CursorAdapter.<init>(CursorAdapter.java:144)
    at android.support.v7.app.AlertController$AlertParams$2.<init>(AlertController.java:1009)
    at android.support.v7.app.AlertController$AlertParams.createListView(AlertController.java:1009)
    at android.support.v7.app.AlertController$AlertParams.apply(AlertController.java:965)
    at android.support.v7.app.AlertDialog$Builder.create(AlertDialog.java:982)
    at com.healthydreams.lpa.allergyscanner.MainActivity.selectAllergens(MainActivity.java:148)
    at com.healthydreams.lpa.allergyscanner.MainActivity.openProfiles(MainActivity.java:85)
    at java.lang.reflect.Method.invoke(Native Method) 
    at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:385) 
    at android.view.View.performClick(View.java:6294) 
    at android.view.View$PerformClick.run(View.java:24770) 
    at android.os.Handler.handleCallback(Handler.java:790) 
    at android.os.Handler.dispatchMessage(Handler.java:99) 
    at android.os.Looper.loop(Looper.java:164) 
    at android.app.ActivityThread.main(ActivityThread.java:6494) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)

Are you sure that `cursor` isn't null or empty when you show the `Dialog`? Where, and how, are you assigning that? — Mike M., Aug 24 '18 at 23:53
@MikeM. Because all I do is create it, I'm sure it is null since it wasn't initialized. I attempted to initialize it once and then the app crashed instead of showing the Dialog. I'll keep looking to initialize it correctly. — Lee Phillips, Aug 25 '18 at 00:02
OK. That's something you probably meant to handle in your `MyDBHandler` class, where you'd do a `query()` on `getReadableDatabase()`, assuming that class is an `SQLiteOpenHelper`. Feel free to [edit] your question to post that code and the errors you were getting. — Mike M., Aug 25 '18 at 00:07
Thanks @MikeM. I added the helper class as well. Getting late here so I'll probably pick up in the morning, but at least now I know I'm looking to initialize the cursor properly to get it going. Appreciate it. — Lee Phillips, Aug 25 '18 at 00:13
Looks like `getCursor()` should do what you want. How did it not work when you assigned that to `cursor` before creating the `AlertDialog`? What exact errors or Exceptions do you get? — Mike M., Aug 25 '18 at 00:16
@MikeM. thank you! So just posting the crash here helped. I saw something I didn't notice in it while in AndroidStudio for some reason. It was complaining about _id not existing. I had it set to allergy_id. So initialization was correct in the helper after all. How do I mark you as the answer since this is all in comments? — Lee Phillips, Aug 25 '18 at 01:00
Ah, good. I'd seen that, but we needed the stack trace to be sure that it was the current issue. Anyhoo, this is a very common problem, and there are many questions about it here already, so I'll just mark this as a duplicate. Thank you, though. I appreciate the offer to answer. Glad you got it figured out. Nice work. Cheers! — Mike M., Aug 25 '18 at 01:05

MXC · Answer 1 · 2018-08-25T01:16:57.343

I assume when you first ran your code you would have got colum_id exception. column '_id' does not exist and the app would have crashed. What this means is you would have to create your primary key like this _columnId. See the underscore at the start. You can read more about the why here. But for now, remember sqllite needs the _ at the start of the primary key column. About "_id" field in Android SQLite

Once you have done that change, next you would face a problem with the select query in your getCursor() method. Sometimes the * does not work as it needs the explicit name of your columns, so change the query to select " + COLUMN_ID + "," + COLUMN_ALLERGY + "," + COLUMN_SELECTED + " from " + TABLE_NAME

I am posting these changes below, I have not called any other method as I wanted to give you a start where you are not seeing crashes in the app.

private static final String COLUMN_ID = " _columnId";
private static final String COLUMN_ALLERGY = "allergy";
private static final String COLUMN_SELECTED = "selected";
private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + COLUMN_ID " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_ALLERGY + ", " + COLUMN_SELECTED  + ")";

......

public Cursor getCursor() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("select " + COLUMN_ID + "," +  
    COLUMN_ALLERGY + "," + COLUMN_SELECTED + " from " + TABLE_NAME, 
    null);
    cursor.moveToFirst();
    return cursor;
}

You can call the method which would populate data in the database and then call the getCursor method. Once you have that, entries would appear in the dialog box. Let me know if you face any issue.

Pardon my ignorance. First time trying to work with databases. It looks like setMultiChoiceItems needs both the name that I'm trying to add to the list and the String that tells it if it's checked or not. If I use 'ArrayList s' to initialize the cursor, will it still have access to all columns needed since that ArrayList only has one of the two needed? — Lee Phillips, Aug 25 '18 at 00:07
I updated my answer, please check and let me know if you face any issue. — MXC, Aug 25 '18 at 01:17

setMultiChoiceItems with a cursor not populating the list

1 Answers1