Update statement does not update the field

Question

I have a database in which I am storing the number of sent messages. However, when I send a message, the counter is not incremented.

This is the database class (usersdata):

import static android.provider.BaseColumns._ID;
import static com.app.Constants.TABLE_NAME;
import static com.app.Constants.PHONE_NUMBER;
import static com.app.Constants.SMS_SENT;
import static com.app.Constants.SMS_RECEIVED;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class UsersData extends SQLiteOpenHelper {

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

public UsersData (Context ctx) {
    super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + 
            PHONE_NUMBER + " TEXT PRIMARY KEY, " +
            SMS_SENT + " INT, " +
            SMS_RECEIVED + " INT);");
}

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

}

In another class I want to update the SMS_SENT field, and I'm doing it like this:

usersData = new UsersData(this);
SQLiteDatabase db = usersData.getWritableDatabase();
db.rawQuery("UPDATE "+ TABLE_NAME + " SET "+ SMS_SENT + " = " + SMS_SENT + "+1", null);

However, this does not update the corresponding field. What am I doing wrong?

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

It appears someone else had the same problem. It was solved using the function execSQL instead of rawQuery to update the field.

You can read about it here: Increase the value of a record in android/sqlite database

edited May 23 '17 at 10:24

Community

1
1

answered Jan 13 '13 at 18:11

Niklas Ekman

943
1
9
26

Thank you for your answer. Unfortunately this didn't solve my problem either, but I'm starting to think that this may be some kind of database problem, since sometimes updates are not saved even if I try to edit values with a SQLite editor. – user1301428 Jan 13 '13 at 18:55

score 2 · Answer 2 · answered Jan 13 '13 at 21:07

Following good database design it is almost always a good idea to have a field ID with an integer data type and have that value auto increment in an effort to create unique records. For your particular problem it can be easily solved by just adding an ID column and set it to an INTEGER data type and not INT as you would do with high-performance databases like (Postgresql, MySQL, etc...). Try changing your schema to something like this and it should do the trick:

    db.execSQL("CREATE TABLE " + TABLE_NAME + " (" +
        ID + " INTEGER PRIMARY KEY, " + 
        PHONE_NUMBER + " TEXT, " +
        SMS_SENT + " INTEGER, " +
        SMS_RECEIVED + " INTEGER);"
    );

All you have to do now is just pass in the values for PHONE_NUMBER, SMS_SENT, SMS_RECEIVED and your counter (the ID field) should start to increment accordingly. For more information you can check out this post.

score 2 · Accepted Answer · answered Jan 13 '13 at 22:58

2

I solved it in the following way:

ContentValues args = new ContentValues();
args.put(SMS_SENT, sent+1);
db.update(TABLE_NAME, args, ID, new String[] {"id_value"});

answered Jan 13 '13 at 22:58

user1301428

1,743
3
25
57

score 2 · Answer 4 · answered Jan 17 '13 at 01:38

2

The update sql statement is missing a where clause so would update all rows. Try this:

db.rawQuery("UPDATE "+ TABLE_NAME + " SET "+ SMS_SENT
    + " = " + SMS_SENT + "+1 WHERE " + PHONE_NUMBER
    + " = '" + phoneNumber +"'", null);

answered Jan 17 '13 at 01:38

Justin Mclean

1,615
13
14

score 1 · Answer 5 · answered Jan 08 '13 at 16:09

1

Try this,

While creating table use INTEGER instead of INT. It should work.

db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + 
        PHONE_NUMBER + " TEXT PRIMARY KEY, " +
        SMS_SENT + " INTEGER, " +
        SMS_RECEIVED + " INTEGER);");

answered Jan 08 '13 at 16:09

vinothp

9,939
19
61
103

