Мистическое поведение запросов Android SQLite

Нет необходимости вызывать dbHelper.onCreate(mDb); в open(). SQLiteOpenHelper обрабатывает создание, обновление и т. д. в зависимости от DATABASE_VERSION в конструкторе DBHelper.

В любом случае, я предполагаю, что вы добавили столбец color после запуска базы данных хотя бы один раз. Однако SQLiteOpenHelper не будет проверять CREATE_TABLE автоматически, вы должны сообщить SQLiteOpenHelper об изменении.

Самый простой способ сделать это — реализовать onUpgrade() в DBHelper и добавить единицу в DATABASE_VERSION.

Вот полный пример того, как я это делаю. Я не могу указать вашу ошибку, не видя больше кода, но это должно помочь вам в правильном направлении:

public class MsDbAdapter {
//Keys for Ingredient database
public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "Name";
public static final String KEY_CASESIZE = "CaseSize";
public static final String KEY_PACKSIZE = "PackSize";
public static final String KEY_PACKNICKNAME = "NickName";
public static final String KEY_UNIT = "Unit";
public static final String KEY_CASECOST = "CaseCost";
public static final String KEY_PACKCOST = "PackCost";
public static final String KEY_COUNTUNIT = "CountUnit";
public static final String KEY_QUANTITY = "Quantity";
public static final String KEY_SUPPLIER = "Supplier";
public static final String KEY_CATEGORY = "Category";
public static final String KEY_LOCATION = "Location";
public static final String KEY_BARCODE = "BarCode";

//Keys for Measurement Unit Costs
public static final String KEY_OUNCE = "Ounce";
public static final String KEY_FLOUNCE = "FlOunce";
public static final String KEY_POUND = "Pound";
public static final String KEY_PINT = "Pint";
public static final String KEY_QUART = "Quart";
public static final String KEY_GALLON = "Gallon";
public static final String KEY_HALFGALLON = "HalfGallon";
public static final String KEY_LITER = "Liter";
public static final String KEY_CUP = "Cup";
public static final String KEY_TBL = "Tbl";
public static final String KEY_TSP = "Tsp";
public static final String KEY_GRAM = "Gram";
public static final String KEY_KILO = "Kilogram";
public static final String KEY_MIL = "Milliliter";
public static final String KEY_CENT = "Centiliter";

//Keys for Suppliers, Categories, and Recipes
public static final String KEY_ADDRESS = "Address";
public static final String KEY_PHONE = "Phone";
public static final String KEY_SALESPERSON = "SalesPerson";

public static final String KEY_RECIPE_NAME = "RecipeName";
public static final String KEY_RECIPE_COST = "RecipeCost";
public static final String KEY_INGREDIENTS = "Ingredients";
public static final String KEY_AMOUNT = "Amount";
public static final String KEY_MEASURE_UNIT = "MeasureUnit";
public static final String KEY_TOTAL_INGCOST = "IngCost";


private static final String TAG = "MsDbAdapter";
private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;

/**
 * Database creation sql statement
 */
private static final String DATABASE_CREATE =
    "create table Stockitems (_id integer primary key autoincrement, "
    + "Name text not null, CaseSize float not null, PackSize float not null, Unit text not null, NickName text not null, CaseCost float not null, PackCost float not null, CountUnit text not null, Quantity float not null, Supplier text not null, Category text not null, Location text not null, BarCode text not null, Ounce float not null, FlOunce float not null, Pound float not null, Pint float not null, Quart float not null, HalfGallon float not null, Gallon float not null, Liter float not null, Cup float not null, Tbl float not null, Tsp float not null, Gram float not null, Kilogram float not null, Milliliter float not null, Centiliter float not null);";
private static final String DATABASE_SUPPLIERS_CREATE =
    "create table suppliers (_id integer primary key autoincrement, "
    + "Name text not null, Address text not null, Phone text not null, SalesPerson text not null);";
private static final String DATABASE_CATEGORIES_CREATE =
    "create table categories (_id integer primary key autoincrement, "
    + "Name text not null);";
private static final String DATABASE_LOCATIONS_CREATE =
    "create table locations (_id integer primary key autoincrement, "
    + "Name text not null);";
private static final String DATABASE_RECIPES_CREATE =
    "create table recipes (_id integer primary key autoincrement, "
    + "RecipeName text not null, RecipeCost double not null);";
private static final String DATABASE_INGREDIENTS_CREATE =
    "create table ingredients (_id integer primary key autoincrement, "
    + "RecipeName text not null, Ingredients text not null, Amount float not null, MeasureUnit text not null, IngCost float not null);";


private static final String DATABASE_NAME = "prebeta6";
private static final String DATABASE_TABLE = "Stockitems";
private static final String DATABASE_TABLE_SUPPLIERS = "Suppliers";
private static final String DATABASE_TABLE_CATEGORIES = "Categories";
private static final String DATABASE_TABLE_LOCATIONS = "Locations";
private static final String DATABASE_TABLE_RECIPES = "Recipes";
private static final String DATABASE_TABLE_INGREDIENTS = "Ingredients";
private static final int DATABASE_VERSION = 6;

private final Context mCtx;

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);
        db.execSQL(DATABASE_SUPPLIERS_CREATE);
        db.execSQL(DATABASE_LOCATIONS_CREATE);
        db.execSQL(DATABASE_CATEGORIES_CREATE);
        db.execSQL(DATABASE_RECIPES_CREATE);
        db.execSQL(DATABASE_INGREDIENTS_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS Stockitems");
        db.execSQL("DROP TABLE IF EXISTS Recipes");
        db.execSQL("DROP TABLE IF EXISTS Suppliers");
        db.execSQL("DROP TABLE IF EXISTS Locations");
        db.execSQL("DROP TABLE IF EXISTS Categories");
        db.execSQL("DROP TABLE IF EXISTS Ingredients");
        onCreate(db);
    }
}

