CODEWITHSUNDEEP
android-sqlite
Jyoti Gulati
Jyoti Gulati

Reputation: 33

cursor count is 1 whereas the table has 3 rows

I m trying to populate sql table and then retrieve data from it. Following is my code.

public void addQuestion(Question quest)
{
    int id = 1;
    ContentValues values = new ContentValues();
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUEST1);
    onCreate(db);
    values.put(KEY_QUES, quest.getQuestion());
    values.put(KEY_ANSWER, quest.getAnswer());
    values.put(KEY_OPTA, quest.getOptA());
    values.put(KEY_OPTB, quest.getOptB());
    values.put(KEY_OPTC, quest.getOptC());
    db.insert(TABLE_QUEST1, null, values);
    System.out.println("Added in database: " + quest.getQuestion());
}

public ArrayList<Question> getAllQuestions() {
    System.out.println("getting rows 1");
    ArrayList<Question> quesList = new ArrayList<Question>();
    System.out.println("getting rows 2");
    Cursor cursor = null;
    SQLiteDatabase db = getReadableDatabase();
    System.out.println("getting rows ");
    cursor = db.rawQuery("SELECT * FROM " + TABLE_QUEST1, null);
    if (!cursor.moveToFirst()) {
        System.out.println("No data in the database ");
    } else {
        System.out.println("theres data in the database ");
        quesList = new ArrayList<Question>();
        do {
            System.out.print("total rows " + cursor.getCount());
            Question quest = new Question();
            quest.setID(cursor.getInt(0));
            quest.setQuestion(cursor.getString(1));
            quest.setAnswer(cursor.getString(2));
            quest.setOptA(cursor.getString(3));
            quest.setOptB(cursor.getString(4));
            quest.setOptC(cursor.getString(5));
            quesList.add(quest);
        } while (cursor.moveToNext());
        cursor.close();
    }
}

I have 4 rows of data in my table and I can see that with the print statement "added in database"

but when i actually read it the cursor just reads row 1 and moves out of the while loop. what could potentially be wrong.

tia

Upvotes: 1

Views: 194

Answers (2)

MikeT
MikeT

Reputation: 56958

As Santosh has pointed out DROPPING the table (as per db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUEST1);) and then re-creating it (as per onCreate(db);) will delete the table and then re-create the table removing any rows/data that had previously been added to the table.

As such it's simply a matter of removing those two lines of code, Also there appears to be no need for the line int id = 1;, so perhaps remove this, as per :-

public void addQuestion(Question quest)
{
    ContentValues values = new ContentValues();
    SQLiteDatabase db = this.getWritableDatabase();
    values.put(KEY_QUES, quest.getQuestion());
    values.put(KEY_ANSWER, quest.getAnswer());
    values.put(KEY_OPTA, quest.getOptA());
    values.put(KEY_OPTB, quest.getOptB());
    values.put(KEY_OPTC, quest.getOptC());
    db.insert(TABLE_QUEST1, null, values);
    System.out.println("Added in database: " + quest.getQuestion());
}

P.S. you may consider not using hard coded column offsets but instead obtain offsets according to column names by utilising the getColumnIndex(column_name) Cursor method. e.g. :-

        Question quest = new Question();
        quest.setID(cursor.getInt(cursor.getColumnIndex("name_of_your_id_columm")));
        quest.setQuestion(cursor.getString(cursor.getColumnIndex(KEY_QUES)));
        quest.setAnswer(cursor.getString(cursor.getColumnIndex(KEY_ANSWER)));
        quest.setOptA(cursor.getString(cursor.getColumnIndex(KEY_OPTA)));
        quest.setOptB(cursor.getString(cursor.getColumnIndex(KEY_OPTB)));
        quest.setOptC(cursor.getString(cursor.getColumnIndex(KEY_OPTC)));
        quesList.add(quest);

Noting that instead of "name_of_your_id_columm", you may have something like KEY_ID defined, if so use that, thus you have a single definition so it reduces the chance of inadvertently mispelling column names or miscalculating the offsets.

Upvotes: 1

Mansi Rao
Mansi Rao

Reputation: 44

Your code was absolutely fine except placing drop command in the loop. As mentioned in the earlier comments, please make sure to avoid calling drop query each time and you'll find the result.

Upvotes: 1

Related Questions