sqlite Update query not functioning

Question

I have created this class

public class MCount {

    public static final String TABLE_NAME = "Count";

    public static final String Columnname_ID = "Password_ID";

    public static final String Columnname_Counts = "counts";

    public static final String[] COLUMNS = { Columnname_ID, Columnname_Counts };

    private int password_id;
    private int counts;


    @Override
    public String toString() {
        return "MPassword[Password_ID=" + this.password_id + ", counts=" +  this.counts  + "]";
    }
    public MCount(int password_id, int counts) {
        this.password_id = password_id;

    }

    public int getPassword_id() {
        return password_id;
    }

    public void setPassword_id(int password_id) {
        this.password_id = password_id;
    }

    public int getcounts() {
        return counts;
    }

    public void setcounts(int counts) {
        this.counts = counts;
    }

}

and an SQl helpher class

/**
 * Helper class which handles read/write from/to the Count.db
 * 
 * @author Eyog Yvon Léonce
 * 
 */
public class CountDbHelper extends SQLiteOpenHelper {

    private String password;

    private static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "Count.db";

    public CountDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // Default password for the password db. 
        this.password = "you are a bad boy! Desu ne!!!";
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_COUNT_TABLE = "CREATE TABLE " + MCount.TABLE_NAME + "( " + MCount.Columnname_ID + " INTEGER PRIMARY KEY, "
                  + MCount.Columnname_Counts + " INTEGER"   + ")";
        db.execSQL(CREATE_COUNT_TABLE);
    }

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

    /**
     * Save the Password in the Count.db
     * 
     * @param password
     */
    public void setPassword(MCount password) {
        SQLiteDatabase db = this.getWritableDatabase(this.password);
        ContentValues values = new ContentValues();
        values.put(MCount.Columnname_ID, password.getPassword_id());
        values.put(MCount.Columnname_Counts, password.getcounts());
        db.insertWithOnConflict(MCount.TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_REPLACE);
        db.close();
    }

    /**
     * Load the Password from the Password.db
     * 
     * @return
     */
    public MCount getPassword() {
        MCount password = null;

        String query = "SELECT * FROM " + MCount.TABLE_NAME;
        SQLiteDatabase db = this.getWritableDatabase(this.password);
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            password = new MCount(Integer.parseInt(cursor.getString(0)), Integer.parseInt(cursor.getString(1)));
        }
        cursor.close();
        db.close();

        return password;
    }

    /**
     * Check if the Count.db has an entry in it
     * 
     * @return
     */
    public boolean hasPassword() {
        String query = "SELECT COUNT(*) FROM " + MCount.TABLE_NAME;
        SQLiteDatabase db = this.getWritableDatabase(this.password);
        Cursor cursor = db.rawQuery(query, null);
        try {
            if (cursor.moveToFirst()) {
                return cursor.getInt(0) > 0;
            }
        } catch (Exception e) {

        } finally {
            cursor.close();
            db.close();
        }
        return false;
    }

    /**
     * Add a failed try to the Count.db
     * 
     * @return
     */
    public int addCount() {

        int counts = 0 ;

        String query = "SELECT " + MCount.Columnname_Counts + " FROM " + MCount.TABLE_NAME;
        SQLiteDatabase db = this.getWritableDatabase(this.password);
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            counts = Integer.parseInt(cursor.getString(0));
            counts++;
            db.execSQL("UPDATE " + MCount.TABLE_NAME + " SET " + MCount.Columnname_Counts + "=" + counts);
        }

        Log.d("addTry()", "Added a Click. Count is now: " + counts);

        cursor.close();
        db.close();

        return counts;
    }
}

I'm trying to get the number of times a given button is pressed and then store it in the database. I instantiate the helper class in the onlick method with

    CountDbHelper helper = new  CountDbHelper (getApplicationContext());
    //MCount m_password = helper.getPassword();

    int tries = helper.addCount();
    Toast.makeText(getApplicationContext(),

            "You clicked "+ tries + " time already. ", Toast.LENGTH_SHORT).show();

It shows zero all the times and I am a little confused. Can someone help me out?

Answer 1

You table is empty, so the SQL UPDATE-statement has nothing to update.

You will want to use a combination of an insert and update statement, also known as an 'upsert'. Something like this:

private void upsertCount(SQLiteDatabase db, int counts) {
    ContentValues values = new ContentValues();
    values.put(MCount.Columnname_Counts, counts);
    int insertedCount = db.insert(MCount.TABLE_NAME, null, values);
    if (insertedCount == 0)
        db.update(MCount.TABLE_NAME, values, null, null);
}

BTW: Note that these upsert statements affect all the records in the MCount table. That may be what you want, but it is strange to use a "table" to store a "single" value. Tables are for multiple values. A more appropriate place to store this would be in SharedPreferences. That is a key-value store for storing app configuration and settings.

Answer 2

In your case, have you inserted record in database? If not cursor.moveToFirst() will always return false and you will get counts = 0

check below condition

public int addCount() {

            int counts = 0 ;

            String query = "SELECT " + MCount.Columnname_Counts + " FROM " + MCount.TABLE_NAME;
            SQLiteDatabase db = this.getWritableDatabase(this.password);
            Cursor cursor = db.rawQuery(query, null);

            if (cursor.moveToFirst()) {
                counts = Integer.parseInt(cursor.getString(0));
                counts++;
                db.execSQL("UPDATE " + MCount.TABLE_NAME + " SET " + MCount.Columnname_Counts + "=" + counts);
            } else {
              //insert record in database
    }

            Log.d("addTry()", "Added a Click. Count is now: " + counts);

            cursor.close();
            db.close();

            return counts;
        }