CODEWITHSUNDEEP
javaandroidsqliteandroid-sqlite
Alexander
Alexander

Reputation: 163

Error no such column in SQLite when updating rows

I'm trying to update data in rows in my DB, but i catch error that there's no such column (no such column 'Moscow' or another)

This is DBHelper code: 

public static final String tableName = "currentWeather";

public static final String KEY_ID = "_id";
public static final String cityName = "city";
public static final String cityTemp = "temperature";

And creating DB: 

sqLiteDatabase.execSQL("create table " + tableName + "(" + KEY_ID + " 
integer primary key autoincrement,"
    + cityName + " text," + cityTemp + " text, " + " UNIQUE(" + cityName + 
"))");

and error shows when i try to execSQl in response: 

sqLiteDatabase.execSQL(
                    "UPDATE " + DBHelper.tableName + " SET " + 
DBHelper.cityTemp + "=" +
                            response.body().getForecastMain().getTemp() + " 
WHERE "
                            + DBHelper.cityName + "=" + cityName);

I expect to update temperature data in rows by cityName

Upvotes: 1

Views: 740

Answers (4)

forpas
forpas

Reputation: 164099

cityName and response.body().getForecastMain().getTemp() are strings and they should be passed surrounded with single quotes to the sql statement:

sqLiteDatabase.execSQL(
    "UPDATE " + DBHelper.tableName + " SET " +  DBHelper.cityTemp + "='" + response.body().getForecastMain().getTemp() + "'" +
    "WHERE " + DBHelper.cityName + " = '" + cityName + "'"
);

But the recommended and safe way of doing the update is with the use of ContentValues and ? as placeholders for the parameters:

ContentValues cv = new ContentValues();
cv.put(DBHelper.cityTemp, String.valueOf(response.body().getForecastMain().getTemp()));
int rows = sqLiteDatabase.update(
    DBHelper.tableName, 
    cv,
    DBHelper.cityName + " = ?",
    new String[] {cityName}
);

You can examine the value of the integer variable rows.
If it is 1 this means that 1 row was updated (because cityName is unique) so the update was successful.

Upvotes: 1

elyar abad
elyar abad

Reputation: 788

Also consider What the data type of response.body().getForecastMain().getTemp() is. If it's int you have to parse it or something, as the data type of the related column is Text.

Upvotes: 1

Syed Ahmed Jamil
Syed Ahmed Jamil

Reputation: 2111

The thing is you need to wrap the values after the = sign in single quotations in the UPDATE statement. As for digits they work in both cases.

For example here is the correct syntax 

UPDATE currentWeather
SET temperature = 45
WHERE
    city = 'Moscow'

But in your code I'm assuming cityName has the value Moscow without the single quotation marks so the converted SQL code will be like this

UPDATE currentWeather
SET temperature = 45
WHERE
    city = Moscow

Now the sql interpreter will think Moscow is some database object or column or something and not a literal value. So you need to surround your values in single quotation marks.

Upvotes: 1

Rakesh
Rakesh

Reputation: 552

I think you have changed column name or add new one (city). So you can fix it by two ways

  1. By uninstall the application from phone
  2. Add column name in upgrade method.

Example:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // If you need to add a column
   if (newVersion > oldVersion) {
      db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
   }
 }

Upvotes: 1

Related Questions