java - SQLiteException no such table:ItemTable while compiling: Select_id,.... from ItemTable

Question

I've been getting this errors in my logcat for a while now. I tried a lot of things like clearing the data in my app, un-installing and re-installing my app, upgrading my DB version, I even deleted my project and created a new one but everytime i run my app i always get this error. Can someone please help me.

What I'm trying to to do is i have two buttons in an activity add item and view item, everytime i click the view item button I get this error.

When the view button is click it must show the data that I save in my database in a listview but that does not happen because my app always stopped and this show in my logcat.

This is the Image of the activity where the data should display

Here is my logcat:

02-09 05:24:23.850: E/AndroidRuntime(8037):     at com.system.inventorysystem.ViewProductItem.onCreate(ViewProductItem.java:46)
02-09 05:46:59.810: E/AndroidRuntime(13150): FATAL EXCEPTION: main
02-09 05:46:59.810: E/AndroidRuntime(13150): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.system.inventorysystem/com.system.inventorysystem.ViewProductItem}: android.database.sqlite.SQLiteException: no such table: ItemTable: , while compiling: SELECT _id, ItemDescription, ItemQuantity, ItemUnit, ItemPrice FROM ItemTable
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1955)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1980)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.app.ActivityThread.access$600(ActivityThread.java:122)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1146)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.os.Handler.dispatchMessage(Handler.java:99)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.os.Looper.loop(Looper.java:137)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.app.ActivityThread.main(ActivityThread.java:4340)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at java.lang.reflect.Method.invokeNative(Native Method)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at java.lang.reflect.Method.invoke(Method.java:511)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at dalvik.system.NativeStart.main(Native Method)
02-09 05:46:59.810: E/AndroidRuntime(13150): Caused by: android.database.sqlite.SQLiteException: no such table: ItemTable: , while compiling: SELECT _id, ItemDescription, ItemQuantity, ItemUnit, ItemPrice FROM ItemTable
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:127)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:94)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:53)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1564)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1449)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1405)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1485)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at com.system.inventorysystem.DBAdapter.getAllItemProduct(DBAdapter.java:108)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at com.system.inventorysystem.ViewProductItem.onCreate(ViewProductItem.java:48)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.app.Activity.performCreate(Activity.java:4465)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1049)
02-09 05:46:59.810: E/AndroidRuntime(13150):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1919)
02-09 05:46:59.810: E/AndroidRuntime(13150):    ... 11 more

This is my Database Adapter.

BDAdapter.java

package com.system.inventorysystem;

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

public class DBAdapter {

private static final String DATABASE_NAME = "ProductItemdb";
private static final int DATABASE_VERSION = 4;

private static final String TABLE_PRODUCTITEM = "ItemTable";
public static final String KEY_ItemID = "_id";
public static final String KEY_ITEM_DESCRIPTION = "ItemDescription";
public static final String KEY_ITEM_QUANTITY = "ItemQuantity";
public static final String KEY_ITEM_UNIT = "ItemUnit";
public static final String KEY_ITEM_PRICE = "ItemPrice";
public static final String KEY_ITEM_DATE = "Date/Time";
private static final String PRODUCTITEM_TABLE = "create table ItemTable (_id integer primary key autoincrement, "
        + " ItemDescription text not null, ItemQuantity integer not null, ItemUnit text not null, ItemPrice float not null, Date/Time datetime DEFAULT CURRENT_TIMESTAMP);";

private final Context ourContext;
private DatabaseHelper ourHelper;
private SQLiteDatabase ourDatabase;

public DBAdapter(Context con) {
    this.ourContext = con;
    ourHelper = new DatabaseHelper(ourContext);
}

static class DatabaseHelper extends SQLiteOpenHelper { 
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        try {
        db.execSQL(PRODUCTITEM_TABLE);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTITEM);
        onCreate(db);
    }
}

//open Database
public DBAdapter open() throws SQLException {
    ourDatabase = ourHelper.getWritableDatabase();
    return this;
}
//close database
public void close() {
    ourHelper.close();
}

