Error: android.database.sqlite.Sqliteexception: no such column:

Question

I added a column in the Users table, invited is an integer. I need to make a select in the table filtering this field, and I get the following error:

android.database.sqlite.Sqliteexception: no such column: invited (code 1): while compiling: SELECT * FROM users WHERE invited = '0' OR invited = null

Follow the codes below:

public ArrayList<UserCommunity> getUsersToInvite() {
    ArrayList<UserCommunity> users = new ArrayList<>();

    // 1. build the query
    String query = "SELECT * FROM " + TABLE_USERS +
            "  WHERE invited = '0' OR invited = null";

    // 2. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);

    // 3. go over each row, build book and add it to list
    UserCommunity user = null;
    if (cursor.moveToFirst()) {
        do {
            user = new UserCommunity();
            user.id = Integer.parseInt(cursor.getString(0));
            user.name = cursor.getString(1);
            user.email = cursor.getString(2);
            //user.distance = Integer.parseInt(cursor.getString(9));

            // Add book to books
            users.add(user);
        } while (cursor.moveToNext());
    }

    Log.d("getUsersNearCoordinat()", users.toString());

    // return books
    return users;
}

View:

    public InvitePresenter(InviteView inviteView){

        inflater = (LayoutInflater)inviteView.getContext().getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        serviceDB = new UsersMySQLiteHelper(AppController.getAppContext());

        RequestManager.Users(new FutureCallback<JsonObject>() {
            @Override
            public void onCompleted(Exception e, JsonObject result) {
                SessionManager sessionManager = new SessionManager();
                if (!sessionManager.getInvitedUser()) {
                    usersList = new Gson().fromJson(result.get("data"), new TypeToken<ArrayList<UserCommunity>>() {
                    }.getType());
                    sessionManager.setInvitedUser(true);
                }else{
                    usersList = serviceDB.getUsersToInvite();
                }
                notifyDataSetChanged();
            }
        });
        this.inviteView = inviteView;
}

Creation of the bank:

@Override public void onCreate(Sqlitedatabase db) {

String CREATE_USERS_TABLE = "CREATE TABLE if not exists users ( " +
        "id INTEGER PRIMARY KEY, " +
        "name TEXT, "+
        "email TEXT, "+
        "company TEXT, "+
        "photo_url TEXT, "+
        "departure_time TEXT, "+
        "arrival_time TEXT, "+
        "address_lat DOUBLE,"+
        "address_lng DOUBLE,"+
        "invited INTEGER)";

// create books table
db.execSQL(CREATE_USERS_TABLE);

}

Answer 1

Your CREATE is with if not exists. If you do not delete the old table it will never create the new column. Or you delete the old one:

DROP TABLE users;

Or you use the following command to add the column:

ALTER TABLE users ADD COLUMN invited INTEGER;

Answer 2

I resolved the issue by changing the constant

private Static final int DATABASE_VERSION,

implementing the method:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older books table if existed
    db.execSQL("DROP TABLE IF EXISTS users");

    // create fresh books table
    this.onCreate(db);
}