Android: SQLite: Trouble updating a row

Question

I've been struggling with this problem for awhile now and thought I might seek the help of more experienced programmers. Basically I've created an SQLite database with 1 row of data. I created a button for testing purposes that will pull up the row values, edit them(just one column in this test), and then update the database with the new values. I'm getting an error when I click the button.

This is what's called when the button is clicked...

private void levelUpClick(){
    DBHelper dbHandler = new DBHelper(this, null, null, 1);
    String id = "1";
    int exp;

    CharStats charstats = 
              dbHandler.findCharStats(id);

    exp = Integer.valueOf(charstats.getCharExperience());
    exp += 500;

    dbHandler.updateCharStats(Integer.valueOf(charstats.getID()), String.valueOf(charstats.getCharName()), String.valueOf(charstats.getCharClass()), Integer.valueOf(charstats.getCha`enter code here`rLevel()), Integer.valueOf(charstats.getCharHitPoints()), exp);

    charExperience.setText("" + exp);
}

Here is the DBAdapter class I created, unfortunately it's probably best to paste the whole thing here...

package com.bred.rpg;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;


public class DBHelper extends SQLiteOpenHelper{

public static final String COLUMN_ID = "id";
public static final String COLUMN_CHAR_NAME = "charname";
public static final String COLUMN_CHAR_CLASS = "charclass";
public static final String COLUMN_CHAR_LEVEL = "charlevel";
public static final String COLUMN_CHAR_HITPOINTS = "charhitpoints";
public static final String COLUMN_CHAR_EXPERIENCE = "charexperience";

private static final String DATABASE_NAME = "RPG.db";
private static final String DATABASE_TABLE = "CharStats";
private static final int DATABASE_VERSION = 1;

public DBHelper(Context context, String name, 
        CursorFactory factory, int version) {
    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    String CREATE_CHAR_STATS_TABLE = "CREATE TABLE " +
             DATABASE_TABLE + "("
             + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_CHAR_NAME 
             + " TEXT unique, " + COLUMN_CHAR_CLASS + " TEXT," + COLUMN_CHAR_LEVEL + 
             " INTEGER, " + COLUMN_CHAR_HITPOINTS + " INTEGER," + COLUMN_CHAR_EXPERIENCE + 
             " INTEGER " + ")";
      db.execSQL(CREATE_CHAR_STATS_TABLE);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
      onCreate(db);
}

public void addCharacter(CharStats charstats) {

    ContentValues values = new ContentValues();
    values.put(COLUMN_CHAR_NAME, charstats.getCharName());
    values.put(COLUMN_CHAR_CLASS, charstats.getCharClass());
    values.put(COLUMN_CHAR_LEVEL, charstats.getCharLevel());
    values.put(COLUMN_CHAR_HITPOINTS, charstats.getCharHitPoints());
    values.put(COLUMN_CHAR_EXPERIENCE, charstats.getCharExperience());

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.query(DATABASE_TABLE, null, null, null, null, null, null); 

    if(cursor.moveToFirst()) {
        String rowId = cursor.getString(cursor.getColumnIndex(COLUMN_ID)); 

        db.delete(DATABASE_TABLE, COLUMN_ID + "=?",  new String[]{rowId});
       }

    db.insert(DATABASE_TABLE, null, values);
    db.close();
}

public CharStats findCharStats(String id) {
    String query = "Select * FROM " + DATABASE_TABLE + " WHERE " + COLUMN_ID + " =  \"" + id + "\"";

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(query, null);

    CharStats charstats = new CharStats();

    if (cursor.moveToFirst()) {
        cursor.moveToFirst();
        charstats.setID(Integer.parseInt(cursor.getString(0)));
        charstats.setCharName(cursor.getString(1));
        charstats.setCharClass(cursor.getString(2));
        charstats.setCharLevel(Integer.parseInt(cursor.getString(3)));
        charstats.setCharHitPoints(Integer.parseInt(cursor.getString(4)));
        charstats.setCharExperience(Integer.parseInt(cursor.getString(5)));
        cursor.close();
    } else {
        charstats = null;
    }
        db.close();
    return charstats;
}

public void updateCharStats(int id, String charName, String charClass, int charLevel, int charHitPoints, int charExperience) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues contentValue = new ContentValues();
    contentValue.put("COLUMN_ID", id);
    contentValue.put("COLUMN_CHAR_NAME", charName);
    contentValue.put("COLUMN_CHAR_CLASS", charClass);
    contentValue.put("COLUMN_CHAR_LEVEL", charLevel);
    contentValue.put("COLUMN_CHAR_HITPOINTS", charHitPoints);
    contentValue.put("COLUMN_CHAR_EXPERIENCE", charExperience);

