Why none proposed solution is not working when I try to update a record in SQLite Database?

Question

I am a novice user,I am trying to update a record with some fields and nothing special. I noticed! that this may be answered a lot of times but none of the proposed answers is working and I dont know where to check in my code to find the solution. I have the following :

public int updateUser(User user) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    values.put(U_ID, user.getId());
    values.put(U_NAME, user.getName());
    values.put(U_EMAIL, user.getEmail());
    values.put(U_ZIP, user.getZip());
    values.put(U_CREATED_AT, user.getCreated_at());

    int res = db.update("login_user", values, "U_ID" + "=?", new String[] {String.valueOf(user.getId())});

    return res;
}

I have tried

int res = db.update("login_user", values, "U_ID" + " = ?", new String[] {String.valueOf(user.getId())});

int res = db.update("login_user", values, "U_ID" + "=?", new String[] {(UserId)});

int res = db.update(MYTABLE, values, U_ID + "=?", new String[] {String.valueOf(user.getId())});

I increased my Database version to make it empty I saved a new record so

My Data are not null, but I get as res=0 and not an expected res=1 and with no errors

What I am doing wrong and where to look?

When the ID is a number, why are you comparing it against a string? — CL., Dec 08 '16 at 14:52
So you mean I should do int res = db.update(MYTABLE, values, U_ID + "=?", new Int[] {user.getId()}); — , Dec 08 '16 at 14:54
That will not work because `update` takes only strings as parameters. So you must not use a parameter for this. — CL., Dec 08 '16 at 14:55
can you clarify your question... "I increased my database version to make it empty", and then you try to update a row in an empty database? ... you shouldn't expect to get anything right (since there is nothing to update)? — Angel Koh, Dec 08 '16 at 15:03
your 3rd method should be correct... can you show your CREATE_TABLE statement too ? and the values for MYTABLE and U_ID — Angel Koh, Dec 08 '16 at 15:14

score 0 · Answer 1 · edited May 23 '17 at 11:45

0

The response from the Sqlite Update method is the number of rows updated.

If the record exists in the DB then it will return 1 (or more)

If you've updated the DB number all the records are delted - so verify the record exists 1st.

If it's returning 0 then I suspect you don't have the record in the DB in the first place - so 0 records are updated.

If you're expecting 1 - then first check if the record exists, via ADB - see this answer on how to check the contents of your DB.

edited May 23 '17 at 11:45

Community

1
1

answered Dec 08 '16 at 15:05

Zain

2,336
1
16
25

I checked there is a record – Dec 08 '16 at 15:10
And does the record match the condition? i.e. is UserId that you're passing in as a parameter present? – Zain Dec 08 '16 at 15:20

score 0 · Accepted Answer · answered Dec 08 '16 at 15:09

0

herein lies your problem

I increased my Database version to make it empty

assuming your database is empty, then you should use the insert method instead. updating an empty table will not have any effects because the WHERE condition in the update statement will always return false.

db.insertWithOnConflict (String table, 
            null, //nullColumnHack  
            values, 
            SQLiteDatabase.CONFLICT_REPLACE);

this will create an entry if the userId is not found, or replace the existing userId row if there is a conflict

answered Dec 08 '16 at 15:09

Angel Koh

12,479
7
64
91

I checked there is a record – Dec 08 '16 at 15:11
1

@NoviceUser, you may also want to check if your record have the same UserId as the one in values? – Angel Koh Dec 08 '16 at 15:13
1

Yes the ID was different because it was updating an MySQL database as well thank you Angel and people – Dec 08 '16 at 15:23

Why none proposed solution is not working when I try to update a record in SQLite Database?

2 Answers2