SQLite Update Listview Query Not Correct

Question

I'm getting stuck on the query to perform an update I have the code for performing the update done correctly I'm just not to sure what the query to do this would be if someone could help that would be great. I'm passing through 3 variables and the 3 old variables as well and also the id. I know the query I've done is wrong just an attempt.

public void updateTable(String newDate, String newMethod, String newAmount, int id, String oldDate, String oldMethod, String oldAmount){
    db = this.getWritableDatabase();
    String query = "UPDATE " + TABLE_INCOME + " SET " + COL2 + " " + COL3 + " " + COL4 + " = '" + newDate + newMethod + newAmount + "' WHERE " + COL1 + " = '" + id + "'" +
        " AND " + COL2 + " " + COL3 + " " + COL4 + " = '" + oldDate + oldMethod + oldAmount + "'";
    db.execSQL(query);
}

Irabzz · Answer 1 · 2017-04-25T07:45:52.157

0

SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();

values.put("COL2", newDate);
values.put("COL3", newMethod );
values.put("COL4", newAmount );

long result = db.update("TABLE_INCOME", values, "COL1 = " + "'" + String.valueOf(id) +"'" , null);
Log.i(TAG, " update result : " + result);

don't use too many parameters. if your id is unique its enough. I hope it helps

edited Apr 25 '17 at 07:45

answered Apr 25 '17 at 03:03

Irabzz

91
6

`id` is not a string. – CL. Apr 25 '17 at 07:04
String.valueOf(id) – Irabzz Apr 25 '17 at 07:45
`"..."+id` actually works. The problem is that the `id` column in the database does not contain string values. – CL. Apr 25 '17 at 08:47
your id will be remain as integer. I just converted the id to String for querying purposes. see my edited answer – Irabzz Apr 26 '17 at 02:21
`"'" + String.valueOf(id) +"'"` results in an SQL string value. – CL. Apr 26 '17 at 06:32
If the `id` column does not have numeric affinity, the comparison will always fail. – CL. Apr 26 '17 at 12:21
then just check if the value of id is numeric before you update the database. see this answer http://stackoverflow.com/questions/1102891/how-to-check-if-a-string-is-numeric-in-java – Irabzz Apr 27 '17 at 02:52

SQLite Update Listview Query Not Correct

1 Answers1