timv timv - 11 days ago 5
Android Question

selectionArgs error says column does not exist

So I have been wrestling with SQLite on android and have had a few issues resolved. Issues are here and here.

I now want to select DISTINCT regions for a given state. My first RecyclerView screen shows the states just fine with the following code.

public Cursor getStateData() {
SQLiteDatabase db = getReadableDatabase();
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
String [] sqlSelect = {"0 _id", "state" };
String sqlTables = "Reefs";
qb.setTables(sqlTables);
qb.setDistinct(true);
Cursor c = qb.query(db, sqlSelect, null, null, null, null, null);
c.moveToFirst();
return c;
}


Now to get DISTINCT regions for each state I use the following code:

public Cursor getRegionData(String whichState) {
SQLiteDatabase db = getReadableDatabase();
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
String [] sqlSelect = {"0 _id", "region" };
String theState = whichState;
String sqlTables = "Reefs";
qb.setTables(sqlTables);
qb.setDistinct(true);
Cursor c = qb.query(db, sqlSelect, theState , null , null, null, null);
// (table, columms to return, colums for the where clause, values for the where clause, null, null, null)
c.moveToFirst();
return c;
}


And it does not work and gives me this error message:

Caused by: android.database.sqlite.SQLiteException: no such column: Tasmania (code 1): , while compiling: SELECT DISTINCT 0 _id, region FROM Reefs WHERE (Tasmania)


I selected Tasmania from the list so Tasmania was passed in as the String argument whichState.

So if I was to describe the issue in purly SQL terms it goes like this:

SELECT
region //column in my table called region
FROM
Reefs // table in my database
WHERE
state = [the state selected] //column in my table called **state** which I pass in with the varible **whichState**


What am I doing wrong?

Answer

You're passing a value, not a WHERE clause, please refer to the documentation.

To correct it, pass the WHERE clause and the arguments as such:

public Cursor getRegionData(String whichState) {
    SQLiteDatabase db = getReadableDatabase();
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
    String [] sqlSelect = {"0 _id", "region" };
    String [] theState = { whichState };
    String sqlTables = "Reefs";
    qb.setTables(sqlTables);
    qb.setDistinct(true);
    Cursor c = qb.query(db, sqlSelect, "state=?", theState, null, null, null);
    c.moveToFirst();
    return c;
}