Why my SQLite cannot create table ID column?

I am creating apps in android studio, I create a table to store user's name, email, password, and also user's ID. The user's name, email and password is passed through user's input, while the ID is generated by computer automatically, when I create a table for those information, the above 3 column works fine but the I cannot create user's ID column.

Here is my code in creating a database table:

public class DatabaseHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME ="contacts.db";
private static final String TABLE_NAME ="contacts";
private static final String COLUMN_ID ="id";
private static final String COLUMN_NAME ="name";
private static final String COLUMN_EMAIL ="email";
private static final String COLUMN_PASS ="pass";
SQLiteDatabase db;
private static final String TABLE_CREATE = "create table contacts (id INTEGER primary key NOT NULL ," +
        "name text not null, email text not null, pass text not null);";

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

@Override
public void onCreate(SQLiteDatabase db) {  // create database
    db.execSQL(TABLE_CREATE);
    this.db = db;
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    String query = "DROP TABLE IF EXISTS " + TABLE_NAME;
    db.execSQL(query);
    this.onCreate(db);
}

public void insertContact(Contact c){
    db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    String query = "select * from contacts";
    Cursor cursor = db.rawQuery(query, null);

    int count = cursor.getCount();

     values.put(COLUMN_ID, count);
    values.put(COLUMN_NAME, c.getUser_name());
    values.put(COLUMN_EMAIL, c.getUser_email());
    values.put(COLUMN_PASS, c.getUser_password());

    db.insert(TABLE_NAME, null, values); // insert into database
    db.close();
}

public String searchPass(String uname){
    db = this.getReadableDatabase();
    String query = "select uname, pass from " + TABLE_NAME;
    Cursor cursor = db.rawQuery(query, null);
    String a, b;
    b = "not found";

    if (cursor.moveToFirst()){
        do {
            a = cursor.getString(0);

            if (a.equals(uname)){
                b = cursor.getString(1);
                break;
            }
        }while (cursor.moveToNext());
    }
    return b;
}

When I am testing my app, I taped into "name =abc, email=abc, password=abc", it shows following issues:

07-12 15:26:27.621 7943-7943/com.example.haoch.myapplication E/SQLiteLog: (1) table contacts has no column named id 07-12 15:26:27.625 7943-7943/com.example.haoch.myapplication E/SQLiteDatabase: Error inserting email=abc name=abc pass=abc id=0 android.database.sqlite.SQLiteException: table contacts has no column named id (code 1): , while compiling: INSERT INTO contacts(email,name,pass,id) VALUES (?,?,?,?)

1 answer

  • answered 2018-07-12 07:33 L Kemp

    Have you changed the database structure? If this is the case, every time you change something on the database structure you have to increase the DB Version:

    private static final int DATABASE_VERSION = 2;
    

    alternatively you can reinstall the app or or clear the app's data on the device