CODEWITHSUNDEEP
androidsqlitesql-update
Alok Agarwal
Alok Agarwal

Reputation: 3099

SQLite syntax Error on UPDATE

I have a database Scheme in SQLite database as:

CREATE TABLE AppRecord ( sno INTEGER PRIMARY KEY, version Text,fields Text, appname    Text, appid Text, status Text, displayDetails Text, id Text);

i am trying to update my record with the following query

UPDATE AppRecord SET version ="16",
fields = "[{"lbl":"user","type":"text","required":true},
    {"lbl":"age","max":100,"type":"text", "number":true, "required":true,     "maxPrecision":2},
    {"lbl":"address","type":"text", "multiline":true},
    {"lbl":"date","type":"date","minDate":"3","maxDate":"3","required":true}]",
    appname = "Sales", 
    appid = "sales",
    status = "modified",
    id = "52860e0d012bad414cc98162" 
    WHERE appid = "sales";

Every time i try to do it, it is giving me a syntactical error saying:

ERROR android.database.sqlite.SQLiteException: near "lbl": syntax error (code 1)

I tried my best to figure out the error but I am not able to resolve it. Any help is appreciated.

Upvotes: 1

Views: 698

Answers (1)

laalto
laalto

Reputation: 152807

You're trying to put a string value enclosed in " quotes in fields but the value also contains " quotes.

The SQL way to escape " is to double it as "": 

sqlite> create table foo(bar);
sqlite> insert into foo values("string with ""quotes"" embedded");
sqlite> select * from foo;
string with "quotes" embedded

A better way is to use parameter substitution.

In Android Java API this can be using the bindArgs parameter of execSQL(), or more conveniently for update queries, using update() with ContentValues (the whereArg does the same substitution for the WHERE part of the constructed update query).

Upvotes: 2

Related Questions