android.database.sqlite.SQLiteException: no such table: OrderDetails (code 1)

Question

Error Code : 1 (SQLITE_ERROR) Caused By : SQL(query) error or missing database.

Here is my code for sqlite in android studio

public class Database extends SQLiteAssetHelper{
private static final String DB_NAME="Jerson.db";
private static final int DB_VER=1;
public Database(Context context)
{
    super(context, DB_NAME,null,DB_VER);
}
public List<Orders> getCarts()
{
    SQLiteDatabase db = getReadableDatabase();
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

    String[] sqlSelect={"MenuId","Name","Quantity","Price"};
    String sqlTable="OrderDetails";

    qb.setTables(sqlTable);
    Cursor c = qb.query(db,sqlSelect,null,null,null,null,null);

    final List<Orders> result = new ArrayList<>();
    if (c.moveToFirst())
    {
        do {
            result.add(new Orders(c.getString(c.getColumnIndex("MenuId")),
                    c.getString(c.getColumnIndex("Name")),
                    c.getString(c.getColumnIndex("Quantity")),
                    c.getInt(c.getColumnIndex("Price"))
            ));
        }while (c.moveToNext());
    }
    return  result;
}
public void addToCart(Order order)
{
    SQLiteDatabase db = getReadableDatabase();
    String query = String.format("INSERT INTO OrderDetails(MenuId,Name,Quantity,Price)VALUES('%s','%s','%s','%s');",
            order.getMenuId(),
            order.getName(),
            order.getQuantity(),
            order.getPrice());
    db.execSQL(query);
}
public void cleanCart()
{
    SQLiteDatabase db = getReadableDatabase();
    String query = String.format("DELETE FROM OrderDetails");
    db.execSQL(query);
}
}

I am sure I have a table in sqlite I dont know why its returning me no such table

here is a snippet of code on where I use the database

 btnCart.setOnClickListener(new View.OnClickListener()
    {
        @Override
        public void onClick(View v) {
            new Database(getBaseContext()).addToCart(new Order(
                    foodId,
                    currentFood.getName(),
                    numberButton.getNumber(),
                    currentFood.getPrice()
            ));
            Toast.makeText(FoodDetail.this, "Added To Cart", Toast.LENGTH_SHORT).show();
        }

    });

I don't know this API well enough to give a formal answer, but wouldn't running `DELETE FROM OrderDetails` remove _every_ user's shopping cart? I doubt this is what you really want to be doing here. — Tim Biegeleisen, Apr 15 '18 at 06:21
Can you check delete the App's data, rerun and show the log it should say `W/SQLiteAssetHelper: copying database from assets... database copy complete` if it's copying successfully from the assets folder. If doesn't there'll be a message indicating otherwise e.g. `com.readystatesoftware.sqliteasset.SQLiteAssetHelper$SQLiteAssetException: Missing databases/Jerson.db file (or .zip, .gz archive) in assets, or target folder not writable` — MikeT, Apr 15 '18 at 07:06
If the latter then make sure that the file Jerson.db is in the 1assets/databases/folder1 (create 1databases1 folder if necessary). Also check that Jerson.db is a valid SQLite database file with the SQLite DB tool you used to create it. — MikeT, Apr 15 '18 at 07:09
@TimBiegeleisen I see no information about multiple users here — OneCricketeer, Apr 15 '18 at 08:01
@cricket_007 What sort of ecommerce site only has one user? Blanket delete statements with no where clause are always suspicious if found in app code. — Tim Biegeleisen, Apr 15 '18 at 08:05
@TimBiegeleisen I understand your point, but it could be a single user shopping cart type app. If there is no concept of "logging in", there would be no way of having multiple users. — OneCricketeer, Apr 15 '18 at 08:16
@AryCruz could you please click the tick to show that the question's been answered. — MikeT, Apr 15 '18 at 08:17
@cricket_007 Face palm time. Yeah, since this database might be on an Android phone, you might be right. — Tim Biegeleisen, Apr 15 '18 at 08:17

score 1 · Accepted Answer · answered Apr 15 '18 at 08:33

The most common cause for table not found is that it hasn't been created when the database is created. This may be due to an SQL error in the tale create statement or issues encountered when copying a packaged database from the assets folder.

The latter can be a badly written copy process, hence why SQLiteAssetHelper is frequently recommended as it's tried and tested. However, even with SQliteAssetHelper a bad file can result in such an error, such a database saved before the table is created as some SQLite Tools can allow.

When using SQLiteAssethelper, as per this question. The fix should be to :-

Delete the App's Data or Uninstall the App (both will delete the existing database allow it be copied (SQLiteAssethelper checks to see if the Database exists, if so then it doesn't attempt to copy the asset)).
Check that Database is sound using an SQlite tool and save it.
Copy the saved file (best done outside of Android Studio) to the App's assets/database folder (creating the assets folder and databases folder if need be).
Rerun the App.

I was trying to install the app on to mobile connected to my desktop with out uninstalling the app. As there is change in table name and old db is saved locally in the mobile, it was giving the same error. After uninstall and fresh install, this problem is resolved. — rags, Jun 05 '20 at 20:29

android.database.sqlite.SQLiteException: no such table: OrderDetails (code 1)

1 Answers1