android sqlite delete query not working

Question

The following sqlite query does not delete IDs which starts with zero.

Android Sqlite Table structure

 String CREATE_TABLE_BUS = "CREATE TABLE " + TABLE_BUS + "("
                    + KEY_TID + " INTEGER PRIMARY KEY AUTOINCREMENT,"    
                    + KEY_BUS_NUM + " TEXT,"
                    + KEY_BUS_NAME + " TEXT,"
                    + KEY_FROM + " TEXT,"
                    + KEY_TO + " TEXT,"
                    + KEY_TYPE + " TEXT"
                    + ")";

            db.execSQL(CREATE_TABLE_BUS);

I kept BUS_NUM as text and not as int for some purpose.

And this is the function I call to delete a row..

        public void Delete_Bus(String bus_num) {
            SQLiteDatabase db = this.getWritableDatabase();

            db.delete(TABLE_BUS, KEY_BUS_NUM+"="+bus_num , null);
              Log.e("deleting bus num", bus_num);

            db.close(); // Closing database connection
        }

This code works very fine when the code does not start with zero..

It works for 76555 and not for 09877. Whats wrong with my code

Answer 1

The query generated by this code ends like this:

DELETE FROM BusTable WHERE BusNum = 012345

The database will interpret this as a number, not a string.

In SQL, strings must be quoted:

DELETE FROM BusTable WHERE BusNum = '012345'

However, you can avoid having to format the string by using a parameter:

db.delete(TABLE_BUS, KEY_BUS_NUM + " = ?", new String[] { bus_num });

Answer 2

Maybe..

public void Delete_Bus(String bus_num)
{
    SQLiteDatabase db=this.getWritableDatabase();
    db.execSQL("DELETE FROM "+TABLE_BUS+" WHERE "+KEY_BUS_NUM+"='"+bus_num+"'");
    db.close();
}