    db.update(DATABASE_TABLE, contentValue, "id =" + id, null);

    db.close();
}

}

Then of course the logcat:

01-28 22:57:42.008: I/Database(25962): sqlite returned: error code = 1, msg = no such   column: COLUMN_CHAR_CLASS
01-28 22:57:42.008: E/Database(25962): Error updating COLUMN_CHAR_CLASS=Warrior COLUMN_CHAR_NAME=Dlgoth COLUMN_CHAR_EXPERIENCE=500 COLUMN_CHAR_HITPOINTS=15 COLUMN_CHAR_LEVEL=1 COLUMN_ID=1 using UPDATE CharStats SET COLUMN_CHAR_CLASS=?, COLUMN_CHAR_NAME=?, COLUMN_CHAR_EXPERIENCE=?, COLUMN_CHAR_HITPOINTS=?, COLUMN_CHAR_LEVEL=?, COLUMN_ID=? WHERE id =1
01-28 22:57:42.008: D/AndroidRuntime(25962): Shutting down VM
01-28 22:57:42.008: W/dalvikvm(25962): threadid=1: thread exiting with uncaught exception (group=0x40015560)
01-28 22:57:42.028: E/AndroidRuntime(25962): FATAL EXCEPTION: main
01-28 22:57:42.028: E/AndroidRuntime(25962): android.database.sqlite.SQLiteException: no such column: COLUMN_CHAR_CLASS: , while compiling: UPDATE CharStats SET COLUMN_CHAR_CLASS=?, COLUMN_CHAR_NAME=?, COLUMN_CHAR_EXPERIENCE=?, COLUMN_CHAR_HITPOINTS=?, COLUMN_CHAR_LEVEL=?, COLUMN_ID=? WHERE id =1
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.database.sqlite.SQLiteCompiledSql.(SQLiteCompiledSql.java:65)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:83)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:41)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1149)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.database.sqlite.SQLiteDatabase.updateWithOnConflict(SQLiteDatabase.java:1706)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.database.sqlite.SQLiteDatabase.update(SQLiteDatabase.java:1656)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at com.bred.rpg.DBHelper.updateCharStats(DBHelper.java:109)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at com.bred.rpg.Character.levelUpClick(Character.java:88)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at com.bred.rpg.Character.onClick(Character.java:67)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.view.View.performClick(View.java:2485)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.view.View$PerformClick.run(View.java:9080)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.os.Handler.handleCallback(Handler.java:587)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.os.Handler.dispatchMessage(Handler.java:92)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.os.Looper.loop(Looper.java:123)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at android.app.ActivityThread.main(ActivityThread.java:3683)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at java.lang.reflect.Method.invokeNative(Native Method)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at java.lang.reflect.Method.invoke(Method.java:507)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
01-28 22:57:42.028: E/AndroidRuntime(25962):    at  dalvik.system.NativeStart.main(Native Method)

Rohan Kandwal · Accepted Answer

You are using

ContentValues contentValue = new ContentValues();
contentValue.put("COLUMN_ID", id);
contentValue.put("COLUMN_CHAR_NAME", charName);
contentValue.put("COLUMN_CHAR_CLASS", charClass);
contentValue.put("COLUMN_CHAR_LEVEL", charLevel);
contentValue.put("COLUMN_CHAR_HITPOINTS", charHitPoints);
contentValue.put("COLUMN_CHAR_EXPERIENCE", charExperience);

Using above will mean searching for columns named COLUMN_ID, COLUMN_CHAR_NAME and so on, when they should be id, charname and so on. Just remove " " from your code above and Android will correctly identify your columns.

Use the following code.

ContentValues contentValue = new ContentValues();
contentValue.put(COLUMN_ID, id);
contentValue.put(COLUMN_CHAR_NAME, charName);
contentValue.put(COLUMN_CHAR_CLASS, charClass);
contentValue.put(COLUMN_CHAR_LEVEL, charLevel);
contentValue.put(COLUMN_CHAR_HITPOINTS, charHitPoints);
contentValue.put(COLUMN_CHAR_EXPERIENCE, charExperience);

Android: SQLite: Trouble updating a row

Answers (2)

Related Questions