Android SQLite Error: “no such column: name…”

Question

I have a proble with my DB. I Had two columns and my app worked good, but when I tried to add a new column a catched a problem:

12-03 21:04:33.716: I/SqliteDatabaseCpp(726): sqlite returned: error code = 1, msg = no such column: Content3, db=/data/data/com.example.pp/databases/MY_DATABASE

public class SQLiteAdapter {

public static final String MYDATABASE_NAME = "MY_DATABASE";
public static final String MYDATABASE_TABLE = "MY_TABLE";
public static final int MYDATABASE_VERSION = 1;
public static final String KEY_ID = "_id";
public static final String KEY_CONTENT1 = "Content1";
public static final String KEY_CONTENT2 = "Content2";
public static final String KEY_CONTENT3 = "Content3";

//create table MY_DATABASE (ID integer primary key, Content text not null);
private static final String SCRIPT_CREATE_DATABASE =
    "create table " + MYDATABASE_TABLE + " ("
    + KEY_ID + " integer primary key autoincrement, "
    + KEY_CONTENT1 + " text, "
    + KEY_CONTENT2 + " text, "
    + KEY_CONTENT3 + " text);";

this is an insert function

public long insert(String content1, String content2, String content3){

    ContentValues contentValues = new ContentValues();
    contentValues.put(KEY_CONTENT1, content1);
    contentValues.put(KEY_CONTENT2, content2);
    contentValues.put(KEY_CONTENT3, content3);
    return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues);
}

and this a cursor

public Cursor queueAll(){
    String[] columns = new String[]{KEY_ID, KEY_CONTENT1, KEY_CONTENT2, KEY_CONTENT3};
    Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, 
            null, null, null, null, null);

    return cursor;
}

Please, help me to identify my problem. Help to beginners in Android dev :)

I'am sorry but how to handle database version upgrade? To write an upgrade function? — Dmitry Boytsov, Dec 03 '12 at 17:41
As @njzk2 stated, increase the `MYDATABASE_VERSION` to 2 and all should work, each time you make a change to the database you need to increase the version number. — jnthnjns, Dec 03 '12 at 17:41
no, it should not all work by magic, you need to write an upgrade function, usually (easily) droping and recreating your database. alternatively, you can ALTER your DB, but that's dangerous. — njzk2, Dec 03 '12 at 17:46
Unfortunatelly, a handle update doesn't help(( Mistake doesn't disappear — Dmitry Boytsov, Dec 03 '12 at 18:10
Thank you! I find a problem. In spite of I handle update version, the problem still exists. I update a name of database, and my app starts good!Thank you very much guys! — Dmitry Boytsov, Dec 03 '12 at 18:24

score 1 · Answer 1 · edited May 23 '17 at 12:23

If you are populating the table on creation OR the table contents don't need to be retained from Version 1 to the next version then Auto Increment your table version number as noted above and add the following to your SQLiteAdapter:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion >= newVersion)
        return;
    db.execSQL("DROP TABLE IF EXISTS " + MYDATABASE_TABLE);
    onCreate(db);
}

Please note that this will remove the table and re-create. If the contents must remain then you will have to write a ALTER TABLE script that fits your changes.

If the table must remain then please take a look at this StackOverflow Q&A for referral of how to accomplish an Alter Table in onUpgrade(). Again, you will have to modify to meet your needs of what you have changed, and the next time you make changes to the Database, you will again have to increment the DB Version # as well as rewrite to match the changes you made.

score 0 · Answer 2 · answered Dec 03 '12 at 18:11

0

When you are updating the columns or the table you need to update the database version even. If you have more than one table in your database you need to make sure that the version numbers of the database in the files are the same. If there is a conflict in the version numbers, then the tables won't update concurrently.

answered Dec 03 '12 at 18:11

Priety

308
1
4
19

Thank you! I find a problem. In spite of I handle update version, the problem still exists. I update a name of database, and my app starts good!Thank you very much guys! – Dmitry Boytsov Dec 03 '12 at 18:23

Android SQLite Error: “no such column: name…”

2 Answers2