java - Android: Count and delete rows from sqlite

Question

I want to delete some records from my database tables, for example table A and table B. Both table A and table B has a column name sycn_status. I want to delete only those records where the value of sync_status is C. For this purpose I wrote the following method but I'm getting the exception.

public void RemoveSyncData(){

    DBHelper = new DatabaseHelper(context); 
    //db = DBHelper.getWritableDatabase();

    db.execSQL("DELETE FROM "+ DATABASE_TABLE_DAILY_ATTENDANCE +" WHERE KEY_ATTENDANCE_SYN_STATUS ='C'");
    db.close(); 

Here I'm only checking for one table. and the second thing I want to count the deleting records so that users should know how much data is deleted.

Logcat errors

06-29 05:02:55.845: E/AndroidRuntime(1594): FATAL EXCEPTION: main
06-29 05:02:55.845: E/AndroidRuntime(1594): android.database.sqlite.SQLiteException: no such column:   KEY_ATTENDANCE_SYN_STATUS (code 1): , while compiling: DELETE FROM dailyattendance WHERE KEY_ATTENDANCE_SYN_STATUS ='C'
06-29 05:02:55.845: E/AndroidRuntime(1594):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
06-29 05:02:55.845: E/AndroidRuntime(1594):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
06-29 05:02:55.845: E/AndroidRuntime(1594):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)

Database method for deleting records. i'm calling the delete method from the button click

    @SuppressLint("NewApi") public void CleanAppDataClick(View V) {

    progressDialog = new ProgressDialog(Others.this);
    progressDialog.setTitle("Cleaning");
    progressDialog.setMessage("Please wait...");//
    Log.e("button delete data", "clicked");           
    usersqlite_obj.open();
    usersqlite_obj.RemoveSyncData();     
    dismissLoadingDialog();
}

Here is the method in database class.

public boolean RemoveSyncData(){

    DBHelper = new DatabaseHelper(context); 
    db.execSQL("DELETE FROM "+ DATABASE_TABLE_DAILY_ATTENDANCE +" WHERE "+KEY_ATTENDANCE_SYN_STATUS +"='C'");
    db.execSQL("DELETE FROM "+ DATABASE_TABLE_DCR +" WHERE "+KEY_DCR_SYN_STATUS +"='C'");
    db.execSQL("DELETE FROM "+ DATABASE_TABLE_DCRNV +" WHERE "+KEY_DCRNV_SYN_STATUS +"='C'");


    db.close();
    return true;

}

Answer 1

try this

 db.execSQL("DELETE FROM "+ DATABASE_TABLE_DAILY_ATTENDANCE +" WHERE "+KEY_ATTENDANCE_SYN_STATUS ='C');

UPDATE:

As requested in one of the comments, the problem was with statement which is executed.

"DELETE FROM "+ DATABASE_TABLE_DAILY_ATTENDANCE +" WHERE KEY_ATTENDANCE_SYN_STATUS ='C'"

In the above statement SQLite will not be able to recognize the string as valid.

DATABASE_TABLE_DAILY_ATTENDANCE is a string value which does not require double quotes around it.

KEY_ATTENDANCE_SYN_STATUS was included within double quotes, but its already a string value, so moving it out of double quotes.

'C'" String was ending here, since the quote ended before keyword KEY_ATTENDANCE_SYN_STATUS removing the last occurance of double quotes.