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

Question

I'm getting and error like the one showed belowe when I try to put a new entry in my database. I search trough it for hours now, but I'm not able to detect whats wrong. Any input would be superb!

Here is the error from LogCat.

02-27 23:02:51.451: E/SQLiteLog(6777): (1) table dager has no column named brutto
02-27 23:02:51.451: E/SQLiteDatabase(6777): Error inserting brutto=0 date=21.03.2013      
hours=4
02-27 23:02:51.451: E/SQLiteDatabase(6777): android.database.sqlite.SQLiteException:    
table dager has no column named brutto (code 1): , while compiling: INSERT INTO     
dager(brutto,date,hours) VALUES (?,?,?)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at 
android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at           android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at com.adev.timelonn.DatabaseHandler.addDay(DatabaseHandler.java:79)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at com.adev.timelonn.AddHours.onClick(AddHours.java:99)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.view.View.performClick(View.java:4084)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.view.View$PerformClick.run(View.java:16966)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.os.Handler.handleCallback(Handler.java:615)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.os.Handler.dispatchMessage(Handler.java:92)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.os.Looper.loop(Looper.java:137)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at android.app.ActivityThread.main(ActivityThread.java:4931)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at java.lang.reflect.Method.invokeNative(Native Method)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at java.lang.reflect.Method.invoke(Method.java:511)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at 
com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:791)
02-27 23:02:51.451: E/SQLiteDatabase(6777):     at 
com.android.internal.os.ZygoteInit.main(ZygoteInit.java:558)
02-27 23:02:51.451: E/SQLiteDatabase(6777): at dalvik.system.NativeStart.main(Native   
Method)

My database file.

 public class DatabaseHandler extends SQLiteOpenHelper {
 private static final int DATABASE_VERSION = 1;

 // Database Name
 private static final String DATABASE_NAME = "timeliste";

 // Contacts table name
 private static final String TABLE_DAYS = "dager";

 // Contacts Table Columns names
 private static final String KEY_ID = "id";
 private static final String KEY_DATE = "date";
 private static final String KEY_HOURS = "hours";
 private static final String KEY_UB = "ub";
 private static final String KEY_BRUTTO = "brutto";

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

 // Creating Tables
 @Override
  public void onCreate(SQLiteDatabase db)        
 String CREATE_DAY_TABLE = "CREATE TABLE " + TABLE_DAYS + "(" 
 + KEY_ID + " INTEGER PRIMARY KEY," + KEY_DATE + " TEXT," + KEY_HOURS + " INTEGER,     
     " 
 + KEY_UB + " INTEGER," + KEY_BRUTTO + "INTEGER," + ")"; 
 db.execSQL(CREATE_DAY_TABLE);
 }

 // Upgrading database
 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // Drop older table if existed
  db.execSQL("DROP TABLE IF EXISTS " + TABLE_DAYS);

  // Create tables again
  onCreate(db);
 }

 /**
  * All CRUD(Create, Read, Update, Delete) Operations
  */

 // Adding new contact
 void addDay(AddNewDay newday) {
  SQLiteDatabase db = this.getWritableDatabase();


  ContentValues values = new ContentValues();
  values.put(KEY_DATE, newday.getDate()); // GetDate
  values.put(KEY_BRUTTO, newday.getBrutto()); // GetBruttoLønn
  values.put(KEY_HOURS, newday.getHours()); // GetHours
  values.put(KEY_UB, newday.getUb()); // GetUBTillegg

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

 // Get single day
 AddNewDay getContact(int id) {
  SQLiteDatabase db = this.getReadableDatabase();

  Cursor cursor = db.query(TABLE_DAYS, new String[] { KEY_ID,
    KEY_DATE, KEY_HOURS, KEY_BRUTTO, KEY_UB }, KEY_ID + "=?",
    new String[] { String.valueOf(id) }, null, null, null, null);
  if (cursor != null)
   cursor.moveToFirst();

  AddNewDay newday = new AddNewDay(Integer.parseInt(cursor.getString(0)),
    cursor.getString(1), Integer.parseInt(cursor.getString(2)),
    Integer.parseInt(cursor.getString(3)),Integer.parseInt(cursor.getString(4)));
  // return contact
  return newday;
 }

 // Getting All Contacts
 public List<AddNewDay> getAllContacts() {
  List<AddNewDay> contactList = new ArrayList<AddNewDay>();
  // Select All Query
  String selectQuery = "SELECT  * FROM " + TABLE_DAYS;

  SQLiteDatabase db = this.getWritableDatabase();
  Cursor cursor = db.rawQuery(selectQuery, null);

  // looping through all rows and adding to list
  if (cursor.moveToFirst()) {
   do {
    AddNewDay days = new AddNewDay();
    days.setID(Integer.parseInt(cursor.getString(0)));
    days.setDate(cursor.getString(1));
    days.setHours(Integer.parseInt(cursor.getString(2)));
    days.setUb(Integer.parseInt(cursor.getString(3)));
    days.setBrutto(Integer.parseInt(cursor.getString(4)));
    // Adding contact to list
    contactList.add(days);
   } while (cursor.moveToNext());
  }

  // return contact list
  return contactList;
 }

 // Updating single contact
 public int updateDay(AddNewDay newday) {
  SQLiteDatabase db = this.getWritableDatabase();

  ContentValues values = new ContentValues();
  values.put(KEY_ID,  newday.getID());
  values.put(KEY_DATE, newday.getDate());
  values.put(KEY_HOURS, newday.getHours());
  values.put(KEY_UB, newday.getUb());
  values.put(KEY_BRUTTO, newday.getUb());

  // updating row
  return db.update(TABLE_DAYS, values, KEY_ID + " = ?",
    new String[] { String.valueOf(newday.getID()) });
 }

 // Deleting single contact
 public void deleteDay(AddNewDay newday) {
  SQLiteDatabase db = this.getWritableDatabase();
  db.delete(TABLE_DAYS, KEY_ID + " = ?",
    new String[] { String.valueOf(newday.getID()) });
  db.close();
 }

 // Getting contacts Count
 public int getContactsCount() {
  String countQuery = "SELECT  * FROM " + TABLE_DAYS;
  SQLiteDatabase db = this.getReadableDatabase();
  Cursor cursor = db.rawQuery(countQuery, null);
  cursor.close();

  // return count
  return cursor.getCount();
 }


}

