Android. SQLite Exception: no such column _ingredients

Question

Im having some troubles trying to get the info stored in my database and showing it in a ListView. When I start the application it crashes.

My MainActivity:

private ListViewAdapter adapter;
private ArrayList<ListItem> itemList;
private ListView list;
private DatabaseAdapter receptDB;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_list);
    setupButton();
    setupListView();
    setupDatabase();
    addObject();


}

private void setupDatabase() {
    receptDB = new DatabaseAdapter(this);
    receptDB.open();
    refreshListView();
}


private void setupButton() {
    Button addItemButton = (Button) findViewById(R.id.addItemButton);
    addItemButton.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            buttonClicked();
        }
    });
}

private void buttonClicked() {
    //get EditText
    Intent newItemIntent = new Intent(List_Page.this, Add_Object.class);
    startActivity(newItemIntent);
    finish();
}

private void setupListView() {
    itemList = new ArrayList<ListItem>();
    adapter = new ListViewAdapter(List_Page.this, itemList);
    list = (ListView) findViewById(R.id.listItem);
    list.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
        @Override
        public boolean onItemLongClick(AdapterView<?> parent, View view,
                                       int position, long id) {
            return true;
        }
    });
    View header = ((LayoutInflater) getSystemService(Context.LAYOUT_INFLATER_SERVICE)).inflate(R.layout.activity_list_item, null);
    list.addHeaderView(header);
    list.setAdapter(adapter);

}

