SQLite column error: table XXX has no column named YYY

Question

I've looked at the following, among others and have found nothing that matches my problem (as far as I can tell):

android.database.sqlite.SQLiteException: table X has no column named Y: , while compiling: INSERT INTO

Android : Table has no column named "variable name" MySql Database error

Error compiling insert into: No column named ID

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

I'm getting the following SQLite error:

SQLiteDatabase: Error inserting zipCode=23456 address2= city=state address1=123 petName=qwerty phoneNumber=null vetName=zaw emailAddress=
                                             android.database.sqlite.SQLiteException: table vets has no column named petName (code 1): , while compiling: INSERT INTO vets (zipCode,address2,city,address1,petName,phoneNumber,vetName,emailAddress) VALUES (?,?,?,?,?,?,?,?)

I have a feeling that it has to do with the foreign key in VetTable but my research hasn't helped me with understanding what's going on.

VetTable is defined as follows:

public class VetTable {
    public static final String TABLE_VETS = "vets";
    public static final String VET_ID = "vetId";
    public static final String PET_ID = "petId";
    public static final String VET_NAME = "vetName";
    public static final String ADDRESS1 = "address1";
    public static final String ADDRESS2 = "address2";
    public static final String CITY = "city";
    public static final String STATE = "state";
    public static final String ZIP_CODE = "zipCode";
    public static final String PHONE_NUMBER = "phoneNumber";
    public static final String EMAIL_ADDRESS = "emailAddress";

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

    public static final String SQL_DELETE =
            "DROP TABLE" + TABLE_VETS;
}

The relevant code from DBHelper code is as follows:

public void addVetInfo(String name, String address1, String address2, String city, String state, String zip, String phone, String email){
    try {
        db = this.getWritableDatabase();
        values.put(VET_NAME, name);
        values.put(ADDRESS1, address1);
        values.put(ADDRESS2, address2);
        values.put(CITY, city);
        values.put(CITY, state);
        values.put(ZIP_CODE, zip);
        values.put(PHONE_NUMBER, phone);
        values.put(EMAIL_ADDRESS, email);
        db.insert(TABLE_VETS, null, values);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

As can be seen petName is not part of nor has ever been included in VetTable, nor is it part of the argument list in addVetInfo. So, what is SQLite doing that makes it think that petName should be a column in VetTable?

There are two `put(CITY` calls. Anyway, the code shown is not the code that actually gets executed. — CL., Nov 18 '17 at 09:14
CL, what do you mean? What code gets executed that I haven't posted? — Jeff, Nov 19 '17 at 15:55
CL, I just changed that second "CITY" to "STATE" and the code works! I don't see any connection between the duplicate column names and the error message though. — Jeff, Nov 19 '17 at 16:10
The app that actually got executed used `petName`. This code does not. Therefore, you did not actually run the code in the question. — CL., Nov 19 '17 at 16:59
OK. But where did it get petName from? It is not in the argument list for method addVetInfo. Something though, did make SQLite think that it should find petName as a column name due to the duplicate 'CITY' references. What could that possibly be? — Jeff, Nov 19 '17 at 18:05
There was some old version that used `petName`, or you did execute another part of the app. Without the full stack trace, it's impossible to tell. — CL., Nov 19 '17 at 22:39
I know that I never used petName but used petId, but even so, as you say without the stack trace, what caused it is lost. Thanks for your time and help! — Jeff, Nov 25 '17 at 15:20

Jeff · Answer 1 · 2018-01-09T00:37:27.707

0

For others who are having problems with this SQLite error, it appears that it was due in my case to having duplicate column names as pointed out by CL to my post. When I corrected that and reran the code, it worked fine, no error.

So, make sure that your column names are correct for your insert statement.

edited Jan 09 '18 at 00:37

answered Nov 19 '17 at 16:13

Jeff

431
4
16

score 0 · Accepted Answer · answered Jul 31 '19 at 15:27

This can happen when you are trying to insert the data into the table where the column name does not exist or maybe schema that you are trying to insert into has been changed with the latest sequelize.js code.

Possible issue Examples:

Renamed column name (eg: earlier ID and now UUID)
New schema design or complete table schema change
Trying to insert the data with the wrong field name (eg: IDS: 1234) into the table with the column name (eg: ID: 1234)

score -1 · Answer 3 · answered Nov 18 '17 at 01:54

Looks like your values variable already have that value inside the object, check this :

public void addVetInfo(String name, String address1, String address2, String city, String state, String zip, String phone, String email){
    try {
        db = this.getWritableDatabase();
        //INITIALIZE YOUR VARIABLE VALUES HERE
        ContentValues values = new ContentValues();

    //if(name == "Enter vet name") name = null;
    values.put(VET_NAME, name);
    //if(address1 == "Enter address 1" || address1 == " ") address1 = null;
    values.put(ADDRESS1, address1);
    //if(address2 == "Enter address 2") address2 = null;
    values.put(ADDRESS2, address2);
    //if(city == "Enter city") city = null;
    values.put(CITY, city);
    //if(state == "Enter state") state = null;
    values.put(CITY, state);
    //if(zip == "Enter zip") zip = null;
    values.put(ZIP_CODE, zip);
    //if(phone == "Enter phone number") phone = null;
    values.put(PHONE_NUMBER, phone);
    //if(email == "Enter email address") email = null;
    values.put(EMAIL_ADDRESS, email);
    db.insert(TABLE_VETS, null, values);
} catch (SQLException e) {
    e.printStackTrace();
}
}

I found that the error was caused by me having duplicate "CITY" references as pointed out by CL's comment. I can't conceive of why SQLite would say that it had anything to do with "petName" though. — Jeff, Nov 19 '17 at 16:46

SQLite column error: table XXX has no column named YYY

3 Answers3

Linked