Android SQLite update query error

Question

I have a table in SQLite where I want to update some of the columns. Here is my query:

String UPDATE_INFORMATION = "UPDATE " + TABLE_USER + " SET "
            + KEY_ADDRESS_OF_LIVING + " = " + addressOfLiving + ", "
            + KEY_PHONE + " = " + phoneNumber + ", "
            + KEY_IDNP + " = " + IDNP
            + " WHERE " + KEY_ID + " = 1";

The values in debug:

UPDATE user SET address = Efdsdfd, phone = 3797333, idnp = 2006002050245 WHERE id = 1

and the error message I get when running this query is:

android.database.sqlite.SQLiteException: no such column: Efdsdfd

Why it takes the "Efdsdfd" as column name?

try this UPDATE user SET 'address' = 'Efdsdfd', 'phone' = '3797333', 'idnp' = '2006002050245' WHERE 'id'=1 — Jinesh Francis
– Jinesh Francis, Commented May 8, 2017 at 13:04
@JineshFrancis ... and why are you quoting the coulmn names?????? — Phantômaxx
– Phantômaxx, Commented May 8, 2017 at 13:06
Anyway, you can use parametric SQL commands or queries and forget about quoting. — Phantômaxx
– Phantômaxx, Commented May 8, 2017 at 13:06

Alvaro Lopez Guerra · Accepted Answer · 2017-05-08 12:44:25Z

2

you are missing the simple quotes. Remember that when you are setting values for string columns, the values should be surrounded by simple quotes. For example :

UPDATE user SET address = Efdsdfd

should be

UPDATE user SET address = 'Efdsdfd'

Also my recommendation is that all constants that you use for the database query (simple quotes, commas, data type, etc) should be placed as Constants in a DbHelper or Constants file and not written.

For example:

String UPDATE_INFORMATION = "UPDATE " + TABLE_USER + " SET " + KEY_ADDRESS_OF_LIVING + " = " + addressOfLiving + ", "

Should be:

String UPDATE_INFORMATION = "UPDATE " + TABLE_USER + " SET " + KEY_ADDRESS_OF_LIVING + " = " + Helper.SIMPLE_QUOTES + addressOfLiving + Helper.SIMPLE_QUOTES + Helper.COMMA

answered May 8, 2017 at 12:44

Alvaro Lopez Guerra

1846 bronze badges

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

4 Comments

John P Over a year ago

Thank you for your response. It would be much easier to write a simple quote that to call the value from a helper class :) The best solution would be to call the SQLite update function rather than running the manual written query, but I do this for testing purposes

Alvaro Lopez Guerra Over a year ago

@AndrewT "The best solution would be to call the SQLite update function" i completely agree with you. But who am i to judge) =) . Have a good testing

Phantômaxx Over a year ago

Or you can use parametric SQL commands or queries and forget about quoting.

Alvaro Lopez Guerra Over a year ago

@Rotwang also true

Apurva Pagare · Accepted Answer · 2017-05-08 13:07:43Z

2

You have to put TEXT values inside single quotes.

answered May 8, 2017 at 13:07

Apurva Pagare

211 bronze badge

2 Comments

Phantômaxx Over a year ago

Or you can use parametric SQL commands or queries and forget about quoting.

John P Over a year ago

I just wanted to see how to run queries as I will have to perform multiple operations that I will be unable to achieve using queries. But for this query you are completely right!

PEHLAJ · Accepted Answer · 2017-05-08 12:39:38Z

1

UPDATE user SET address = Efdsdfd, phone = 3797333, idnp = 2006002050245 WHERE id = 1

The above query is not valid as address is type of VARCHAR or TEXT. You have to put TEXT values inside single quotes.

Updated query string:

String UPDATE_INFORMATION = "UPDATE " + TABLE_USER + " SET "
        + KEY_ADDRESS_OF_LIVING + " = '" + addressOfLiving + "', "
        + KEY_PHONE + " = " + phoneNumber + ", "
        + KEY_IDNP + " = " + IDNP
        + " WHERE " + KEY_ID + " = 1";

Result will be as

UPDATE user SET address = 'Efdsdfd', phone = 3797333, idnp = 2006002050245 WHERE id = 1

answered May 8, 2017 at 12:39

PEHLAJ

10.2k10 gold badges43 silver badges57 bronze badges

3 Comments

John P Over a year ago

Thank you! This seems to have solved my problem. I will reinstall the app and as one of the columns in my sqlite was not created but most sure it was the problem

John P Over a year ago

yes! I had to wait 10 minutes until I can accept the answer. Thanks!

Phantômaxx Over a year ago

Or you can use parametric SQL commands or queries and forget about quoting.

Collectives™ on Stack Overflow

Android SQLite update query error

3 Answers 3

4 Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related