knokout1 knokout1 - 25 days ago 9
Android Question

Android Studio, SQLite, No Such Column Issue

Below is the error I'm getting:

java.lang.RuntimeException: Unable to start activity ComponentInfo{groceryproject.jacob.com.recipelist/groceryproject.jacob.com.recipelist.RecipeList}: android.database.sqlite.SQLiteException: no such column: prep_time (code 1): , while compiling: SELECT id, recipe_name, servings, prep_time, cook_time, ingredients, directions FROM recipes WHERE id=?


This is after I've uninstalled the app and cleared all data to make sure I don't have a previous database messing me up. Below is the code for my database class:

package groceryproject.jacob.com.recipelist;

/**
* Created by Jacob on 11/12/2016.
*/
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class RecipeDB extends SQLiteOpenHelper {

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

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

// Contacts table name
private static final String TABLE_RECIPES = "recipes";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "recipe_name";
private static final String KEY_COOK_TIME = "cook_time";
private static final String KEY_PREP_TIME = "prep_time";
private static final String KEY_SERVINGS = "servings";
private static final String KEY_INGREDIENTS = "ingredients";
private static final String KEY_DIRECTIONS = "directions";

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


@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_RECIPES + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
+ KEY_SERVINGS + " TEXT," + " TEXT," + KEY_COOK_TIME + " TEXT,"
+ KEY_INGREDIENTS + " TEXT," + KEY_DIRECTIONS + " TEXT" + ")";

db.execSQL(CREATE_CONTACTS_TABLE);
}


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

// Create tables again
onCreate(db);
}



void addRecipe(Recipe recipe) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(KEY_NAME, recipe.getRecipeName()); // Contact Name
values.put(KEY_SERVINGS, recipe.getServings()); // Contact Phone
values.put(KEY_PREP_TIME, recipe.getPrepTime());
values.put(KEY_COOK_TIME, recipe.getCookTime());

//Next few lines turns an array list of strings into one string seperated by tabs
String directionsConcat = "";
String ingedientsConcat = "";

if(recipe.getIngredients() != null) {
StringBuilder ingred = new StringBuilder();
for (String s : recipe.getIngredients()) {
ingred.append(s);
ingred.append("\t");
}
ingedientsConcat = ingred.toString();
}

if(recipe.getDirections() != null) {
StringBuilder direct = new StringBuilder();
for (String s : recipe.getDirections()) {
direct.append(s);
direct.append("\t");
}
directionsConcat = direct.toString();
}

values.put(KEY_INGREDIENTS, ingedientsConcat);
values.put(KEY_DIRECTIONS, directionsConcat);


db.insert(TABLE_RECIPES, null, values);
db.close(); // Closing database connection
}


Recipe getRecipe(int id) {
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_RECIPES, new String[] { KEY_ID,
KEY_NAME, KEY_SERVINGS, KEY_PREP_TIME, KEY_COOK_TIME, KEY_INGREDIENTS, KEY_DIRECTIONS },
KEY_ID + "=?", new String[] { String.valueOf(id) }, null, null, null, null); //This line is where the error points to
if (cursor != null)
cursor.moveToFirst();

List<String> directionsList = new ArrayList<>();
List<String> ingredientsList = new ArrayList<>();

String ingredients = cursor.getString(5);
String directions = cursor.getString(6);


if (directions != null){
if(directions.contains("\t")) {
directionsList = Arrays.asList(directions.split("\t"));
}
else{
directionsList = Arrays.asList(directions);
}
}

if (ingredients != null){
if(ingredients.contains("\t")) {
ingredientsList = Arrays.asList(ingredients.split("\t"));
}
else{
ingredientsList = Arrays.asList(ingredients);
}
}

Recipe recipe = new Recipe(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2),
cursor.getString(3), cursor.getString(4), ingredientsList, directionsList);
return recipe;
}

public List<Recipe> getAllRecipes() {
List<Recipe> recipeList = new ArrayList<Recipe>();

String selectQuery = "SELECT * FROM " + TABLE_RECIPES;

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


if (cursor.moveToFirst()) {
do {
Recipe recipe = new Recipe();

recipe.setID(Integer.parseInt(cursor.getString(0)));
recipe.setRecipeName(cursor.getString(1));
recipe.setServingSize(cursor.getString(2));
recipe.setPrepTime(cursor.getString(3));
recipe.setCookTime(cursor.getString(4));

List<String> directionsList = new ArrayList<>();
List<String> ingredientsList = new ArrayList<>();

String ingredients = cursor.getString(5);
String directions = cursor.getString(6);


if (directions != null){
if(directions.contains("\t")) {
directionsList = Arrays.asList(directions.split("\t"));
}
else{
directionsList = Arrays.asList(directions);
}
}


if (ingredients != null){
if(ingredients.contains("\t")) {
ingredientsList = Arrays.asList(ingredients.split("\t"));
}
else{
ingredientsList = Arrays.asList(ingredients);
}
}

recipe.setIngredients(ingredientsList);
recipe.setDirections(directionsList);

recipeList.add(recipe);
} while (cursor.moveToNext());
}


return recipeList;
}