/**
 * Constructor - takes the context to allow the database to be
 * opened/created
 * 
 * @param ctx the Context within which to work
 */
public MsDbAdapter(Context ctx) {
    this.mCtx = ctx;
}

/**
 * Open the notes database. If it cannot be opened, try to create a new
 * instance of the database. If it cannot be created, throw an exception to
 * signal the failure
 * 
 * @return this (self reference, allowing this to be chained in an
 *         initialization call)
 * @throws SQLException if the database could be neither opened or created
 */
public MsDbAdapter open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

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


/**
 * 
 */
public long createItem(String Name, double CaseSize, double PackSize, String Unit, String NickName, double CaseCost, double PackCost, String CountUnit, double Quantity, String Supplier, String Category, String Location, String BarCode, Double Ounce, double Flounce, double Pound, double Pint, double Quart, double HalfGallon, double Gallon, double Liter, double Cup, double Tbl, double Tsp, double Gram, double Kilo, double Mil, double Cent) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME, Name);
    initialValues.put(KEY_CASESIZE, CaseSize);
    initialValues.put(KEY_PACKSIZE, PackSize);
    initialValues.put(KEY_UNIT, Unit);
    initialValues.put(KEY_PACKNICKNAME, NickName);
    initialValues.put(KEY_CASECOST, CaseCost);
    initialValues.put(KEY_PACKCOST, PackCost);
    initialValues.put(KEY_COUNTUNIT, CountUnit);
    initialValues.put(KEY_QUANTITY, Quantity);
    initialValues.put(KEY_SUPPLIER, Supplier);
    initialValues.put(KEY_CATEGORY, Category);
    initialValues.put(KEY_LOCATION, Location);
    initialValues.put(KEY_BARCODE, BarCode);
    initialValues.put(KEY_OUNCE, Ounce);
    initialValues.put(KEY_FLOUNCE, Flounce);
    initialValues.put(KEY_POUND, Pound);
    initialValues.put(KEY_PINT, Pint);
    initialValues.put(KEY_QUART, Quart);
    initialValues.put(KEY_HALFGALLON, HalfGallon);
    initialValues.put(KEY_GALLON, Gallon);
    initialValues.put(KEY_LITER, Liter);
    initialValues.put(KEY_CUP, Cup);
    initialValues.put(KEY_TBL, Tbl);
    initialValues.put(KEY_TSP, Tsp);
    initialValues.put(KEY_GRAM, Gram);
    initialValues.put(KEY_KILO, Kilo);
    initialValues.put(KEY_MIL, Mil);
    initialValues.put(KEY_CENT, Cent);

    return mDb.insert(DATABASE_TABLE, null, initialValues);
}
public long createRecipeItem(String Name, double RecipeCost) {
    ContentValues initialValues = new ContentValues();

    initialValues.put(KEY_RECIPE_NAME, Name);
    initialValues.put(KEY_RECIPE_COST, RecipeCost);

    return mDb.insert(DATABASE_TABLE_RECIPES, null, initialValues);
}
public long createIngredientsItem(String Name, String Ingredients, double Amount,         
String MeasureUnit, double IngCost){
ContentValues initialValues = new ContentValues();

    initialValues.put(KEY_RECIPE_NAME, Name);
    initialValues.put(KEY_INGREDIENTS, Ingredients);
    initialValues.put(KEY_AMOUNT, Amount);
    initialValues.put(KEY_MEASURE_UNIT, MeasureUnit);
    initialValues.put(KEY_TOTAL_INGCOST, IngCost);


    return mDb.insert(DATABASE_TABLE_INGREDIENTS, null, initialValues);
}
public long createSupplierItem(String Name, String Address, String Phone, String SalesPerson) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME, Name);
    initialValues.put(KEY_ADDRESS, Address);
    initialValues.put(KEY_PHONE, Phone);
    initialValues.put(KEY_SALESPERSON, SalesPerson);




    return mDb.insert(DATABASE_TABLE_SUPPLIERS, null, initialValues);
}
public long createCategoriesItem(String Name) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME, Name);




    return mDb.insert(DATABASE_TABLE_CATEGORIES, null, initialValues);
}
public long createLocationsItem(String Name) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME, Name);




    return mDb.insert(DATABASE_TABLE_LOCATIONS, null, initialValues);
}

