Table has no column named: SQLite odd behavior on insert

Question

Table has no column named: SQLite odd behavior on insert

I have searched for and read a number of entries for "Table has no column named" like the following:

Android SQLite issue - table ... has no column named Table has no column named

but they do not seem to apply as my app is exhibiting odd "table ___ has no column named ____ (code 1):" errors that depends on what was entered first. The pet table is:

public static final String SQL_CREATE =
        "CREATE TABLE " + TABLE_PETS + " (" +
                PET_ID + " INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, " +
                VET_ID + " INTEGER, " +
                PET_NAME + " TEXT, " +
                " FOREIGN KEY(" + VET_ID + ") " +
                " REFERENCES vets(vetId) ON DELETE CASCADE);";

The Vet table is:

public static final String SQL_CREATE =
        "CREATE TABLE " + TABLE_VETS + "(" +
                VET_ID + " INTEGER PRIMARY KEY UNIQUE, " +
                VET_NAME + " TEXT, " +
                ADDRESS1 + " TEXT, " +
                ADDRESS2 + " TEXT, " +
                CITY + " TEXT, " +
                STATE + " TEXT, " +
                ZIP_CODE + " TEXT, " +
                PHONE_NUMBER + " TEXT, " +
                EMAIL_ADDRESS + " TEXT);";

I have checked the emulator's DB and the structure is as shown above. I deleted the app's data and unistalled then installed the app. I enter a pet's name:

public void addPetName(String name){
    Log.d(TAG, "Entered addPetName");
    try {
        db = this.getWritableDatabase();
        values.put(PET_NAME, name);
        db.insert(TABLE_PETS, null, values);
        Log.d(TAG, "Should have saved " + name + " to the DB");
        } catch (SQLException e) {
        e.printStackTrace();
    }
}

This was successful (from LogCat):

DBHelper: Entered addPetName
DBHelper: Should have saved Tim to the DB

I then tried to add a vet name:

public void addVetInfo(String name, String address1, String address2, String city, String state, String zip, String phone, String email){
    try {
        Log.d(TAG, "Entered addVetInfo");

        db = this.getWritableDatabase();
        Log.d(TAG, "Vet name entered is " + name);
        values.put(VET_NAME, name);
        Log.d(TAG, "Address1 entered is " + address1);
        values.put(ADDRESS1, address1);
        Log.d(TAG, "Address2 entered is " + address2);
        values.put(ADDRESS2, address2);
        Log.d(TAG, "City  entered is " + city);
        values.put(CITY, city);
        Log.d(TAG, "State entered is " + state);
        values.put(STATE, state);
        Log.d(TAG, "Zip entered is " + zip);
        values.put(ZIP_CODE, zip);
        Log.d(TAG, "Phone entered is " + phone);
        values.put(PHONE_NUMBER, phone);
        Log.d(TAG, "Email entered is " + email);
        values.put(EMAIL_ADDRESS, email);
        Log.d(TAG, "Add new vet data to table");
        db.insert(TABLE_VETS, null, values);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

This is unsuccessful (from LogCat):

SQLiteLog: (1) table vets has no column named petName
SQLiteDatabase: Error inserting zipCode= address2= city= address1= state=         
petName=Tig phoneNumber=null vetName=Springhill emailAddress=

android.database.sqlite.SQLiteException: table vets has no column named petName (code 1): , while compiling: INSERT INTO vets(zipCode,address2,city,address1,state,petName,phoneNumber,vetName,emailAddress) VALUES (?,?,?,?,?,?,?,?,?)

Something is goin on in SQLite that prevents data input to tables depending on which data was input first:

• For pet name input first, input vet name fails. As seen in the vet table there is no petName column
• For vet name input first, input pet name fails. As seen in the pet table there is no zipCode column

What could be going on with SQLite? Could it be something due to me not opening/closing the DB connection?

Answer 1

Your issue is likely that you are re-using the same instance of ContentValues values and thus adding to it. So basically the first use will work but subsequent uses could be problematic.

The solution would be to either use values.clear() before adding (putting) values for each method. Or to use a new ContentValues instance for each e.g. use ContentValues values = new ContentValues();

The latter being the more common approach.

So for example the addPetname method could be :-

public void addPetName(String name){
    Log.d(TAG, "Entered addPetName");
    try {
        db = this.getWritableDatabase();
        ContentValues values = new ContentValues(); //<<<<<<<<
        values.put(PET_NAME, name);
        db.insert(TABLE_PETS, null, values);
        Log.d(TAG, "Should have saved " + name + " to the DB");
        } catch (SQLException e) {
        e.printStackTrace();
    }
}

or

public void addPetName(String name){
    Log.d(TAG, "Entered addPetName");
    try {
        db = this.getWritableDatabase();
        values.clear(); //<<<<<<<<
        values.put(PET_NAME, name);
        db.insert(TABLE_PETS, null, values);
        Log.d(TAG, "Should have saved " + name + " to the DB");
        } catch (SQLException e) {
        e.printStackTrace();
    }
}