public int updateRecipe(Recipe recipe) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(KEY_NAME, recipe.getRecipeName()); // Contact Name
values.put(KEY_SERVINGS, recipe.getServings()); // Contact Phone
values.put(KEY_PREP_TIME, recipe.getPrepTime());
values.put(KEY_COOK_TIME, recipe.getCookTime());

//Next few lines turns an array list of strings into one string seperated by tabs
String directionsConcat = "";
String ingedientsConcat = "";

if(recipe.getIngredients() != null) {
StringBuilder ingred = new StringBuilder();
for (String s : recipe.getIngredients()) {
ingred.append(s);
ingred.append("\t");
}
ingedientsConcat = ingred.toString();
}

if(recipe.getDirections() != null) {
StringBuilder direct = new StringBuilder();
for (String s : recipe.getDirections()) {
direct.append(s);
direct.append("\t");
}
directionsConcat = direct.toString();
}

values.put(KEY_INGREDIENTS, ingedientsConcat);
values.put(KEY_DIRECTIONS, directionsConcat);

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


public void deleteRecipe(Recipe recipe) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_RECIPES, KEY_ID + " = ?",
new String[] { String.valueOf(recipe.getID()) });
db.close();
}



public int getRecipeCount() {
String countQuery = "SELECT * FROM " + TABLE_RECIPES;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();

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



}


The issue is caused when I call
getRecipe()
from my main Activity class:

package groceryproject.jacob.com.recipelist;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Parcelable;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import java.util.ArrayList;
import java.util.List;

public class RecipeList extends AppCompatActivity{
private RecyclerView mRecyclerView;
private RecyclerView.Adapter mAdapter;
private RecyclerView.LayoutManager mLayoutManager;
private int REQUEST_CODE=1;

//private SQLiteDatabase mDatabase;

//TODO: Create a new taskbar
//TODO: Create a navigaton bar.
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);

RecipeDB dbHelper = new RecipeDB(this);

/*
if(savedInstanceState != null){
recipes = savedInstanceState.getParcelableArrayList("savedRecipes");
}
*/



ArrayList<String> one = new ArrayList<>();
one.add("Test");

Recipe testRecipe = new Recipe("Name", "Four slices", "40", "80", one, one);

dbHelper.addRecipe(testRecipe);
Recipe testTwo = dbHelper.getRecipe(1); //This is where it breaks


List<Recipe> recipes = dbHelper.getAllRecipes();
recipes.add(testTwo); //This is to prove the adapter is working


String log = "No results";
for (Recipe rn : recipes){
log = "Id: " + rn.getID() + ", Name: " + rn.getRecipeName();
}
Log.d("Name, ", log);
//This log is printing no results when the app actually runs


setContentView(R.layout.activity_recipe_list);
mRecyclerView = (RecyclerView) findViewById(R.id.list_recycler_view);


mLayoutManager = new LinearLayoutManager(this);
mRecyclerView.setLayoutManager(mLayoutManager);


mAdapter = new MyAdapter(recipes);
mRecyclerView.setAdapter(mAdapter);




}


The cursor line in
getRecipe()
is where the error log is pointing to. It only happens when I call
getRecipe()
. If I was to create a list from calling
getAllRecipes()
instead, and pass that to the adapter, it doesn't crash on start up but the ArrayList is also empty.

I know the adapter works because if I manually add it to the array list recipes it appears on screen correctly. It's the database that's causing this problem.

I've been working at this for days, and have tried looking at many similar issues on SO, and I just feel stuck. Any advice is appreciated.

Answer

Check the CREATE_CONTACTS_TABLE variable, I don't see a preptime key in there. And Contacts is a strange name for a Recipe table variable....

    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_RECIPES + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_SERVINGS + " TEXT," + " TEXT," + KEY_COOK_TIME + " TEXT,"
            + KEY_INGREDIENTS + " TEXT," + KEY_DIRECTIONS + " TEXT" + ")";

After you add the column, you need to update the DATABASE_VERSION as well to reflect those changes.