private void addObject(){
    Intent intent = getIntent();
    if (intent.hasExtra(Constants.KEY_RECEPT_NAME)) {
        String name = intent.getExtras().getString(Constants.KEY_RECEPT_NAME);
        String kategory = intent.getExtras().getString(Constants.KEY_KATEGORY);
        String ingredients = intent.getExtras().getString(Constants.KEY_INGREDIENTS);
        String directions = intent.getExtras().getString(Constants.KEY_DIRECTIONS);
        Bitmap image = (Bitmap) intent.getParcelableExtra(Constants.KEY_IMAGE);
        //byte[] imageBytes = getBytes(image);
        ListItem newObject = new ListItem(name,kategory,ingredients,directions, image);
        //itemList.add(newObject);
        receptDB.insertReceptItem(newObject);
        refreshListView();
    }
}
    private void refreshListView() {
    Cursor cursor = receptDB.getAllRows();
    String[] fromFieldNames = new String[] {DatabaseAdapter.KEY_NAME, DatabaseAdapter.KEY_KATEGORY};
    int[] toViewIDs = new int[] {R.id.receptName, R.id.kategory};
    SimpleCursorAdapter myCursorAdapter;
    myCursorAdapter = new SimpleCursorAdapter(getBaseContext(), R.layout.activity_list_item, cursor, fromFieldNames, toViewIDs, 0);
    ListView myList = (ListView) findViewById(R.id.listItem);
    myList.setAdapter(myCursorAdapter);
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {

    // Inflate the menu; this adds items to the action bar if it is present.
    getMenuInflater().inflate(R.menu.main, menu);
    return true;
}

@Override
protected void onDestroy() {
    super.onDestroy();
    receptDB.close();
}

Database:

public class DatabaseAdapter {

private static final String TAG = "DBAdapter";
private static final String DATABASE_NAME = "receptlist.db";
private static final int DATABASE_VERSION = 2;

private static final String DATABASE_TABLE = "receptlistitems";

public static final String KEY_ID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_KATEGORY = "kategory";
public static final String KEY_INGREDIENTS = "ingredients";
public static final String KEY_DIRECTIONS = "directions";
//bitmap?

public static final int COLUMN_NAME_INDEX = 1;
public static final int COLUMN_KATEGORY_INDEX = 2;
public static final int COLUMN_INGREDIENTS_INDEX = 3;
public static final int COLUMN_DIRECTIONS_INDEX = 4;

public static final String[] ALL_KEYS = new String[] {KEY_ID, KEY_NAME, KEY_KATEGORY, KEY_INGREDIENTS, KEY_DIRECTIONS};

private static final String DATABASE_CREATE = "create table "
        + DATABASE_TABLE + " (" + KEY_ID
        + " integer primary key autoincrement, " + KEY_NAME
        + " text not null, " + KEY_KATEGORY
        + " text, " + KEY_INGREDIENTS
        + " text not null, " + KEY_DIRECTIONS
        + " text not null);";

private final Context context;
private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DatabaseAdapter(Context context) {
    this.context = context;
    DBHelper = new DatabaseHelper(context);
}

public DatabaseAdapter open(){
    db = DBHelper.getWritableDatabase();
    return this;
}

public void close(){
    DBHelper.close();
}

//Neue Liste von Daten in Datenbank
public long insertReceptItem(ListItem item) {
    ContentValues itemValues = new ContentValues();
    itemValues.put(KEY_NAME, item.getName());
    itemValues.put(KEY_KATEGORY,item.getKategory());
    itemValues.put(KEY_INGREDIENTS, item.getIngredients());
    itemValues.put(KEY_DIRECTIONS, item.getDirection());

    //Speichern in die Datenbank
    return db.insert(DATABASE_TABLE, null, itemValues);
}

//Löschen eines Items durch den primary Key
public boolean deleteItem(long itemID){
    String where = KEY_ID + "=" + itemID;
    return db.delete(DATABASE_TABLE, where, null) != 0;
}

public void deleteAll() {
    Cursor c = getAllRows();
    long rowId = c.getColumnIndexOrThrow(KEY_ID);
    if (c.moveToFirst()) {
        do {
            deleteItem(c.getLong((int) rowId));
        } while (c.moveToNext());
    }
    c.close();
}

//Zurückgeben aller Daten in der Datenbank
public Cursor getAllRows(){
    String where = null;
    Cursor c = db.query(true, DATABASE_TABLE, ALL_KEYS, where, null,null,null,null,null);
    if (c != null) c.moveToFirst();
    return c;
}

//Auf bestimmte Reihe zugreifen
public Cursor getRow(long rowId){
    String where = KEY_ID + "=" + rowId;
    Cursor c = db.query(true, DATABASE_TABLE, ALL_KEYS, where, null,null,null,null,null);
    if (c != null) c.moveToFirst();
    return c;
}

private static class DatabaseHelper extends SQLiteOpenHelper{

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

}

Logcat:

08-12 16:41:11.161 20386-20386/de.ur.mi.android.excercises.starter E/SQLiteLog: (1) no such column: ingredients
08-12 16:41:11.163 20386-20386/de.ur.mi.android.excercises.starter D/AndroidRuntime: Shutting down VM
08-12 16:41:11.164 20386-20386/de.ur.mi.android.excercises.starter E/AndroidRuntime: FATAL EXCEPTION: main
                                                                                     Process: de.ur.mi.android.excercises.starter, PID: 20386
                                                                                     java.lang.RuntimeException: Unable to start activity ComponentInfo{de.ur.mi.android.excercises.starter/de.ur.mi.android.excercises.starter.List_Page}: android.database.sqlite.SQLiteException: no such column: ingredients (code 1): , while compiling: SELECT DISTINCT _id, name, kategory, ingredients, directions FROM receptlistitems
                                                                                         at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2720)
                                                                                         at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2781)
                                                                                         at android.app.ActivityThread.-wrap12(ActivityThread.java)
                                                                                         at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1508)
                                                                                         at android.os.Handler.dispatchMessage(Handler.java:102)
                                                                                         at android.os.Looper.loop(Looper.java:241)
                                                                                         at android.app.ActivityThread.main(ActivityThread.java:6274)
                                                                                         at java.lang.reflect.Method.invoke(Native Method)
                                                                                         at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:886)
                                                                                         at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:776)
                                                                                      Caused by: android.database.sqlite.SQLiteException: no such column: ingredients (code 1): , while compiling: SELECT DISTINCT _id, name, kategory, ingredients, directions FROM receptlistitems
                                                                                         at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                                         at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:895)
                                                                                         at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:506)
                                                                                         at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                                                                                         at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                                                                                         at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
                                                                                         at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
                                                                                         at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1318)
                                                                                         at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1165)
                                                                                         at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1036)
                                                                                         at de.ur.mi.android.excercises.starter.DatabaseAdapter.getAllRows(DatabaseAdapter.java:96)
                                                                                         at de.ur.mi.android.excercises.starter.List_Page.refreshListView(List_Page.java:109)
                                                                                         at de.ur.mi.android.excercises.starter.List_Page.setupDatabase(List_Page.java:51)
                                                                                         at de.ur.mi.android.excercises.starter.List_Page.onCreate(List_Page.java:42)
                                                                                         at android.app.Activity.performCreate(Activity.java:6720)
                                                                                         at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1119)
                                                                                         at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2673)
                                                                                         at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2781) 
                                                                                         at android.app.ActivityThread.-wrap12(ActivityThread.java) 
                                                                                         at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1508) 
                                                                                         at android.os.Handler.dispatchMessage(Handler.java:102) 
                                                                                         at android.os.Looper.loop(Looper.java:241) 
                                                                                         at android.app.ActivityThread.main(ActivityThread.java:6274) 
                                                                                         at java.lang.reflect.Method.invoke(Native Method) 
                                                                                         at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:886) 
                                                                                         at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:776)

Thanks in advance!

Did you add the column `ingredients` in your schema after you install the app the first time? — Bob, Aug 12 '17 at 15:03
If you change anything in the database (like adding a column), you have to increase the Database version in `SQLiteOpenHelper` and handle that change in `onUpgrade()`. Or the simple solution is to uninstall and reinstall the app. — Bob, Aug 12 '17 at 15:20
I have added that as an answer with explanation. If it's working would you please mark it as correct. — Bob, Aug 12 '17 at 15:27

score 0 · Accepted Answer · answered Aug 12 '17 at 15:27

If you change anything in the database schema (like adding a column), you have to increase the Database version in SQLiteOpenHelper. If you increase the value of the version you will get a callback to onUpgrade and you have to handle that database change there.

private static class DatabaseHelper extends SQLiteOpenHelper{

    DatabaseHelper(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION); // increased version
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         // Either add the column, or drop and create the table again. 
    }

}

Or the simple solution is to uninstall and reinstall the app.

Android. SQLite Exception: no such column _ingredients

1 Answers1