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

Question

I'm getting this error - 07-03 12:29:18.643: E/SQLiteLog(5181): (1) table accounts has no column named otherNotes

This is my code:

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "accountsManager";
private static final String TABLE_ACCOUNTS = "accounts";

private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_USERID = "userId";
private static final String KEY_PASSWORD = "password";
private static final String KEY_LOGINURL = "loginUrl";
private static final String KEY_OTHERNOTES = "otherNotes";

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

public void onCreate(SQLiteDatabase db) {
    String CREATE_ACCOUNTS_TABLE = "CREATE TABLE " + TABLE_ACCOUNTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TITLE + " TEXT,"
            + KEY_USERID + " TEXT," + KEY_PASSWORD + " TEXT," + KEY_LOGINURL + " TEXT,"
            + KEY_OTHERNOTES + " TEXT" + ");";
db.execSQL(CREATE_ACCOUNTS_TABLE);
}


public void addAccount(AccountDetails account) {
    SQLiteDatabase db = this.getWritableDatabase();
    System.out.println("Hello!");   


    ContentValues values = new ContentValues();
    values.put(KEY_TITLE, account.getTitle()); // Account Title
    values.put(KEY_USERID, account.getUserId()); // account userid
    values.put(KEY_PASSWORD, account.getPassword()); // account password
    values.put(KEY_LOGINURL, account.getLoginUrl()); // account loginurl
    values.put(KEY_OTHERNOTES, account.getOtherNotes()); // account othernotes
    Log.v("title", KEY_TITLE);


    // Inserting Row
    db.insert(TABLE_ACCOUNTS, null, values);
    db.close(); // Closing database connection
}

Also, when I remove the following statement:

values.put(KEY_OTHERNOTES, account.getOtherNotes()); // account othernotes

Then I get the same problem with password...etc. i.e, (1) table accounts has no column named password

Please help!!

Answer 1

It seems that you added some columns later in the database. I do agree with Ken Wolf and you should consider uninstalling and re-installing your app. One better approach is, drop and recreate all tables in onUpdate method, and increase the db version every time you change the schema.

Answer 2

Well, If you are confindent about syntax for creating table, than it may happen when you add new column in your same table, for that...

1) Unistall from your device and run it again.

OR

2) Setting -> app -> ClearData

OR

3) Change DATABASE_NAME in your "DatabaseHandler" class ( I faced same problem. But I suuceed by changing DATABASE_NAME.)

OR

4) Change DATABASE_VERSION in your "DatabaseHandler" class (If you have added new column than it will upgrade autimatically)

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

Answer 3

As Benil says in a comment above, change the database version number and run the code again.

Answer 4

The SQL code looks fine. I think that you forgot to call open() on your database object that you created.

add this methods to you SQL class:

private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

public DataBaseMain open() throws SQLException{ 
    // Open the database to make her writeable, must be called before writing 
    // to database

    ourHelper = new DbHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase();
    return this;
}

public void close(){                
    // Closing the database for writing, avoids error.
    ourHelper.close();
}

And use when you want to call you DB.