get specific row in SQLiteDatabase not working

Question

im a new developer and noob in SQLiteDatabaseand and have this little problem .. and i need a little help I'm creating a simple to_do app that have catagories, the app can create catagories and display them in a list view, and by clicking the category item that just created i can add new to_do tasks and Display them in anew list view with this category name

here is my dataBase :

public class DatabaseOpenHelper extends SQLiteOpenHelper {
    public static final String ITEM_NAME_TABLE="items_table";
    public static final String TYPE_NAME_TABLE="types_table";
    public static final String ITEM_NAME="name";
    public static final String ITEM_PRICE="price";
    public static final String ITEM_IMAGE_NAME="img_name";
    public static final String ITEM_TYPE="item_type";
    public static final String ID="_id";
}

i want to view the items by the category that clicked so i write this method :

public Cursor getlAllGifts(String category){
    return database.query(DatabaseOpenHelper.ITEM_NAME_TABLE, null, DatabaseOpenHelper.ITEM_TYPE+"="+ category, null, null, null, null);
}

i passed the category form the pervious intent but when this method invoked i get this error

08-03 06:59:59.120: E/SQLiteLog(2696): (1) no such column: personal
08-03 06:59:59.120: W/dalvikvm(2696): threadid=12: thread exiting with uncaught exception (group=0xa4bfd648)
08-03 06:59:59.120: E/AndroidRuntime(2696): FATAL EXCEPTION: AsyncTask #2
08-03 06:59:59.120: E/AndroidRuntime(2696):     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
08-03 06:59:59.120: E/AndroidRuntime(2696):     at java.lang.Thread.run(Thread.java:841)
08-03 06:59:59.120: E/AndroidRuntime(2696): Caused by: android.database.sqlite.SQLiteException: no such column: personal (code 1): , while compiling: SELECT * FROM items_table WHERE item_type=personal
08-03 06:59:59.120: E/AndroidRuntime(2696): Caused by: android.database.sqlite.SQLiteException: no such column: personal (code 1): , while compiling: SELECT * FROM items_table WHERE item_type=personal
08-03 06:59:59.120: E/AndroidRuntime(2696):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

i have the catagory personal but i don't know how to get the rows by specific category (item_type) please help me

Answer 1

Could you please try the query below?

If works, then, I update the answer with more details:

public Cursor getlAllGifts(String category){
    return database.query(DatabaseOpenHelper.ITEM_NAME_TABLE, null, DatabaseOpenHelper.ITEM_TYPE + " LIKE ? ", new String[]{category}, null, null, null, null);
}

Background

If you check the DOCS, you can see that method query() accepts following parameters:

query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) 

Your code was:

database.query(DatabaseOpenHelper.ITEM_NAME_TABLE, null, DatabaseOpenHelper.ITEM_TYPE+"="+ category, null, null, null, null)

So, you are setting your WHERE statement in the right place. However, you was not doing properly. Then, you query was converted to:

SELECT * FROM items_table WHERE item_type=personal

Then, SQLite was handling personal as a column (and not as your where condition)

Let the Android make the Dirt Work

In fact, you can use the 4th parameter of query() (selectionArgs[]) and then, Android will create the where clause for you... You just send the arguments/parameters.

This way, in the 3rd parameter, you add ? and in the 4th parameter, you add the arguments that android should insert in place of the ?

So, in my example, I added:

• 3rd parameter: DatabaseOpenHelper.ITEM_TYPE + " LIKE ? "
• 4th parameter: new String[]{category}

Android will then, replace ? by your argument category.