Storing data to sqlite

Question

MainData helper = new MainData(this);name
    SQLiteDatabase db = helper.getWritableDatabase();        

String uid = MessageRecieverId;
    int userData = 0;
    Cursor data2 = helper.getUserData();
    while (data2.moveToNext()) {
        userData = data2.getInt(data2.getColumnIndex(KEY_MESSAGES_SENT));
    }
    ContentValues contentValues2 = new ContentValues();
    contentValues2.put(KEY_ID, uid);
    contentValues2.put(KEY_MESSAGES_SENT, userData+1);
    long returnVariable2 = db.update(TABLE_USER_DATA, contentValues2,null,null);
    if (returnVariable2 == -1) {
        Toast.makeText(getApplication(), "Nope", Toast.LENGTH_LONG).show();
        //-1 means there was an error updating the values
    } else {
        Toast.makeText(getApplication(),"uf", Toast.LENGTH_SHORT).show();
    }
}

userData+1 should execute only for what row where KEY_ID = uid but its changing all the rows values to +1 instead of only that... MessageRecieverId is different but the data changing it to all rows... can anyone help me solve this?

Helper

    String userQuery = "CREATE TABLE IF NOT EXISTS " + TABLE_USER_DATA + "(" + KEY_ID + " TEXT, " + KEY_NAME
            + " TEXT, " + KEY_MESSAGES_SENT + " INTEGER, " + KEY_MESSAGES_RECIEVED + " INTEGER, "
            + KEY_MESSAGES + " INTEGER, " + KEY_TIME_SPENT + " TEXT)";

Method

    public Cursor getUserData() {
    SQLiteDatabase db = this.getWritableDatabase();
    String query = "SELECT * FROM " + TABLE_USER_DATA;
    return db.rawQuery(query, null);
}

I added the whereclause after looking to the answers and getting an error saying no such column exists

Adding default data

public void Start(){
    MainData helper = new MainData(this); //Change the name to your Helper Class name
    SQLiteDatabase db = helper.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(KEY_ID, MessageRecieverId);
    contentValues.put(KEY_NAME, MessageRecieverName);
    contentValues.put(KEY_MESSAGES_SENT, 0);
    contentValues.put(KEY_MESSAGES_RECIEVED, 0);
    contentValues.put(KEY_MESSAGES, 0);
    contentValues.put(KEY_TIME_SPENT, "");

    long returnVariable = db.insert(TABLE_USER_DATA,null,contentValues);
    if (returnVariable == -1) {
        Toast.makeText(getApplication(), "Nope", Toast.LENGTH_LONG).show();
        //-1 means there was an error updating the values
    } else {
        Toast.makeText(getApplication(),"Done", Toast.LENGTH_SHORT).show();
        SharedPreferences sp = getSharedPreferences(MessageRecieverId, Context.MODE_PRIVATE);
        SharedPreferences.Editor editor = sp.edit();
        editor.putBoolean("Data", true); //save that the user enters username
        editor.apply();
    }

}

After adding data like this i increment it by one like mentioned above but error shows

 Process: com.appmaster.akash.messageplus, PID: 24433
                                                                             android.database.sqlite.SQLiteException: no such column: eR3C1LXJhzaK3fPzCpcuTuz9jZB3 (code 1): , while compiling: UPDATE UserData SET MessagesSent=? WHERE RecieversID =eR3C1LXJhzaK3fPzCpcuTuz9jZB3
                                                                                 at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                                 at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
                                                                                 at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
                                                                                 at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                                                                                 at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                                                                                 at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
                                                                                 at android.database.sqlite.SQLiteDatabase.updateWithOnConflict(SQLiteDatabase.java:1577)
                                                                                 at android.database.sqlite.SQLiteDatabase.update(SQLiteDatabase.java:1525)
                                                                                 at com.appmaster.akash.messageplus.Chat.mMessagesSent(Chat.java:913)
                                                                                 at com.appmaster.akash.messageplus.Chat.sendMessage(Chat.java:704)
                                                                                 at com.appmaster.akash.messageplus.Chat.access$900(Chat.java:75)
                                                                                 at com.appmaster.akash.messageplus.Chat$5.onClick(Chat.java:441)
                                                                                 at android.view.View.performClick(View.java:6256)
                                                                                 at android.view.View$PerformClick.run(View.java:24701)
                                                                                 at android.os.Handler.handleCallback(Handler.java:789)
                                                                                 at android.os.Handler.dispatchMessage(Handler.java:98)
                                                                                 at android.os.Looper.loop(Looper.java:164)
                                                                                 at android.app.ActivityThread.main(ActivityThread.java:6541)
                                                                                 at java.lang.reflect.Method.invoke(Native Method)
                                                                                 at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
                                                                                 at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)

Answer 1

UPDATE will update every row unless you specify which rows to update.

As such long returnVariable2 = db.update(TABLE_USER_DATA, contentValues2,null,null);,

as the selection (3rd parameter) is null will update every row.

Selection is a WHERE clause (less the WHERE keyword); the 4th parameter can be used to pass arguments into the WHERE clause replacing ?'s on a one by one basis.

• passing arguments this way has the advantage that they will be properly enclosed/handled and
• offers protection against SQL injection.

E.g. you most likely want something along the lines of :-

//contentValues2.put(KEY_ID, uid); //<<<<<<<<<< YOU PROBABLY DO NOT WANT TO CHANGE THE ID, so commented out
contentValues2.put(KEY_MESSAGES_SENT, userData+1);
String whereclause = KEY_ID + "=?";
String[] whereargs = new String[]{uid};
long returnVariable2 = db.update(TABLE_USER_DATA, contentValues2,whereclause,whereargs);

Note that update returns an int not a long and that it will never be -1 as it's the number of rows that have been updated so 0 or greater. So you need to check for 0 not -1 for the Toast

Answer 2

You are specifically setting your where clause and where args parameter in the db.update call to null. That's why the command is updating all rows, because there is no where clause.

You need to change the last two parameters in this line:

long returnVariable2 = db.update(TABLE_USER_DATA, contentValues2,null,null);

Here's the details from the official docs:

https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#update(java.lang.String,%20android.content.ContentValues,%20java.lang.String,%20java.lang.String[])

Answer 3

    ContentValues cv = new ContentValues();
    cv.put("isSynced", 1);
    db.update(TABLE_NAME, cv, "id =" + recID, null);

update the code with where condition. Hope it will works