Android SQLite - First Inserted value is always lost

Question

This is bit creepy - first inserted data is always lost.

What ever I do - I always loses my first transaction details. Similarly as shown in the following i am adding transactions twice - but when i get size of returned results for transactions table it is always 1 - instead of 2.

Let me know if I am doing anything wrong?

Thanks in advance

I am adding transactions as follows:

transactionHelper th = new transactionHelper(this.activity.getApplicationContext());
values.clear();
values = new ContentValues();

values.put("accountid", accountID );
values.put("amount", totalBalance);
values.put("transactiontype", 1);
values.put("checknumber", "");
values.put("checkdate", transactionDate);
values.put("cleared", 1);
values.put("notes", "Starting Balance");
values.put("categoryid", startCat);

boolean added = th.addTransaction(values);
Log.v("jv", "Is this added or not: " + added);

th.addTransaction(values);
Log.v("jv", "nothing added: " + th.getAll().size());

My class looks like:

public transactionHelper(Context context) { super(context, DATABASE_NAME , null, 1); }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "CREATE TABLE IF NOT EXISTS transactions ( " +
                "transactionid INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "accountid INTEGER, "+
                "amount REAL, " +
                "transactiontype INTEGER, " +
                "checknumber TEXT, " +
                "checkdate TEXT, " +
                "cleared INTEGER," +
                "notes TEXT," +
                "categoryid INTEGER)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         db.execSQL("DROP TABLE IF EXISTS transactions");
         onCreate(db);
    }

    public boolean addTransaction(ContentValues values) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.insert("transactions", null, values);
        return true;
    }

    public boolean updateTransaction(Integer id, ContentValues values) {
        try {
            SQLiteDatabase db = this.getWritableDatabase();
            db.update("transactions", values, "transactionid = ? ", new String[] { Integer.toString(id) } );
        } catch(Exception e) { Log.v("jv", "Exception in Update Contact: " + e.toString()); return false;}
        return true;
    }

    public int deleteTransaction(Integer id) {
        int numDeleted = 0;

        try {
            SQLiteDatabase db = this.getWritableDatabase();
            numDeleted = db.delete("transactions","transactionid = ? ", new String[] { Integer.toString(id) });
        } catch(Exception e) { Log.v("jv", "Exception in deleteTransaction: " + e.toString()); }


        return numDeleted;
    }

    public cTransactions getTransaction(int id){
         cTransactions  ct = new cTransactions();

         try {
             SQLiteDatabase db = this.getReadableDatabase();
             Cursor cur =  db.rawQuery( "select * from transactions where transactionid="+id+"", null );

             if( cur.moveToFirst() ) {
                ct.setTransactionID(cur.getInt(0));
                ct.setAccountID(cur.getInt(1));
                ct.setAmount(cur.getDouble(2));
                ct.setTransactionType(cur.getInt(3));
                ct.setCheckNumber(cur.getString(4));
                ct.setCheckDate(cur.getString(5));
                ct.setCleared( cur.getInt(6));
                ct.setNotes(cur.getString(7));
                ct.setCategory(cur.getInt(8));
             }
         } catch (Exception e) { Log.v("jv", "Exception in GetTransaction: " + e.toString() ); }
         return ct;
     }

    public ArrayList<Object> getAll() {
        ArrayList<Object> ret = new ArrayList<Object>();

        try {
            String query = "SELECT * FROM transactions";
            SQLiteDatabase db = this.getWritableDatabase();
            Cursor cur = db.rawQuery(query, null);

            if( cur != null )
                cur.moveToFirst();

            while( cur.moveToNext()) {
                cTransactions ct = new cTransactions();
                ct.setTransactionID(cur.getInt(0));
                ct.setAccountID(cur.getInt(1));
                ct.setAmount(cur.getDouble(2));
                ct.setTransactionType(cur.getInt(3));
                ct.setCheckNumber(cur.getString(4));
                ct.setCheckDate(cur.getString(5));
                ct.setCleared( cur.getInt(6));
                ct.setNotes(cur.getString(7));
                ct.setCategory(cur.getInt(8));
                ret.add(ct);
            }
        } catch(Exception e) { Log.v("jv", e.toString()); }
        return ret;
    }

Answer 1

cursor.moveToFirst() sets the cursor to the first result row. Then you immediately call cursor.moveToNext() which advances to the second result row. So you are just always skipping the first row in getAll().

You could change your loop to a do-while to fix it:

cursor.moveToFirst();
do{
  // get the values
} while (cursor.moveToNext());