And then my AddNewDay file.

  public class AddNewDay {

 //private variables
     int _id;
     String _date;
     int _hours;
     int _ubtillegg;
     int _brutto;        

     // Empty constructor
     public AddNewDay(){
     }
     // constructor
     public AddNewDay(int id, String date, int hours, int ubtillegg, int            brutto){
      this._id = id;
      this._date = date;
      this._hours = hours;
      this._ubtillegg = ubtillegg;
      this._brutto = brutto;
     }       
     // constructor
     public AddNewDay(String date, int hours, int brutto){
      this._date = date;
      this._hours = hours;
      this._brutto = brutto;
     }
     // getting ID
     public int getID(){
      return this._id;
     }

     // setting id
     public void setID(int id){
      this._id = id;
     }

     // getting date
     public  String getDate(){
      return this._date;
     }

     // setting date
     public void setDate(String date){
      this._date = date;
     }

     // getting hours
     public int getHours(){
      return this._hours;
     }

     // setting hours
     public void setHours(int hours){
      this._hours = hours;
     }

     // getting ubtillegg
     public int getUb(){
      return this._ubtillegg;
     }

     // setting ubtillegg
     public void setUb(int ub){
      this._ubtillegg = ub;
     }

     // getting brutto
     public int getBrutto(){
      return this._brutto;
     }

     // setting brutto
     public void setBrutto(int brutto){
      this._brutto = brutto;
     }

             public String toString() {
         return _date + " jobbet du " + _hours + " timer.";
             }  
            }

And this is how I add a new entry in the database.

        // # Makes a new object of newday.
        AddNewDay newday = new AddNewDay ();
        newday._date = "21.03.2013";
        newday._id = 1;
        newday._hours = 4;
        newday._ubtillegg = 1500;

        // # Open databse connection and writes new day.
        DatabaseHandler connect = new DatabaseHandler (this);
        connect.addDay(newday);

        // # Updates the dblist with entries.
        GlobalVariables.dblist = connect.getAllContacts();

You missed some commas in your CREATE_DAY_TABLE String. And, without them, you don't have a column named "brutto." At first glance it looks like you need them after hours and ub (at least). — Charlie Collins, Feb 27 '13 at 22:47
Also, you can connect to the DB via the shell and poke around, that might be helpful (dump the table structure there, or export it and use a tool like SQLite Manager). — Charlie Collins, Feb 27 '13 at 22:51
Oh, fixed those. Updated question. Did not change anything , but I assume it fixed a possible error number 2. :) — chriskvik, Feb 27 '13 at 22:54
Did you clear-data after you fixed it? Chances are, your DB wasn't recreated. — 323go, Feb 27 '13 at 22:55
You can't have more than one `PRIMARY KEY` clause in a single `CREATE TABLE` cf [documentation](http://www.sqlite.org/lang_createtable.html) — SteveR, Feb 27 '13 at 23:03
No, I assumed Eclipse re-installed it when i pushed it again? I'l try to remove the app manually. :) — chriskvik, Feb 27 '13 at 23:04
Your question still doesn't have the commas, so did you really fix that, or no? Also, while it's true you can only have one primary key, you can have a COMPOUND primary key (a single key made of multiple fields). — Charlie Collins, Feb 28 '13 at 14:56
Fixed them, found the error. Dont know what causes it tough.. all fields are created correctly. Except the field "KEY_BRUTTO" in the dbexplorer it shows as bruttoINTEGER so something is wrong with the last + KEY_BRUTTO + "INTEGER," + ")" as INTEGER and brutto are beeing thrown together into a single field name. — chriskvik, Mar 01 '13 at 00:18

