Issue with Android SQLite: 'No such column' error

Question

I'm making the app that stores a list of the user's purchases in a SQLite database in android. A problem I'm having is I can't delete any of the entries. This is the error message I am getting: android.database.sqlite.SQLiteException: no such column: purchaseId (code 1): , while compiling: DELETE FROM purchases WHERE purchaseId='0'. It's not finding the purchaseId column. What can I do?

here's my relevant code:

In this function I store a list of the database contents in an arraylist called purchaseList. Then I iterate through all of items, passing the id to my deletePurchase method:

public void clearHistory(){
        purchaseList = dbTools.getAllPurchases();
        int size = purchaseList.size();
        for(int id = 0; id < size; id++) {
            String idString = String.valueOf(id);
            dbTools.deletePurchase(idString);

        }

    }

In this function I create the database. purchaseId is an INTEGER PRIMARY KEY, which autoincrements:

public void onCreate(SQLiteDatabase database) {
        String query = "CREATE TABLE purchases ( purchaseId INTEGER PRIMARY KEY, datePurchased TEXT, " +
                "storeName TEXT, price TEXT)";


        database.execSQL(query);
}

This function is where I think the problem is. I know purchaseId is an integer, but is this line correct: String deleteQuery = "DELETE FROM purchases WHERE purchaseId='"+ id +"'";?

public void deletePurchase(String id) {
        SQLiteDatabase database = this.getWritableDatabase();

        String deleteQuery = "DELETE FROM purchases WHERE purchaseId='"+ id +"'";   //***line of interest***
        database.execSQL(deleteQuery);

    }

I think there is an easy fix because the deleteQuery works if I use another column like storeName, which is a STRING

Any help?

try this.. "DELETE FROM purchases WHERE purchaseId='+ id +'"; — Pragnesh Ghoda シ, Dec 29 '14 at 05:40

score 2 · Accepted Answer · answered Dec 29 '14 at 05:30

2

try like this: for integer type ' are not necessary.

String deleteQuery = "DELETE FROM purchases WHERE purchaseId="+ id ;

answered Dec 29 '14 at 05:30

Rathan Kumar

2,567
2
17
24

Still getting similar error: android.database.sqlite.SQLiteException: no such column: purchaseId (code 1): , while compiling: DELETE FROM purchases WHERE purchaseId=0 – user3195991 Dec 29 '14 at 05:35
1

once uninstall and run the application, and auto increment starts from 1 – Rathan Kumar Dec 29 '14 at 05:38

score 1 · Answer 2 · answered Dec 29 '14 at 05:29

1

try this remove single quote(')

  String deleteQuery = "DELETE FROM purchases WHERE purchaseId="+ id; //Remove single quote

Your purchaseId column type is integer.

answered Dec 29 '14 at 05:29

M D

47,665
9
93
114

I am getting a similar error unfortunately: android.database.sqlite.SQLiteException: no such column: purchaseId (code 1): , while compiling: DELETE FROM purchases WHERE purchaseId=0 – user3195991 Dec 29 '14 at 05:36

Issue with Android SQLite: 'No such column' error

2 Answers2