SimpleCursorAdapter is being difficult to use

I have been stuck at one point from long time, i.e with the use of

 SimpleCursorAdapter 

as it fails while returning the correct value. I have seen similar many post in SO itself, saying that I should add _id column in the cursor database query, rather I should do

 db.rawQuery(String,String)

My code in the onCreate(..) is

HospitalData = new Database(this);
HospitalData.open();
Cursor c = HospitalData.getAllRows_Patient_Db();
startManagingCursor(c);
c.moveToFirst();

//HERE SOME LOOP IS NEEDED FOR TRAVERSING AND PUTTING IN THE LISTVIEW
while(c.isAfterLast() == false)
{
    String[] columns = new String[] { c.getString(1), c.getString(2) };
    int[] to = new int[] { R.id.room_number_db, R.id.pt_initial_db };
    adapter = new SimpleCursorAdapter(this,R.layout.patient_db, c,columns,to);
    c.moveToNext();
}
setListAdapter(adapter);

And previously my database accessing code was as follows

 public Cursor getAllRows_Patient_Db() 
{               
    return db.query(DATABASE_PATIENT_TABLE, new String[] {KEY_ROWID, KEY_ROOM_NUMBER,                             
                                                        KEY_PATIENT_INITIAL
            }, 
            null, 
            null, 
            null, 
            null, 
            null);
}

where KEY_ROWID is defined as follows

public static final String KEY_ROWID = "_id";

And the error with this is

07-04 22:10:23.301: ERROR/AndroidRuntime(16795): Caused by: java.lang.IllegalArgumentException: column '90' does not exist
07-04 22:10:23.301: ERROR/AndroidRuntime(16795):     at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:314)
07-04 22:10:23.301: ERROR/AndroidRuntime(16795):     at android.widget.SimpleCursorAdapter.findColumns(SimpleCursorAdapter.java:312)

Here column 90 is not the column id but according to my database is the data stored in cursor.getString(1), but I think here it is trying to search cursor.getString(0) which is the row id.

Later I changed my code as follows

public Cursor getAllRows_Patient_Db() 
{
  String db_sel = "SELECT id as _id, KEY_ROOM_NUMBER" +
    ",KEY_PATIENT_INITIAL FROM DATABASE_PATIENT_TABLE";

    return db.rawQuery(db_sel,null);
}

But still I am getting error, this time error is different

07-04 21:36:12.510: ERROR/global(9861): Deprecated Thread methods are not supported.

 07-04 21:36:12.950: ERROR/AndroidRuntime(9861): FATAL EXCEPTION: main
 07-04 21:36:12.950: ERROR/AndroidRuntime(9861): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.pro/com.pr开发者_开发知识库o.CopyOfFirstScreen}: android.database.sqlite.SQLiteException: no such table: DATABASE_PATIENT_TABLE: , while compiling: SELECT id as _id, KEY_ROOM_NUMBER,KEY_PATIENT_INITIAL FROM DATABASE_PATIENT_TABLE

 07-04 21:36:12.950: ERROR/AndroidRuntime(9861): Caused by: android.database.sqlite.SQLiteException: no such table: DATABASE_PATIENT_TABLE: , while compiling: SELECT id as _id, KEY_ROOM_NUMBER,KEY_PATIENT_INITIAL FROM DATABASE_PATIENT_TABLE

I am stuck with it from very long time, please help!!

EDIT : Okay now with you guys help my query statement is correct and thanks for that, I am sorry I am not good in understanding the syntax for database query but I am trying to learn

The working query after changes is

String db_sel = "SELECT _id as _id, room_number" +",patient_initial FROM " + DATABASE_PATIENT_TABLE;

Actually I had to change the declared String with the key values

public static final String KEY_ROWID = "_id";
public static final String KEY_ROOM_NUMBER = "room_number";
public static final String KEY_PATIENT_INITIAL = "patient_initial";

But now I see another problem that is not from the query statement but the way I am accessing or using the simplecursorAdapter

As you can see that my table has 2 rows and 3 columns, Now I want to fetch the data from column 2 and column 3 and put it in my listview. But after the fix from the query I am getting another error

Originally it was

   String[] columns = new String[] {c.getString(1), c.getString(2) };
        int[] to = new int[] {  R.id.room_number_db, R.id.pt_initial_db };
     adapter = new SimpleCursorAdapter(this,R.layout.patient_db, c,columns,to);
     c.moveToNext();
    setListAdapter(adapter);

And the error was

  07-05 21:32:29.228: ERROR/AndroidRuntime(1505): Caused by: java.lang.IllegalArgumentException: column '90' does not exist
  07-05 21:32:29.228: ERROR/AndroidRuntime(1505):     at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:314)
  07-05 21:32:29.228: ERROR/AndroidRuntime(1505):     at android.widget.SimpleCursorAdapter.findColumns(SimpleCursorAdapter.java:312)

