CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Randomly Named User
Randomly Named User

Reputation: 1949

Table accounts has no column named otherNotes

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

Upvotes: 48

Views: 90877

Answers (17)

Ramesh Kumar
Ramesh Kumar

Reputation: 1249

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

Upvotes: 0

Hassan Ijaz
Hassan Ijaz

Reputation: 1

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

Upvotes: 0

Sonu Kumar
Sonu Kumar

Reputation: 27

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

Upvotes: -3

Ram Chhabra
Ram Chhabra

Reputation: 445

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

Upvotes: -2

Mohamed Amine Sekmani
Mohamed Amine Sekmani

Reputation: 59

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.

Upvotes: -1

S.Manko
S.Manko

Reputation: 151

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.

Upvotes: 0

dasdasd
dasdasd

Reputation: 2031

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.

Upvotes: 4

itin
itin

Reputation: 450

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,"

Upvotes: 3

daniiielak
daniiielak

Reputation: 29

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

Upvotes: 2

Anyon
Anyon

Reputation: 19

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

Upvotes: 1

Kush
Kush

Reputation: 1092

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);
        }

Upvotes: 25

Utopia
Utopia

Reputation: 278

You can just specify the version like this:

private static final int VERSION = 4;

Upvotes: 1

Ajji
Ajji

Reputation: 3086

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.

Upvotes: 3

sachin garg
sachin garg

Reputation: 1316

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.

Upvotes: 94

Andrew
Andrew

Reputation: 38039

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.

Upvotes: 2

user2241968
user2241968

Reputation: 71

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

Upvotes: 4

Mit Bhatt
Mit Bhatt

Reputation: 1645

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);";

Upvotes: 2

Related Questions