Column not Creating in Android SQLite Database

Question

private static final String DATABASE_NAME = "assignmentsdb";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_TABLE = "assignmentsTable";

public static final String KEY_MODULE = "moduleCode";
public static final String KEY_NAME = "assignmentName";
public static final String KEY_PRO = "marksProportion";
public static final String KEY_DUE = "whenDue";
public static final String KEY_PROG = "Progress";

public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
                KEY_MODULE + " TEXT, " +
                KEY_NAME + " TEXT, " +
                KEY_PROG + " INTEGER, " +
                KEY_DUE + " TEXT, " +
                KEY_PRO + " INTEGER);"      
        );
    }

When I try to insert into this table I get an error telling me the KEY_PROG and KEY_DUE columns do not exist. When I comment them out, insert and return the results the rest work correctly. I cannot figure out why the 2 columns are not being created? Thanks.

Uninstall and reinstall. http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run — laalto, Apr 15 '14 at 12:04
post full SQLite helper class, sometime clearing the app data in app manager fixes the problem, need to view the whole class — Syed Waqas, Apr 15 '14 at 12:28
Did you try creating table with full text sql without variables ? Did you upgrade your db version ? — Tugrul, Apr 15 '14 at 13:00

Mouad EL Fakir · Answer 1 · 2014-04-15T13:08:42.597

i think your sql query is correct with (KEY_PROG and KEY_DUE) or without, so what is the problem then?

well i think the problem in the process of creating the table it self, let me explain what i mean , when you are creating an sqlite db in android you specify a version number (1 in your case) so if your table "assignmentsTable" created without (KEY_PROG and KEY_DUE) you can't change it again, so when you are trying to access thos columns which basically don't exist you'll get an error msg, so to add those columns you have to do the following changes:

change the version number to 2 (or higher).
implement the onUpgrade method.
and execut the folloxing queries in it (in onUpgrade):

SQL queries:

String q1 = "ALTER TABLE " + DATABASE_TABLE + " ADD " + KEY_PROG + " INTEGER";
String q2 = "ALTER TABLE " + DATABASE_TABLE + " ADD " + KEY_DUE + " TEXT";

Or just you can leave your code as it is and simply add this implementation to it: After(changing the version number of course)

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);

        // Create tables again
        onCreate(db);
}

Column not Creating in Android SQLite Database

1 Answers1