Android SQLite issue: 'Table [...] has no column named' error

Question

I keep getting error that a column in my table cannot be found. Cannot figure out why this is happening. The column names appear to be spelled correctly.

I'm getting this error : 06-11 21:27:02.701 3703-3703/com.example.luca.contatti E/SQLiteLog﹕ (1) table Contatti has no column named imageUri

This is my DatabaseHandler page:

public class DatabaseHandler extends SQLiteOpenHelper {
    public static final String DB_NAME = "dbContatti",
        TABLE = "Contatti",
        ID = "id",
        NAME = "name",
        PHONE = "phone",
        EMAIL = "email",
        ADDRESS = "address",
        IMAGEURI = "imageUri";
public static final int DB_VERSION = 4;

public DatabaseHandler(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}


@Override
public void onCreate(SQLiteDatabase db) {
    //Creazione della tabella per il DB
    db.execSQL("CREATE TABLE " + TABLE + "(" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + NAME + "TEXT," + PHONE + "TEXT," + EMAIL + "TEXT," + ADDRESS + "TEXT," + IMAGEURI + "TEXT " + ")");

}

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

    onCreate(db);
}

// CRUD: CREATE

public void createContact (ContactList contact){
    SQLiteDatabase db = getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put(NAME, contact.getName());
    values.put(PHONE, contact.getPhone());
    values.put(EMAIL, contact.getEmail());
    values.put(ADDRESS, contact.getAddress());
    values.put(IMAGEURI, contact.getImageUri().toString());

    db.insert(TABLE, null, values);
    db.close();
}

//CRUD: READ

public ContactList getContact(int id){
    SQLiteDatabase db = getReadableDatabase();

    Cursor cursor = db.query(TABLE, new String[] {ID, NAME, PHONE, EMAIL, ADDRESS, /*IMAGEURI*/}, ID + "=?", new String[] {String.valueOf(id)},null,null,null,null );

    if (cursor != null)
        cursor.moveToFirst();

    ContactList contact = new ContactList(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), Uri.parse(cursor.getString(5)));
    db.close();
    cursor.close();
    return contact;
}

//CRUD: UPDATE

public int updateContact(ContactList contact){
    SQLiteDatabase db = getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put(NAME, contact.getName());
    values.put(PHONE, contact.getPhone());
    values.put(EMAIL, contact.getEmail());
    values.put(ADDRESS, contact.getAddress());
    values.put(IMAGEURI, contact.getImageUri().toString());

    return db.update(TABLE, values, ID + "=?", new String[] { String.valueOf(contact.getId()) });
}

//CRUD: DELETE

public void deleteContact(ContactList contact){
    SQLiteDatabase db = getWritableDatabase();

    db.delete(TABLE, ID + "=?", new String[] {String.valueOf(contact.getId())});
    db.close();
}

public int getContactsCount(){
    SQLiteDatabase db = getReadableDatabase();

    Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE, null);
    int count = cursor.getCount();
    db.close();
    cursor.close();
    return count;
}

public List<ContactList> getAllContacts(){
    List<ContactList> contacts = new ArrayList<ContactList>();

    SQLiteDatabase db = getWritableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE, null);

    if(cursor.moveToFirst()){
        do {
            ContactList contact = new ContactList(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), Uri.parse(cursor.getString(5)));
            contacts.add(contact);
        }
        while (cursor.moveToNext());
    }
    return contacts;
}
}

Also when i remove this statement:

values.put(IMAGEURI, contact.getImageUri().toString());

I've problem with other statements.

What can i do for fix this problem? Please help me!!

Answer 1

First of all, you have to put whitespaces between each column names and their type :

db.execSQL("CREATE TABLE " + TABLE + "(" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + NAME + " TEXT," + PHONE + " TEXT," + EMAIL + " TEXT," + ADDRESS + " TEXT," + IMAGEURI + " TEXT " + ")");

Second, in your Cursor :

Cursor cursor = db.query(TABLE, new String[] {ID, NAME, PHONE, EMAIL, ADDRESS, /*IMAGEURI*/}, ID + "=?", new String[] {String.valueOf(id)},null,null,null,null );

you wrote /*IMAGEURI*/ ! So it's normal that some lines later, the cursor.getString(5) doesn't exist, and it crashes too :

ContactList contact = new ContactList(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), Uri.parse(cursor.getString(5)));

Finally make sure your Datamodel is updated. If you just added the imageUri column while the database has been created during previous debug sessions, it will crash. You well overrided onUpgrade method to delete existing tables, but I hope you didn't forget to increment Database version value too ;-)