SQLite will change to INTEGER automatically. Read [this](http://www.sqlite.org/datatype3.html) It will not solve the problem. – Tamás Cseh Jan 08 '13 at 16:11
Can you update that field using dummy value? Try that if its get updated then the problem is in coding. If not then its database problem – vinothp Jan 08 '13 at 16:18
@CsehTamas yes i agree with you. It just a try – vinothp Jan 08 '13 at 16:19
So the problem is with the database. Delete that database and run with new one. It may work. – vinothp Jan 08 '13 at 17:26

score 0 · Answer 6 · answered Jan 08 '13 at 16:19

0

chnge your UPDATE query as:

int newcount=SMS_SENT+1;
db.rawQuery("UPDATE "+ TABLE_NAME + " SET "+ 
                       SMS_SENT + " = " + newcount + "", null);
                        ^^^^^

and make sure SMS_SENT is String column name not an number

answered Jan 08 '13 at 16:19

ρяσѕρєя K

132,198
53
198
213

1

SMS_SENT is the name of the field, not an integer value – Tamás Cseh Jan 08 '13 at 16:21
@CsehTamás : then what is output of `SMS_SENT + "+1"` ? means you are incrementing column name with 1 ? – ρяσѕρєя K Jan 08 '13 at 16:22
concatenated the field name with "+1" – Tamás Cseh Jan 08 '13 at 16:23
@CsehTamás : means you have created coulmn as `SMS_SENT INT` and trying to instead String – ρяσѕρєя K Jan 08 '13 at 16:25
@CsehTamás : you have created column name with Integer value ? – ρяσѕρєя K Jan 08 '13 at 16:27
i don't want to do anything :D it's not my question, please ask the OP these questions – Tamás Cseh Jan 08 '13 at 16:27
I just saw in the code, he posted that SMS_SENT is an integer field – Tamás Cseh Jan 08 '13 at 16:28
I didn't downvote your question! I think someone who upvote my comment, downvote your question. It wasn't me, I'm true. – Tamás Cseh Jan 08 '13 at 16:28
@CsehTamás : plz you can also see UPDATE query OP is first SMS_SENT as column name and then put `SMS_SENT + 1` in Column . as i mention in my answer if SMS_SENT is int then it's not possible to use it as column name – ρяσѕρєя K Jan 08 '13 at 16:33
I meant SMS_SENT is an integer field in the database. What he could try to do is actually seems correct, because in SQLite you can do this like: "update table_name set col_name=col_name+1;" if col_name is integer in the database. But I don't understand why you are argue with me... – Tamás Cseh Jan 08 '13 at 16:42
@CsehTamás : sorry actually you have comment me that's why i think u have downvote me . but if your statement is true then i think sqlite also not accept column name as Integer Value . as in SQL column name and Table name must me String – ρяσѕρєя K Jan 08 '13 at 16:45
SMS_SENT is the name of the field, and I wanted to update it taking its old value and adding one. However, I changed my query putting yours, but still nothing is updated :( – user1301428 Jan 08 '13 at 16:48
At the moment I wrote the comment, I saw that someone downote and I had a bad feeling, you will suspect me... It's a misunderstanding and sorry for that. I don't know anyone can view who downvoted, but if someone can, maybe helps, if not, just trust me! :) (and some comments ago I wrote "I didn't downvote your question!",typo, it's answer, but never mind. – Tamás Cseh Jan 08 '13 at 16:48
@user1301428 : if SMS_SENT is column name means you are using integer as column name ? – ρяσѕρєя K Jan 08 '13 at 16:54
SMS_SENT is a column name that stands for the string "sms_sent", it's not an integer – user1301428 Jan 08 '13 at 16:56
@user1301428 : thanks for this, now you are inserting `" + SMS_SENT + "+1"` which means `sms_sent + 1` an String value in SMS_SENT ? but you create SMS_SENT column as `INT` then how we insert an String value in INT column ? – ρяσѕρєя K Jan 08 '13 at 17:02
Forget about that, I don't understand what you are saying. I want my query to look like this: "UPDATE TABLE_NAME SET SMS_SENT = 5". Is it possible to do? – user1301428 Jan 08 '13 at 17:04
@user1301428 : have you tried `db.rawQuery("UPDATE "+ TABLE_NAME + " SET sms_sent = 5", null);` static query just for test? – ρяσѕρєя K Jan 08 '13 at 17:17
It doesn't work either.. I really don't know what to say, especially because this is a trivial operation on any other programming language – user1301428 Jan 08 '13 at 17:27
@user1301428 : solved or not ? if not then plz edit your question with whole UsersData class code – ρяσѕρєя K Jan 08 '13 at 18:08
No it's not solved... I've updated my question adding the whole UsersData code – user1301428 Jan 10 '13 at 18:13
@user1301428 : where you have updated im not getting any update in current post so plz post full relevant class code and also logcat result when app is crashing – ρяσѕρєя K Jan 10 '13 at 18:35
You asked or the whole UsersData code and I've added it. The LogCat does not show any error because the app does not crash, it simply doesn't update the field. – user1301428 Jan 10 '13 at 18:59

Update statement does not update the field

6 Answers6