How to add new Column to Android SQLite Database?

Question

I have one problem with Android SQLite database.

I have one table which contains one field.StudentFname and that application is working fine with Android 2.3.1 and now if I add another field then my application is not working properly.

Can anyone help me who knows database very well,

Answer 1

The easiest way to create a new column on a table is add some SQL to the onUpgrade() method in SQLiteOpenHelper class. Like:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
        case 1:
            db.execSQL(SQL_MY_TABLE);

        case 2:
            db.execSQL("ALTER TABLE myTable ADD COLUMN myNewColumn TEXT");
    }
}

Answer 2

Simply change in your code

private final int DATABASE_VERSION = 1;

to

private static final int DATABASE_VERSION =2;

Answer 3

I came across link while searching for the same issue. It explains how to use upgrade. https://thebhwgroup.com/blog/how-android-sqlite-onupgrade

it explains why to use this below code

 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
             db.execSQL(DATABASE_ALTER_TEAM_1);
        }
        if (oldVersion < 3) {
             db.execSQL(DATABASE_ALTER_TEAM_2);
        }
    }

Answer 4

@Aamirkhan.i think you would have solved the problem you mentioned in the comments long back ago.i think you didn't increase the data base version. or else the answers here are straight forward.i am writing this because it might help anyone who hasn't increased or changed their data base version number when they are altering a table.

Answer 5

Perhaps a slightly more elegant approach using switch instead of if/then that will upgrade from any schema to the most recent schema...

Here's also a decent page on the syntax for altering a table: http://alvinalexander.com/android/sqlite-alter-table-syntax-examples

public static final String TABLE_TEAM = "team";
public static final String COLUMN_COACH = "coach";
public static final String COLUMN_STADIUM = "stadium";


private static final String DATABASE_ALTER_TEAM_TO_V2 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " TEXT;";

private static final String DATABASE_ALTER_TEAM_TO_V3 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " TEXT;";


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

    switch (oldVersion)
    {
        case 1:
            //upgrade from version 1 to 2
            db.execSQL(DATABASE_ALTER_TEAM_TO_V2);
        case 2:
            //upgrade from version 2 to 3
            db.execSQL(DATABASE_ALTER_TEAM_TO_V3);

        //and so on.. do not add breaks so that switch will
        //start at oldVersion, and run straight through to the latest

    }

}