/**
 * Delete the item with the given rowId
 * 
 * @param rowId id of item to delete
 * @return true if deleted, false otherwise
 */
public boolean deleteItem(long rowId) {

    return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean deleteRecipeItem(long rowId) {

    return mDb.delete(DATABASE_TABLE_RECIPES, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean deleteSuppliersItem(long rowId) {

    return mDb.delete(DATABASE_TABLE_SUPPLIERS, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean deleteCategoriesItem(long rowId) {

    return mDb.delete(DATABASE_TABLE_CATEGORIES, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean deleteLocationsItem(long rowId) {

    return mDb.delete(DATABASE_TABLE_LOCATIONS, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean deleteIngredientsItem(long rowId) {

    return mDb.delete(DATABASE_TABLE_INGREDIENTS, KEY_ROWID + "=" + rowId, null) > 0;
}

/**
 * Return a Cursor over the list of all items in the database
 * 
 * 
 */
public Cursor fetchAllItems() {

    return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
            KEY_CASESIZE, KEY_PACKSIZE, KEY_UNIT, KEY_PACKNICKNAME, KEY_CASECOST, KEY_PACKCOST, KEY_COUNTUNIT, KEY_QUANTITY, KEY_SUPPLIER, KEY_CATEGORY, KEY_LOCATION, KEY_BARCODE, KEY_OUNCE, KEY_FLOUNCE, KEY_POUND, KEY_PINT, KEY_QUART, KEY_HALFGALLON, KEY_GALLON, KEY_LITER, KEY_CUP, KEY_TBL, KEY_TSP, KEY_GRAM, KEY_KILO, KEY_MIL, KEY_CENT}, null, null, null, null, null);
}
public Cursor fetchEntireDb(){
    return mDb.query(DATABASE_TABLE, null, null, null, null, null, null);
}
public Cursor fetchAllRecipeItems() {

    return mDb.query(DATABASE_TABLE_RECIPES, new String[] {KEY_ROWID, KEY_RECIPE_NAME}, null, null, null, null, null);
}
public Cursor fetchAllSuppliersItems() {
    if (mDb == null)
    {
    this.open();
    }
    return mDb.query(DATABASE_TABLE_SUPPLIERS, new String[] {KEY_ROWID, KEY_NAME, KEY_ADDRESS, KEY_PHONE, KEY_SALESPERSON}, null, null, KEY_NAME, null, null);
}
public Cursor fetchAllCategoriesItems() {
    if (mDb == null)
    {
    this.open();
    }
    return mDb.query(DATABASE_TABLE_CATEGORIES, new String[] {KEY_ROWID, KEY_NAME}, null, null, KEY_NAME, null, null);
}
public Cursor fetchAllLocationsItems() {
    if (mDb == null)
    {
    this.open();
    }
    return mDb.query(DATABASE_TABLE_LOCATIONS, new String[] {KEY_ROWID, KEY_NAME}, null, null, KEY_NAME, null, null);
}
public Cursor fetchAllIngredientsItems(String[] RecipeName) {
    if (mDb == null)
    {
    this.open();
    }
    String NewIngredientsQString = "RecipeName = ?;";

    return mDb.query(DATABASE_TABLE_INGREDIENTS, new String[] {KEY_ROWID, KEY_RECIPE_NAME, KEY_INGREDIENTS, KEY_AMOUNT, KEY_MEASURE_UNIT, KEY_TOTAL_INGCOST}, NewIngredientsQString, RecipeName, null, null, null);
}






/**
 * Return a Cursor positioned at the item that matches the given rowId
 * 
 * @param rowId id of item to retrieve
 * @return Cursor positioned to matching item, if found
 * @throws SQLException if item could not be found/retrieved
 */
public Cursor fetchItem(long rowId) throws SQLException {


    Cursor mCursor =

        mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
                KEY_CASESIZE, KEY_PACKSIZE, KEY_UNIT, KEY_PACKNICKNAME, KEY_CASECOST, KEY_PACKCOST, KEY_COUNTUNIT, KEY_QUANTITY, KEY_SUPPLIER, KEY_CATEGORY, KEY_LOCATION, KEY_BARCODE, KEY_OUNCE, KEY_FLOUNCE, KEY_POUND, KEY_PINT, KEY_QUART, KEY_HALFGALLON, KEY_GALLON, KEY_LITER, KEY_CUP, KEY_TBL, KEY_TSP, KEY_GRAM, KEY_KILO, KEY_MIL, KEY_CENT}, KEY_ROWID + "=" + rowId, null,
                null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}
public Cursor fetchSuppliersItem(long rowId) throws SQLException {
    if (mDb == null)
    {
    this.open();
    }

    Cursor mCursor =

        mDb.query(true, DATABASE_TABLE_SUPPLIERS, new String[] {KEY_ROWID, KEY_NAME, KEY_ADDRESS, KEY_PHONE, KEY_SALESPERSON}, KEY_ROWID + "=" + rowId, null,
                null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}
public Cursor fetchCategoriesItem(long rowId) throws SQLException {
    if (mDb == null)
    {
    this.open();
    }

    Cursor mCursor =

        mDb.query(true, DATABASE_TABLE_CATEGORIES, new String[] {KEY_ROWID, KEY_NAME}, KEY_ROWID + "=" + rowId, null,
                null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}
public Cursor fetchLocationsItem(long rowId) throws SQLException {
    if (mDb == null)
    {
    this.open();
    }

    Cursor mCursor =

        mDb.query(true, DATABASE_TABLE_LOCATIONS, new String[] {KEY_ROWID, KEY_NAME}, KEY_ROWID + "=" + rowId, null,
                null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}
public Cursor fetchRecipeItem(long rowId) throws SQLException {
    if (mDb == null)
    {
    this.open();
    }

    Cursor mCursor =

        mDb.query(true, DATABASE_TABLE_RECIPES, new String[] {KEY_ROWID, KEY_RECIPE_NAME, KEY_RECIPE_COST}, KEY_ROWID + "=" + rowId, null,
                null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}

public Cursor fetchIngredientsItem(long rowId) throws SQLException {
    if (mDb == null)
    {
    this.open();
    }

    Cursor mCursor =

        mDb.query(true, DATABASE_TABLE_INGREDIENTS, new String[] {KEY_ROWID, KEY_RECIPE_NAME, KEY_AMOUNT, KEY_TOTAL_INGCOST}, KEY_ROWID + "=" + rowId, null,
                null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}

/**
 * 
 */
public boolean updateItem(long rowId, String Name, double CaseSize, double PackSize, String Unit, String NickName, double CaseCost, double PackCost, String CountUnit, double Quantity, String Supplier, String Category, String Location, String BarCode,Double Ounce, double Flounce, double Pound, double Pint, double Quart, double HalfGallon, double Gallon, double Liter, double Cup, double Tbl, double Tsp, double Gram, double Kilo, double Mil, double Cent) {
    ContentValues args = new ContentValues();
    args.put(KEY_NAME, Name);
    args.put(KEY_CASESIZE, CaseSize);
    args.put(KEY_PACKSIZE, PackSize);
    args.put(KEY_UNIT, Unit);
    args.put(KEY_PACKNICKNAME, NickName);
    args.put(KEY_CASECOST, CaseCost);
    args.put(KEY_PACKCOST, PackCost);
    args.put(KEY_COUNTUNIT, CountUnit);
    args.put(KEY_QUANTITY, Quantity);
    args.put(KEY_SUPPLIER, Supplier);
    args.put(KEY_CATEGORY, Category);
    args.put(KEY_LOCATION, Location);
    args.put(KEY_BARCODE, BarCode);
    args.put(KEY_OUNCE, Ounce);
    args.put(KEY_FLOUNCE, Flounce);
    args.put(KEY_POUND, Pound);
    args.put(KEY_PINT, Pint);
    args.put(KEY_QUART, Quart);
    args.put(KEY_HALFGALLON, HalfGallon);
    args.put(KEY_GALLON, Gallon);
    args.put(KEY_LITER, Liter);
    args.put(KEY_CUP, Cup);
    args.put(KEY_TBL, Tbl);
    args.put(KEY_TSP, Tsp);
    args.put(KEY_GRAM, Gram);
    args.put(KEY_KILO, Kilo);
    args.put(KEY_MIL, Mil);
    args.put(KEY_CENT, Cent);
    return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean updateCount(long rowId, double Quantity, String CountUnit){
    ContentValues args = new ContentValues();

    args.put(KEY_QUANTITY, Quantity);
    args.put(KEY_COUNTUNIT, CountUnit);

    return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean updateRecipeItem(long rowId, String Name, double RecipeCost) {
    ContentValues args = new ContentValues();
    args.put(KEY_RECIPE_NAME, Name);
    args.put(KEY_RECIPE_COST, RecipeCost);

    return mDb.update(DATABASE_TABLE_RECIPES, args, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean updateIngredientsItem(long rowId, String Name, String Ingredient, double Amount, String MeasureUnit, double IngCost) {
    ContentValues args = new ContentValues();
    args.put(KEY_RECIPE_NAME, Name);
    args.put(KEY_INGREDIENTS, Ingredient);
    args.put(KEY_AMOUNT, Amount);
    args.put(KEY_MEASURE_UNIT, MeasureUnit);
    args.put(KEY_TOTAL_INGCOST, IngCost);
    return mDb.update(DATABASE_TABLE_INGREDIENTS, args, KEY_ROWID + "=" + rowId, null) > 0;
}

public boolean updateSuppliersItem(long rowId, String Name, String Address, String Phone, String SalesPerson) {
    ContentValues args = new ContentValues();
    args.put(KEY_NAME, Name);
    args.put(KEY_ADDRESS, Address);
    args.put(KEY_PHONE, Phone);
    args.put(KEY_SALESPERSON, SalesPerson);


    return mDb.update(DATABASE_TABLE_SUPPLIERS, args, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean updateCategoriesItem(long rowId, String Name) {
    ContentValues args = new ContentValues();
    args.put(KEY_NAME, Name);


    return mDb.update(DATABASE_TABLE_CATEGORIES, args, KEY_ROWID + "=" + rowId, null) > 0;
}
public boolean updateLocationsItem(long rowId, String Name) {
    ContentValues args = new ContentValues();
    args.put(KEY_NAME, Name);


    return mDb.update(DATABASE_TABLE_LOCATIONS, args, KEY_ROWID + "=" + rowId, null) > 0;
}


}