Android: SQLite database query not returning any records

Question

I created and populated a SQLite database with the Firefox SQLite Manager. I copied the .sqlite file into the assets folder of my Android project.

My aim is to use this records to populate a spinner. When I query the database, the cursor displays the columns but no records.

My DatabaseHandler class extends SQLiteOpenHelper and contains methods for onCreate, onUpgrade and getAllOffices.

public List<String> getAllOffices(){ 
    List<String> offices = new ArrayList<String>(); 

    // Select All Query 
    String selectQuery = "SELECT OfficeId as _id, OfficeName FROM " + TABLE_OFFICE; 

    SQLiteDatabase db = this.getReadableDatabase(); 
    Cursor cursor = db.rawQuery(selectQuery, null); 

    // looping through all rows and adding to list 
    if (cursor.moveToFirst()) { 
        do { 
            offices.add(cursor.getString(1)); 
        } while (cursor.moveToNext()); 
    } 

    // closing connection 
    cursor.close(); 
    db.close(); 

    // returning offices 
    return offices; 
}

Any ideas on why the records are not returned would be appreciated.

Are you sure that you have populated it correctly?

Shajeel Afzal
– Shajeel Afzal

2013-04-09 02:59:26 +00:00
Commented Apr 9, 2013 at 2:59 — Shajeel Afzal
– Shajeel Afzal, Commented Apr 9, 2013 at 2:59

collusionbdbh · Accepted Answer · 2013-04-09 04:12:50Z

1

Try this:

public List<String> getAllOffices(){ 
    List<String> offices = new ArrayList<String>(); 

    // Select All Query 
    String selectQuery = "SELECT OfficeId as _id, OfficeName FROM " + TABLE_OFFICE; 

    SQLiteDatabase db = this.getReadableDatabase(); 
    Cursor cursor = db.rawQuery(selectQuery, null); 

    // looping through all rows and adding to list 
    if (cursor.moveToFirst()) { 
        do { 
            offices.add(cursor.getString(cursor.getColumnIndex("_id"))); 
        } while (cursor.moveToNext()); 
    } 

    // closing connection 
    cursor.close(); 
    db.close(); 

    // returning offices 
    return offices; 
}

Basically it will just ensure that you are using the correct column index. Otherwise if you are using eclipse then try the cellobject plugin to browse the database and make sure your table is populated as expected: http://marketplace.eclipse.org/content/cellobject-sqlite-xml-browser#.UWOU5RaBD3g

edited Apr 9, 2013 at 4:12

answered Apr 9, 2013 at 3:18

collusionbdbh

7001 gold badge6 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marius Over a year ago

Thanks for this suggestion. I found that the database is not populated as expected although when I run a query on the initial database in SQLite Manager the records are there.

Harshid Vasoya · Accepted Answer · 2013-04-09 04:48:02Z

Please try this and may be useful for you.

public List<String> getAllOffices(){ 
    List<String> offices = new ArrayList<String>(); 

    // Select All Query 
    String selectQuery = "SELECT * FROM " + TABLE_OFFICE; 

    SQLiteDatabase db = this.getReadableDatabase(); 
    Cursor cursor = db.rawQuery(selectQuery, null); 
    System.out.println("CURSOR SIZE:"+cursor.getCount())
    // looping through all rows and adding to list 
    if(cursor.getCount()!=0){
        if (cursor.moveToFirst()) { 
            do { 
                System.out.println(cursor.getString(c.getColumnIndex("as per your db column name"));
            } while (cursor.moveToNext()); 
        }
    }   

    // closing connection 
    cursor.close(); 
    db.close(); 

    // returning offices 
    return offices; 
}

First of all you have to just see console output if you are getting right then add into your office bean

Collectives™ on Stack Overflow

Android: SQLite database query not returning any records

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related