Error android.database.sqlite.SQLiteException: no such column: while updating SQLite database

Question

I have one column in SQLite database.It stores Text value.When new value is inserted I need to delete the previous value and add new value in Database. This is method in Class which extends SQLiteOpenHelper to update the column.

public void updateOffline(String data){
        SQLiteDatabase db=this.getWritableDatabase();
        String updateQuery="UPDATE "+OFFLINE_MODE +" SET "+OFFLINE_DATA+" = "+ data;
        db.execSQL(readQuery);
    }

where data is the String I need to UPDATE
I am calling this method in one of Fragment as

db.updateOffline(tempArray.toString());

I am getting error as

android.database.sqlite.SQLiteException: no such column: {"name":"jjajaja","phone":"4545454545","no_people":"2","booktime":"12:46 pm"} (code 1): , while compiling: UPDATE offlinedata SET  offlinequeue =[{"name":"jjajaja","phone":"4545454545","no_people":"2","booktime":"12:46 pm"}]

How to resolve this ?

no such column shows you're providing wrong column name — Ali Ahmed
– Ali Ahmed, Commented Nov 1, 2018 at 7:44

laalto · Accepted Answer · 2018-11-01 07:58:32Z

1

You cannot just dump a JSON string inside SQL and expect it to be syntactically valid.

String literals in SQL go in 'single quotes' but then just dumping data in SQL i quotes exposes you to SQL injection. Use variables instead.

If you use SQLiteDatabse update() method with ContentValues, it uses variables automatically for you.

If you want to keep on working in raw SQL, change

String updateQuery="UPDATE "+OFFLINE_MODE +" SET "+OFFLINE_DATA+" = "+ data;
db.execSQL(updateQuery);

to something like

String updateQuery="UPDATE "+OFFLINE_MODE +" SET "+OFFLINE_DATA+" = ?";
db.execSQL(updateQuery, new Object[] { data });

answered Nov 1, 2018 at 7:58

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error android.database.sqlite.SQLiteException: no such column: while updating SQLite database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related