SQLite table deleted now new table is not creating

Question

i deleted my old table with the statement: db.execSQL("DROP TABLE IF EXISTS " + TABLE_REGISTER); written in method addUser();

i think table is deleted. But now when i register new User. User registration failed. I think new table is not creating. I also have deleted the above statement from addUser() method.

public class DBHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "agent.db";
    private static final int DATABASE_VERSION = 1;

    private static final String KEY_ID = "[ID]";
    private static final String KEY_FNAME = "[FirstName]";
    private static final String KEY_LNAME = "[LastName]";
    private static final String KEY_REGDATE = "[RegistrationDate]";
    private static final String KEY_USERID = "[UserID]";
    private static final String KEY_PASSWORD = "[Password]";

    private static final String TABLE_REGISTER = "tbRegister";
    private static final String CREATE_TABLE_REGISTER = "CREATE TABLE " + TABLE_REGISTER + "("
                                                        + KEY_ID + "INTEGER PRIMARY KEY,"
                                                        + KEY_FNAME + "TEXT NOT NULL,"
                                                        + KEY_LNAME + "TEXT NOT NULL,"
                                                        + KEY_REGDATE + "TEXT NOT NULL,"
                                                        + KEY_USERID + "TEXT NOT NULL,"
                                                        + KEY_PASSWORD + "TEXT NOT NULL )";


    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_REGISTER);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_REGISTER);
        onCreate(db);

    }

    public long addUser(User user) {

        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ID, user.getId());
        values.put(KEY_FNAME, user.getfName());
        values.put(KEY_LNAME, user.getlName());
        values.put(KEY_REGDATE, user.getRegDate());
        values.put(KEY_USERID, user.getUserID());
        values.put(KEY_PASSWORD, user.getPassword());

        return db.insert(TABLE_REGISTER, null, values);
    }

    public List<User> getAllUsers() {
        List<User> userList = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * from " + TABLE_REGISTER, null);

        if (cursor.moveToFirst()){
            do {
                int id = cursor.getInt(0);
                String fName = cursor.getString(1);
                String lName = cursor.getString(2);
                String regDate = cursor.getString(3);
                String userID = cursor.getString(4);
                String password = cursor.getString(5);

                User user = new User(id, fName, lName, regDate, userID, password);
                userList.add(user);

            }while (cursor.moveToNext());
            cursor.close();
        }

        return userList;

    }
}

04-07 17:05:40.344 28263-28263/? E/Zygote: v2
04-07 17:05:40.344 28263-28263/? E/Zygote: accessInfo : 0
04-07 17:06:09.354 28263-28263/pk.edu.vu.agentpawnbroker E/SQLiteLog: (1) table tbRegister has no column named FirstName
04-07 17:06:09.364 28263-28263/pk.edu.vu.agentpawnbroker E/SQLiteDatabase: Error inserting [Password]=ppp [FirstName]=Majid [UserID]=munir64 [RegistrationDate]=07042019 [LastName]=Munir
    android.database.sqlite.SQLiteException: table tbRegister has no column named FirstName (code 1): , while compiling: INSERT INTO tbRegister([Password],[FirstName],[UserID],[RegistrationDate],[LastName]) VALUES (?,?,?,?,?)
    #################################################################
    Error Code : 1 (SQLITE_ERROR)
    Caused By : SQL(query) error or missing database.
        (table tbRegister has no column named FirstName (code 1): , while compiling: INSERT INTO tbRegister([Password],[FirstName],[UserID],[RegistrationDate],[LastName]) VALUES (?,?,?,?,?))
    #################################################################
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1058)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:623)

forpas · Accepted Answer · 2019-04-07T11:48:00.873

The code inside onCreate() is not executed every time you run the app.
So don't expect that the statement:

db.execSQL(CREATE_TABLE_REGISTER);

will recreate the table when you run the app next time.
You must uninstall the app from the device where you test it.
This way the database is deleted and when you rerun the app it will recreate the database and the code inside onCreate() will be executed to create the table.
The method onCreate() is called only when the database does not exist, or you can call it directly say inside onUpgrade().
Also change the create statement to this:

private static final String CREATE_TABLE_REGISTER = "CREATE TABLE " + TABLE_REGISTER + "("
    + KEY_ID + " INTEGER PRIMARY KEY,"
    + KEY_FNAME + " TEXT NOT NULL,"
    + KEY_LNAME + " TEXT NOT NULL,"
    + KEY_REGDATE + " TEXT NOT NULL,"
    + KEY_USERID + " TEXT NOT NULL,"
    + KEY_PASSWORD + " TEXT NOT NULL)";

You missed several spacec between the column names and their data type.
Edit:
Delete this line from addUser():

values.put(KEY_ID, user.getId());

The column KEY_ID is defined as PRIMARY KEY which for SQLite means that it will also be AUTOINCREMENT and you must not supply a value for it.

SQLite table deleted now new table is not creating

1 Answers1