I'm unable to insert data into my SQLiteDatabase

Question

I'm creating a SQLiteDatabase in Android Studio that loads and calculates the food calories and I'm getting the error below after I press submit (The app then crashes):

2020-02-06 21:36:47.840 26379-26379/? E/SQLiteLog: (1) table food_tbl has no column named calories 2020-02-06 21:36:47.840 26379-26379/? E/SQLiteDatabase: Error inserting name=beer calories=567 recorddate=1581043007840 android.database.sqlite.SQLiteException: table food_tbl has no column named calories (code 1): , while compiling: INSERT INTO food_tbl(name,calories,recorddate) VALUES (?,?,?)

****Here is my DatabaseHandler code:****

public class DatabaseHandler extends SQLiteOpenHelper {

private final ArrayList<Food> foodList = new ArrayList<>();


public DatabaseHandler(Context context) {
    super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
    // create table
    String CREATE_TABLE = "CREATE TABLE " + Constants.TABLE_NAME + "("
            + Constants.KEY_ID + " INTEGER PRIMARY KEY, " + Constants.FOOD_NAME +
            " TEXT, " + Constants.FOOD_CALORIES_NAME + " INT, " + Constants.DATE_NAME + " LONG);";

    db.execSQL(CREATE_TABLE);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(" DROP TABLE IF EXISTS " + Constants.TABLE_NAME);


    // create a new one
    onCreate(db);

}

// Get total items saved
public int getTotalItems() {
    int totalItems = 0;

    String query = " SELECT * FROM " + Constants.TABLE_NAME;
    SQLiteDatabase dba = this.getReadableDatabase();
    Cursor cursor = dba.rawQuery(query, null);

    totalItems = cursor.getCount();

    cursor.close();

    return totalItems;
}

//get total calories consumed
public int totalCalories(){
    int cals = 0;

    SQLiteDatabase dba = this.getReadableDatabase();

    String query = "SELECT SUM( " + Constants.FOOD_CALORIES_NAME + " ) " +
            "FROM " + Constants.TABLE_NAME;

    Cursor cursor = dba.rawQuery(query, null);

    if (cursor.moveToFirst()){
        cals = cursor.getInt(0);
    }

    cursor.close();
    dba.close();


    return cals;
}

//delete food item
public void deleteFood(int id) {

    SQLiteDatabase dba = this.getWritableDatabase();
    dba.delete(Constants.TABLE_NAME, Constants.KEY_ID + " = ?",
            new String[]{ String.valueOf(id)});

    dba.close();
}

//add content to db - add food
public void addFood(Food food) {

    SQLiteDatabase dba = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(Constants.FOOD_NAME, food.getFoodName());
    values.put(Constants.FOOD_CALORIES_NAME, food.getCalories());
    values.put(Constants.DATE_NAME, System.currentTimeMillis());

    dba.insert(Constants.TABLE_NAME, null, values);

    Log.v("Added Food item", "Yesss!!");

    dba.close();
}

// Get all foods
public ArrayList<Food> getFoods() {

    foodList.clear();

    SQLiteDatabase dba = this.getReadableDatabase();

    Cursor cursor = dba.query(Constants.TABLE_NAME,
            new String[]{Constants.KEY_ID, Constants.FOOD_NAME, Constants.FOOD_CALORIES_NAME,
            Constants.DATE_NAME}, null, null, null, null, Constants.DATE_NAME + " DESC ");

    // loop through...
    if (cursor.moveToFirst()) {
        do {

            Food food = new Food();
            food.setFoodName(cursor.getString(cursor.getColumnIndex(Constants.FOOD_NAME)));
            food.setCalories(cursor.getInt(cursor.getColumnIndex(Constants.FOOD_CALORIES_NAME)));
            food.setFoodId(cursor.getInt(cursor.getColumnIndex(Constants.KEY_ID)));

            DateFormat dateFormat = DateFormat.getDateInstance();
            String date = dateFormat.format(new Date(cursor.getLong(cursor.getColumnIndex(Constants.DATE_NAME))).getTime());

            food.setRecordDate(date);

            foodList.add(food);

        }while (cursor.moveToNext());
    }

    cursor.close();
    dba.close();

    return foodList;
}

}

You likely had a previous version of this code with a different column name but didn't change the database version. — Ryan M
– Ryan M ♦, Commented Jun 17, 2021 at 6:47

Junaid · Accepted Answer · 2020-02-07 02:53:15Z

0

Try this

String CREATE_TABLE = "CREATE TABLE " + Constants.TABLE_NAME + "("
            + Constants.KEY_ID + " INTEGER PRIMARY KEY, " + Constants.FOOD_NAME +
            " TEXT, " + Constants.FOOD_CALORIES_NAME + " INT, " + Constants.DATE_NAME + " LONG"+ ")";

answered Feb 7, 2020 at 2:53

Junaid

1,3711 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jermaine Jones Over a year ago

No, that didn't work. I thought that would for sure.

Dipankar Baghel · Accepted Answer · 2020-02-07 03:15:00Z

0

As per the crash log I can understand you need to add the column calories in your table food_tbl.

please add calories column to fix the issue.

answered Feb 7, 2020 at 3:15

Dipankar Baghel

2,0892 gold badges14 silver badges28 bronze badges

Comments

Nidhi Savaliya · Accepted Answer · 2020-02-07 04:23:04Z

0

I think your create table query is wrong so try this below code.

String CREATE_TABLE = "CREATE TABLE " + Constants.TABLE_NAME + "("
        + Constants.KEY_ID + " INTEGER PRIMARY KEY," + Constants.FOOD_NAME +
        " TEXT," + Constants.FOOD_CALORIES_NAME + " INTEGER," + Constants.DATE_NAME + " DATETIME);"

answered Feb 7, 2020 at 4:23

Nidhi Savaliya

1793 silver badges12 bronze badges

1 Comment

Jermaine Jones Over a year ago

That didn't do it either

Raunak Pandey · Accepted Answer · 2020-02-07 11:28:11Z

0

The problem is in your totalCalories() in the cursor.getInt(0) You are retrieving nothing from the database, since you have an integer column in the 1st index but your retrieving it from the 0th index.
Solution:

cursor.getInt(1)

edited Feb 7, 2020 at 11:28

answered Feb 7, 2020 at 4:46

Raunak Pandey

3702 silver badges19 bronze badges

Comments

Jermaine Jones · Accepted Answer · 2020-02-23 01:05:17Z

0

I solved the problem: there was a compatibility issue with the emulator in android studio I was using. I was using Nexus 5 API 24 and apparently that is not compatible with sqlite3. I switched to Pixel 3 API 26 for my emulator and it worked. Thanks for all of your responses. It helped.

answered Feb 23, 2020 at 1:05

Jermaine Jones

213 bronze badges

Collectives™ on Stack Overflow

I'm unable to insert data into my SQLiteDatabase

5 Answers 5

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related