How to Delete or Update using SQLiteDatabase in Android

Question

I have created a Database in MainActivity, and want to delete or update that Database in EditActivity with method below.

    private void deleteNote() {
    SQLiteDatabase db;
    db = openOrCreateDatabase("note.db", SQLiteDatabase.CREATE_IF_NECESSARY, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS note("
            + "idx INTEGER PRIMARY KEY AUTOINCREMENT,"
            + "name TEXT,"
            + "date TEXT,"
            + "taste TEXT,"
            + "rating FLOAT" + ");");
    db.execSQL("DELETE FROM note WHERE idx = " + positionInt + ";");
    Toast.makeText(EditActivity.this, "Delete Success", Toast.LENGTH_SHORT).show();
    db.close();
}

Above is my deleting code, but it seems doesn't work right. I see the toast message("Delete Success"), however DB still has the deleted data.

My question is: 1) Are there any wrong part in my code? 2) Are there any way to check whether the data deleted or not in Android monitor, not using Virtual device?

Many thanks! Hope some guru help me out!

Of course, you see the toast, since it executes unconditionally. There's probably no record with idx = " + positionInt. Please understand that the position in a ListView (or any other list/grid-formic View) and a table row id are not the same thing. And they most likely will differ. By the way, the ; at the end of a SQL query or command is completely useless (and string concatenation is a time consuming operation). — Phantômaxx
– Phantômaxx, Commented Jan 4, 2017 at 18:59
I had brought position number from ListView in MainActivity and defined it in EditActivity which I am working at. Int positionInt = getIntent().getIntExtra("position", positionInt); Thanks for letting me know semi colon is useless here! — Dahn Hwang
– Dahn Hwang, Commented Jan 4, 2017 at 19:05
I had brought position number from ListView in MainActivity and defined it in EditActivity which I am working at. Exactly what I understood. And, as I told you, the position in the ListView has nothing to do with the table row id. — Phantômaxx
– Phantômaxx, Commented Jan 4, 2017 at 20:15
@Rotwang Ok, now I understood. But I used the position number in ListView when I moved into the other activity. And the position number and the table row id is same. Then how can I make them connected? — Dahn Hwang
– Dahn Hwang, Commented Jan 5, 2017 at 16:36
The row id is most likely nerver the same as the ListView index. The IDs in a table start with 1. A ListView item positions start with 0. So you are at least off by 1. Not even to mention when you delete an item in the table. All the following row ids are not renumbered and new row ids will continue the count (this is so not to loose the eventual relations in other tables). You should retrieve the id in your Adapter. — Phantômaxx
– Phantômaxx, Commented Jan 5, 2017 at 19:02

Serg Chernata · Accepted Answer · 2017-01-04 19:10:28Z

1

Use db.delete() instead, which also returns the number of affected rows that you could use for verification and show toast only when it works.

db.delete("tablename","id=?",new String[]{"1"});

edited Jan 4, 2017 at 19:10

answered Jan 4, 2017 at 18:52

Serg Chernata

12.4k6 gold badges35 silver badges50 bronze badges

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

1 Comment

Dahn Hwang Over a year ago

I changed to "db.delete("note.db","'positionInt'",null);" and app is dead. Can you please tell me what to put in second and third position?(instead of "positionint" and null I am not sure of)

Collectives™ on Stack Overflow

How to Delete or Update using SQLiteDatabase in Android

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related