//METHODS FOR ITEM PRODUCTS
//insert data
public long addItemProduct(String description, int quantity, String unit, Float price)
{
    ContentValues cv = new ContentValues();
    cv.put(KEY_ITEM_DESCRIPTION, description);
    cv.put(KEY_ITEM_QUANTITY, quantity);
    cv.put(KEY_ITEM_UNIT, unit);
    cv.put(KEY_ITEM_PRICE, price);
    return ourDatabase.insert(TABLE_PRODUCTITEM, null, cv);
}
//update data
public boolean updateItemProduct(long itemId, String description, int quantity, String unit, Float price)
{
    ContentValues cv = new ContentValues();
    cv.put(KEY_ITEM_DESCRIPTION, itemId);
    cv.put(KEY_ITEM_QUANTITY, quantity);
    cv.put(KEY_ITEM_UNIT, unit);
    cv.put(KEY_ITEM_PRICE, price);
    return ourDatabase.update(TABLE_PRODUCTITEM, cv, KEY_ItemID + "=" + itemId, null) > 0;
}
//delete data
public boolean deleteItemProduct(long itemId)
{
    return ourDatabase.delete(TABLE_PRODUCTITEM, KEY_ItemID + "=" + itemId, null) > 0;
}
//retrieve data
public Cursor getItemProduct(long itemId) throws SQLException
{   
    Cursor mCursor = ourDatabase.query(true, TABLE_PRODUCTITEM, new String[] {KEY_ItemID, KEY_ITEM_DESCRIPTION, 
            KEY_ITEM_QUANTITY, KEY_ITEM_UNIT, KEY_ITEM_PRICE}, KEY_ItemID + "=" + itemId, null,
            null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}
//retrieve all data
public Cursor getAllItemProduct()
{
    return ourDatabase.query(TABLE_PRODUCTITEM, new String[] {KEY_ItemID, KEY_ITEM_DESCRIPTION, KEY_ITEM_QUANTITY, KEY_ITEM_UNIT, KEY_ITEM_PRICE}, 
            null, null, null, null, null);
}
}

And here is my java class where I was trying to display my Data in listview from my Database.

ViewItemProduct.java

package com.system.inventorysystem;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.graphics.Typeface;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;

public class ViewProductItem extends Activity implements View.OnClickListener{

DBAdapter ourDatabase;
Typeface customFont;
TextView view_item_txt;
Button delete,update;
ListView myList;

@SuppressLint("NewApi") @Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.viewproductitem);

    customFont = Typeface.createFromAsset(getAssets(), "fonts/EraserRegular.ttf");

    view_item_txt = (TextView)findViewById(R.id.view_item_tv);
    view_item_txt.setTypeface(customFont);

    delete = (Button)findViewById(R.id.view_delete_button);
    delete.setTypeface(customFont);
    delete.setOnClickListener(this);

    update = (Button)findViewById(R.id.view_update_button);
    update.setTypeface(customFont);
    update.setOnClickListener(this);

    openDB();
    ourDatabase.open();

    final Cursor c = ourDatabase.getAllItemProduct();
    String[] ViewItemsNames = new String[] {DBAdapter.KEY_ItemID,DBAdapter.KEY_ITEM_DESCRIPTION,DBAdapter.KEY_ITEM_QUANTITY,DBAdapter.KEY_ITEM_UNIT,DBAdapter.KEY_ITEM_PRICE};
    int[] ViewItemsID = new int[] {R.id.view_item_id,R.id.view_item_desc,R.id.view_item_quantity,R.id.view_item_unit,R.id.view_item_price};
    final SimpleCursorAdapter myCursorAdapter;
    myCursorAdapter = new SimpleCursorAdapter(getBaseContext(),R.layout.viewproduct_items, c, ViewItemsNames, ViewItemsID,0);
    myList = (ListView)findViewById(R.id.view_item_listview);
    myList.setAdapter(myCursorAdapter);
}

private void openDB() {
    ourDatabase = new DBAdapter(this);
    ourDatabase.open();
}

@Override
public void onClick(View v) {
    // TODO Auto-generated method stub
    switch(v.getId())
    {
    case R.id.view_delete_button:
        Intent openDeleteItem = new Intent(this,DeleteProductItem.class);
        this.startActivity(openDeleteItem);
        break;
    case R.id.view_update_button:
        Intent openUpdateItem = new Intent(this,UpdateProductItem.class);
        this.startActivity(openUpdateItem);
        break;
    }
}
}

Here is my xml file.

Answer 1

You have a syntax problem in your create table. Date/Time is not a valid column name - get rid of the /.

The syntax problem is hidden because of the try-catch in SQLiteOpenHelper onCreate(). If there is a problem, onCreate() must throw an exception. Otherwise SQLiteOpenHelper thinks everything is ok.

After fixing the two problems above, either uninstall your app/clear its data or bump up the database version so that onCreate() gets called again.