chriskvik · Accepted Answer · 2014-02-05T20:02:41.623

44

So basicly I found the solution. I'm still confused about how stupid it was. And clearly database work i super-sensitive material! My problem was I forgot a white-space in the last "INTEGER" value. So instead of " INTEGER" i wrote "INTEGER", and that gave me a row called "bruttoINTEGER" instead of "brutto". So a white-space was the error. I rewrote the createTable function to this :

 public void onCreate(SQLiteDatabase db) {
   String CREATE_DAY_TABLE = "CREATE TABLE " + TABLE_DAYS + "(" 
 + KEY_ID + " INTEGER PRIMARY KEY," + KEY_DATE + " TEXT," + KEY_HOURS + " INTEGER, " 
 + KEY_UB + " INTEGER," + KEY_BRUTTO + " INTEGER" + ");"; 
 db.execSQL(CREATE_DAY_TABLE);
 }

edited Feb 05 '14 at 20:02

answered Mar 01 '13 at 00:31

chriskvik

1,281
2
10
32

1

I had a comma after the last column declaration (hurray for copy paste errors) -_- – Warpzit Mar 24 '14 at 11:30
thanks sir.. you really shocked me how stupid I am, just for a single little space I wasted 1 hour. – Mohammed Sufian Mar 31 '16 at 17:35
1

oh man.. you saved my life... i am just looking someone to kick my butts.. how can I be so stupid... cheers!! – Haseeb Zulfiqar Dec 08 '16 at 09:55

score 3 · Answer 2 · edited May 06 '17 at 20:19

3

private static final int VERSION = 4;

Change the version it worked fine for me

edited May 06 '17 at 20:19

Necronet

6,704
9
49
89

answered Feb 20 '15 at 08:42

velraj

71
1

1

1> Unistall from your device and run it again. or 2> Change version in your "DatabaseHandler" class – Ashton Nov 02 '15 at 06:42

score 1 · Answer 3 · answered May 21 '14 at 07:28

i agree with iNzzane above. this problem is mainly caused by syntax. for example, previously my code was correct. here it is:

String CREATE_PRODUCTS_TABLE = "CREATE TABLE " + TABLE_PRODUCTS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_MESSAGEADDRESS
                + " TEXT," + COLUMN_MESSAGEBODY + " TEXT " + ")";

the above code was running correct but i added another column and it started causing the mentioned error. below is the erroneous code:

String CREATE_PRODUCTS_TABLE = "CREATE TABLE " + TABLE_PRODUCTS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_MESSAGEADDRESS
                + " TEXT," + COLUMN_MESSAGEBODY + " TEXT" + COLUMN_MESSAGETIME + " LONG" + ")";

as you can see, when i added the new column i forgot to include a comma after type TEXT. this means that when this code is executed, there will be syntax error as the compiler will read the last part as:

COLUMN_MESSAGEBODY TEXTCOLUMN_MESSAGETIME LONG

as opposed to:

COLUMN_MESSAGEBODY TEXT, COLUMN_MESSAGETIME LONG

solution: ensure that your syntax is correct and you heed to spaces and commas.

score 0 · Answer 4 · answered Feb 27 '13 at 22:50

0

I´m not sure but maybe you forgot the " , " in the query before "ub" and "brutto" fields

String CREATE_DAY_TABLE = "CREATE TABLE " + TABLE_DAYS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_DATE + " TEXT," + KEY_HOURS + " INTEGER PRIMARY KEY, " + KEY_UB + " INTEGER PRIMARY KEY, " + KEY_BRUTTO + "INTEGER PRIMARY KEY" + ")"; db.execSQL(CREATE_DAY_TABLE);

answered Feb 27 '13 at 22:50

Flip120

99
1
2
10

Don't post an answer if you're "not sure" -- especially if all you're doing is copy a comment. – 323go Feb 27 '13 at 22:52
Thanks for the suggestion. But unfortunately it did not change anything. It looks like it has something to do with the passing of variables to a column. Or the creation of the column field KEY_UB. – chriskvik Feb 27 '13 at 22:57
Yes, sorry is my fault, is not the copy, i add ' , ' – Flip120 Feb 27 '13 at 22:58

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

4 Answers4

Linked