android.widget.SimpleCursorAdapter.findColumns(SimpleCursorAdapter.java:312)

As you can see it is trying to access the data of first column inspite of accessing the row

After that I made changes in my code

     int x = 0;         
     String[] columns = new String[] { c.getString(0),c.getString(1), c.getString(2) };
        int[] to = new int[] { x, R.id.room_number_db, R.id.pt_initial_db };
     adapter = new SimpleCursorAdapter(this,R.layout.patient_db, c,columns,to);
     c.moveToNext();
    setListAdapter(adapter); 

This was only a guess in order to find out how differently SimpleCursorAdapter works compared to a normal cursor and the error I get is

 07-05 21:34:47.947: ERROR/AndroidRuntime(1966): Caused by: java.lang.IllegalArgumentException: column '1' does not exist
 07-05 21:34:47.947: ERROR/AndroidRuntime(1966):     at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:314)

I know this question is becoming too long :-( do u suggest me to remove some code from here.

---

The new error is saying that you don't have the table "DATABASE_PATIENT_TABLE" in your database. From your code that appears to be the Static Field name for the string that contains the name of the table in the database and not the name of the table in the database.

In the code below you need to change "DATABASE_PATIENT_TABLE" to the name of the table in the database.

String db_sel = "SELECT id as _id, KEY_ROOM_NUMBER" +
",KEY_PATIENT_INITIAL FROM DATABASE_PATIENT_TABLE";

Try this and see if it will work:

String db_sel = "SELECT id as _id, KEY_ROOM_NUMBER" +
",KEY_PATIENT_INITIAL FROM" +  DATABASE_PATIENT_TABLE;

As for your original error a SimpleCursorAdapter must contain the field of _id. The change you made to the SQL Statment that powers the cursor should fix it but you need to make sure you have the right table name in the SQL statement.

Hope this helps.

---

Have you read this part of error stack:

android.database.sqlite.SQLiteException: no such table: DATABASE_PATIENT_TABLE:

? You just have no such table in database. It's not CursorAdapter problem. Your loop seems completely pointless - just read some java handbook. Ok, lets start to list some problems in your code:

String[] columns = new String[] { c.getString(1), c.getString(2) };

columns[] should contain columns names not values... In your while{} loop you are creating dozens(?) of SimpleCursorAdapters while only the last on is passed. This part of code:

String db_sel = "SELECT id as _id, KEY_ROOM_NUMBER" +
    ",KEY_PATIENT_INITIAL FROM DATABASE_PATIENT_TABLE";

is obviously wrong. I assume that you have something like

private static String DATABASE_PATIENT_TABLE = "patient";

so just use those constants:

String db_sel = "select id as _id, +"KEY_ROOM_NUMBER+", "+ KEY_PATIENT_INITIAL +" from "+DATABASE_PATIENT_TABLE;

Replace that:

 String[] columns = new String[] { c.getString(0),c.getString(1), c.getString(2) };
    int[] to = new int[] { x, R.id.room_number_db, R.id.pt_initial_db };

With that:

 String[] columns = new String[] { c.getColumnName(1), c.getColumnName(2) };
    int[] to = new int[] {R.id.room_number_db, R.id.pt_initial_db };

---

The problem is fixed, the main thing is that I should make sure I always query for the _id with each query to the database, but if I do it with cursor.getString(someIndex) then this gets misleaded as the adapter in my case is taking the content of first column as the rowid and is trying to search the row.

Nether doing rawQuery(),or using "Select _id as _id" stuffs are mandatory. these things are sometime misleading as I felt.

So keeping things simple below is the code. Database code

 public Cursor getAllRows_Patient_Db() 
{               
    return db.query(DATABASE_PATIENT_TABLE, new String[] {
            KEY_ROWID, 
            KEY_ROOM_NUMBER,
            KEY_PATIENT_INITIAL
            }, 
            null, 
            null, 
            null, 
            null, 
            null);
}

Activity Code

    HospitalData = new Database(this);
    HospitalData.open();
    Cursor c = HospitalData.getAllRows_Patient_Db();


    startManagingCursor(c);

    String[] columns = new String[] {HospitalData.KEY_ROOM_NUMBER,HospitalData.KEY_PATIENT_INITIAL };
     int[] to = new int[] {  R.id.room_number_db, R.id.pt_initial_db };
     adapter = new SimpleCursorAdapter(this,R.layout.patient_db, c,columns,to);
     c.moveToNext();
     setListAdapter(adapter);