db.delete deletes the first row instead of the row specified

Question

I want to be able to delete a specific column in my SQLite Database.

I have tried to pass the id of the column as the whereClause, but i keep getting an error saying that the whereClause needs a string value.

I have combed SO for a solution but have not found anything that answers my question. I have also tried to google search and have written my question many different ways, but to no avail.

Here is my code

Delete Button

public void toDeleteNote(View view) {

    DBHelper dbHelper = new DBHelper(this);
    int deletedRows = dbHelper.deleteData(selectedID);
    if (deletedRows > 0){
        Toast.makeText(getBaseContext(), selectedName + " deleted successfully", Toast.LENGTH_LONG).show();
    } else {
        Toast.makeText(getBaseContext(), "Something went wrong!", Toast.LENGTH_LONG).show();
    }
    Intent intent5 = new Intent(EditData.this, MainActivity.class);
    startActivity(intent5);

    }
}

DBHelper Delete method

public int deleteData(int id){
    String ID = String.valueOf(id);
    SQLiteDatabase database = this.getWritableDatabase();
    return database.delete(TABLE_NAME, _id + " = ?",new String[]{ID});

CREATE TABLE

@Override
public void onCreate(SQLiteDatabase db) {
   String createTable = "CREATE TABLE " + TABLE_NAME + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " + MAP_NO + " INTEGER, " + LOCATION + " TEXT, " + DATE + " INTEGER, " + NOTATHOMES + " TEXT)";
    db.execSQL(createTable);
}

If you need any more informantion, please feel free to ask.

Can you add your table definition? How are you verifying which row is deleted? Also, you're deleting an entire row , not a column — OneCricketeer, Aug 20 '17 at 02:46
Hi, @cricket_007, sorry, i will fix my mistake. I want to delete a row, not a column. — The Abstract Lightbulb, Aug 20 '17 at 02:55
Perhaps `selectedID` doesn't contain the value you think it does. — Andreas, Aug 20 '17 at 02:57
Hi @Andreas, how would i check that? Should i try and print value to console? — The Abstract Lightbulb, Aug 20 '17 at 02:59
@TheAbstractLightbulb, where you write table create code and post also this. — nivesh shastri, Aug 20 '17 at 03:02
@TheAbstractLightbulb Since this is android, you likely need to log it: [How do I write outputs to the Log in Android?](https://stackoverflow.com/q/2364811/5221149) — Andreas, Aug 20 '17 at 03:05
@Andreas, i logged the value. it says that it is id 2. this would be correct, because my database currently has 3 rows thus id 2 would refer to row 3. The only problem is that when i took a look at the database, it says that the id for the three rows is 5, 6 and 7. maybe i have done something wrong when i auto increment my primary key? — The Abstract Lightbulb, Aug 20 '17 at 03:52
Id 2 might be row 3 or it might be row 1, it all depends on if rows of a lesser id were deleted. (e.g. I have rows with id: 1, 2, 3, 4, 5. 5 is fifth, if I delete 1 and 3, and add 6. ID=6 is now 4th row. — AnthonyK, Aug 20 '17 at 04:50
@TheAbstractLightbulb So you have 3 rows with IDs 5, 6, and 7, and you run `delete()` with ID of 2. In that case, no rows are deleted. Note that if the ID is assigned by the database, i.e. the ID column is auto-increment, it will never reuse a number. If you delete all rows and create 3 new ones, they will be numbered 8, 9, and 10, or whatever the next 3 numbers are. The real question is: Why is `selectedID = 2` if database has rows with IDs 5, 6, and 7? Figure that out and fix it. — Andreas, Aug 20 '17 at 06:55

Bob · Answer 1 · 2017-08-20T09:11:54.330

0

You should not rely on _id column of the database to uniquely identify a row in the table, like if you have 3 rows and the table will have _id as 0, 1, 2. That is not the case.

That value _id column gets incremented irrespective of the total number of rows in your table. For example, if you have 3 rows initially, you might have _id as 0, 1, 2. Then If you delete the first two rows and add the same 2 rows again, you will have 2, 3, 4 as _id.

So to uniquely identify a row in your table, you should define your own unique id (like UUID) in a separate column, that does not change for row deletion and addition.

edited Aug 20 '17 at 09:11

answered Aug 20 '17 at 04:41

Bob

13,447
7
35
45

Do you have any suggestions on how i might achieve this? – The Abstract Lightbulb Aug 20 '17 at 08:55
While creating a note, creating a unique id for each note. You can use UUID to generate unique id: `UUID.randomUUID().toString()`. And save that id in a column of the note table. So now you have to pass that id to delete the note from the table. – Bob Aug 20 '17 at 09:14
I'll give it a crack – The Abstract Lightbulb Aug 20 '17 at 10:44

score 0 · Accepted Answer · answered Aug 22 '17 at 03:07

So i figured it out.

All i needed to do was use the cursor associated with my list view to return the id and store in intent.

Here is an Example:

  public void displayList() {
        final Cursor cursor = dbHelper.getData();
        String from[] = new String[]{DBHelper.LOCATION};
        int to[] = new int[]{R.id.ListLayout1};
        simpleCursorAdapter = new SimpleCursorAdapter(this, R.layout.row_item, cursor, from, to, 0);


        final ListView listView = (ListView) findViewById(R.id.list);
        listView.setAdapter(simpleCursorAdapter);
        while (cursor.moveToNext()) {
        listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                if(cursor.getCount()>0) {
                    cursor.moveToPosition(i);
                    Intent intent = new Intent(MainActivity.this, EditData.class);
                    intent.putExtra("id", cursor.getInt(cursor.getColumnIndex(DBHelper._id)));
                    startActivity(intent);

So there it is. Thanks for your help everybody!

db.delete deletes the first row instead of the row specified

2 Answers2