SQLite table row not deleting when delete button pressed, android

Question

I'm trying to delete a row from a table in SQLite for the first time, I thought I'd followed a tutorial and adapted it to my app but I've messed up the syntax somewhere.

Here is the code from DatabaseHelper.java

public void deleteRow (String subject) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("delete from " + TABLE_NAME +" where " + COL_2 + "=" + subject);
}

And the code that calls it from SubjectActivity.java:

public void onClick(View view) {
    if(view.getId()==R.id.deleteSubject) {
        Bundle extras = getIntent().getExtras();
        String subject = extras.getString("subject");
        myDb.deleteRow(subject);
    }
}

The row should be deleted when a delete button is pressed in the subject activity, but when I do press the button, the app force closes. What am I missing in the syntax?

I don't know, my emulators have stopped working in the last couple of days and I haven't been able to fix them yet. I've been running things on my phone, and all I know is that the app force closes when I click the delete button. — Emma
– Emma, Commented Jan 6, 2018 at 7:33
May be no record available that satisfy the condition you are passing with delete query — Suraj Vaishnav
– Suraj Vaishnav, Commented Jan 6, 2018 at 7:35

Tim Biegeleisen · Accepted Answer · 2018-01-06 07:54:35Z

5

This is what your current raw query being passed to SQLite looks like:

delete from TABLE_NAME where COL_2 = some_subject

In other words, you are comparing COL_2 against some_subject, which SQLite will interpret as a column, but not as a string literal. Here is the query you intended to run:

delete from TABLE_NAME where COL_2 = 'some_subject'

The best fix here is to use prepared statements, where the query has a positional parameter (?) for the subject string. The statement will automatically take care of escaping the string on your behalf.

public void deleteRow (String subject) {
    SQLiteDatabase db = this.getWritableDatabase();
    String sql = "DELETE FROM " + TABLE_NAME + " WHERE " + COL_2 + " = ?";
    SQLiteStatement statement = db.compileStatement(sql);
    statement.bindString(1, subject);

    int numRowsDeleted = statement.executeUpdateDelete();
    // you might want to check the number of records which were actually deleted
}

Note that it is not very typical to make the table and column names variable for a given statement. Instead, you would usually see these being hard coded. The reason for this is that it is unlikely that you would want to run the same statement on a different table, or the same table but with different columns.

edited Jan 6, 2018 at 7:54

answered Jan 6, 2018 at 7:38

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Emma Over a year ago

Ok, so the SQLite isn't seeing my subject variable as the string in the row I want deleted but rather as the name of the collumn itself? Thanks for the response, would that code go inside my deleterow() function? I'm just having a little trouble seeing what the bindString and executeUpdateDelete functions do, sorry.

Tim Biegeleisen Over a year ago

@Emma This is just a minor refactor to your deleteRow() method, q.v. my updated answer.

CL. Over a year ago

You do not need a prepared statement; db.delete() supports parameters, too.

Emma Over a year ago

Awesome, thank you so much for all your help @Tim Biegeleisen, and explaining why my code didn't work instead of just correcting it. I really appreciate it.

Omkar · Accepted Answer · 2018-01-06 07:47:36Z

-1

check below code

public void deleteRow (String subject) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NAME , COL_2 + "=" + subject, null);
}

answered Jan 6, 2018 at 7:47

Omkar

3,1001 gold badge26 silver badges44 bronze badges

Collectives™ on Stack Overflow

SQLite table row not deleting when delete button pressed, android

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related