47

Error:

07-03 12:29:18.643: E/SQLiteLog(5181): (1) table accounts has no column named otherNotes

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
}

When I remove:

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

I get the same problem with password:

(1) table accounts has no column named password

user4157124
  • 2,809
  • 13
  • 27
  • 42
Randomly Named User
  • 1,889
  • 7
  • 27
  • 47
  • 3
    pull out you db and check if the mentioned columns are created in those tables.. – CRUSADER Jul 03 '13 at 07:22
  • 7
    Potentially you are working with an old version of your db. Try going into settings/apps and "clear data" on your app. Alternatively uninstall and reinstall it. – Ken Wolf Jul 03 '13 at 07:24
  • 3
    Change the Database version number and run the code again. !! – Benil Mathew Jul 03 '13 at 07:25
  • Just a side note: your `KEY_ID = "id";` should be `KEY_ID = "_id";`. *an irrelevant suggestion*. some details here at [About “_id” field in Android SQLite](http://stackoverflow.com/q/3192064/593709) – Adil Soomro Jul 03 '13 at 07:28
  • 2
    Just un-install the app from device/emulator, and run again. – S.D. Jul 03 '13 at 07:44
  • Please triple check your syntax. I faced this issue for not giving a space between column name and its type. – Haseeb Zulfiqar Dec 08 '16 at 09:58
  • Possible duplicate of [When is SQLiteOpenHelper onCreate() / onUpgrade() run?](http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run) – OneCricketeer Apr 14 '17 at 23:27

17 Answers17

94

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.

Infinite Recursion
  • 6,511
  • 28
  • 39
  • 51
sachin garg
  • 1,316
  • 15
  • 27
  • 3
    Yes, that worked. I went to settings, and cleared all the previous data. It worked perfectly after that. Thank you! – Randomly Named User Jul 03 '13 at 08:05
  • but drop table delete my all the records from db – Ramesh Bhati Jan 19 '22 at 04:37
  • @RameshBhati yup. it will delete all records. If you want to keep that data, create one temp table and then after creating your main table as per new schema, insert records there. remember you have to do all this in onUpgrade method. Maybe you have fixed it yourself by now, just writing here for other's reference. – sachin garg Mar 29 '22 at 12:35
  • @sachingarg i thing, its not proper solution. You can use onUpgrade methods and add colum here. and onCreate methos also add colum – Ramesh Bhati Mar 30 '22 at 05:28
24

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);
        }
Kush
  • 1,080
  • 11
  • 15
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.

Erick Elizondo
  • 188
  • 3
  • 13
dasdasd
  • 1,971
  • 10
  • 45
  • 72
4

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

Community
  • 1
  • 1
3

Try "Clear Data " of your App from Settings. For me this problem was occuring because i was storage data on SD Card and later i added some columns. So if you are saving on SD card delete the previous data.

Ajji
  • 3,068
  • 2
  • 30
  • 31
3

For those who have similar problem and above solution is not worked then check 2 things:

  1. Space between column name and data type like

     COLUMN_NUMBER+" TEXT PRIMARY KEY) not  COLUMN_NUMBER+"TEXT PRIMARY KEY)
    
  2. The order of column during the creation of table should same as map for data insertion like

    ContentValues values = new ContentValues();
    values.put(TEST_ID, testId);
    values.put(CLASS_NAME, className);
    values.put(SUBJECT, subject);
    
    "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + TEST_ID + " INTEGER,"
                + CLASS_NAME + " TEXT,"
                + SUBJECT + " TEXT,"
    
itin
  • 430
  • 3
  • 9
  • 20
2

Here It is your query..TRY this.

 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);";
Mit Bhatt
  • 1,605
  • 2
  • 13
  • 24
2

It's possible if you made a mistake in creation query and then fix it,

So in the file system you have a db, but this db possibly has no such column.

Solution: in emulator find a db file: data/data/com.somecompany.yourapp/databases/db and remove it, then try again.

It's also possible to open this file in some sql explorer and check if there is that column.

Andrew
  • 36,676
  • 11
  • 141
  • 113
2

I ran into the same issue, as I updated my database table in sqlite with new columns during testing phase.

Besides the answers that were already given above, what I found really useful to update SQLite databases (e.g. after changes) is ABD Idea, an AndroidStudio plugin that allows you to:

  1. Uninstall App
  2. List item
  3. Kill App
  4. Start App
  5. Restart App
  6. Clear App Data
  7. Clear App Data and Restart
Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
daniiielak
  • 29
  • 2
1

You can just specify the version like this:

private static final int VERSION = 4;
Utopia
  • 278
  • 1
  • 3
  • 19
1

If your sure your codes are OKAY, just try uninstalling the application, then rerun it agian. This solved my issue. Hope it helped

Anyon
  • 19
  • 1
0

the possible solution also (not the best one) to use different version of DB, like:

public static final int DATABASE_VERSION = 2;

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

it works for me.

S.Manko
  • 151
  • 1
  • 2
  • 15
0

clearing app data from the phone settings worked in my case. either in the android emulator, you can do the same.

  • This is not a correct way, rather a work around. The correct method to achieve is by creating `VERSION` for the database. So I am voting to close this. – Abhishek Dutt Mar 08 '22 at 13:17
0

Current code is

override fun onCreate(db: SQLiteDatabase?) {
    val createPWDTable =
        "CREATE TABLE IF NOT EXISTS $tblPassword ($pwdId INTEGER PRIMARY KEY autoincrement,$title TEXT,$email TEXT,$pwd TEXT,$notes TEXT);"
    db!!.execSQL(createPWDTable)
}

Now add colmn encrypted

override fun onCreate(db: SQLiteDatabase?) {
    val createPWDTable =
        "CREATE TABLE IF NOT EXISTS $tblPassword ($pwdId INTEGER PRIMARY KEY autoincrement,$title TEXT,$email TEXT,$pwd TEXT,$encrypted INT,$notes TEXT);"
    db!!.execSQL(createPWDTable)
}

override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
    if (newVersion == 2) {
        val query = "ALTER TABLE $tblPassword ADD COLUMN $encrypted INT DEFAULT '0'"
        db!!.execSQL(query)
    } 
}

Here if New user then oncreate method directly create table with encrypted value. if Old user then onUpgrade method has check db version and alter the table

Ramesh Bhati
  • 1,239
  • 16
  • 25
-1

I know i have replie to this question to late but maybe someone can solve her problem with this answer. You are put some data later in table database so the best solution is to re name your data base name.

-2

In my case there was a query syntax error (need space b/w column name and datatype ..see attached image) in onCreate() method

to resolve it .. I followed these steps

  1. Correct your syntax error
  2. change DB version number
  3. Clear App data from settings
  4. Uninstall and Run Your app again.]1]1

Hope it helps

Ram Chhabra
  • 421
  • 8
  • 11
-3

Uninstall your app and reinstall your app and after that it will work fi

Sonu Kumar
  • 27
  • 2