Deleting a row in SQLite in Android

Question

Am trying to delete a row from players table (which just has players name and id in each row). I feel it may be something obvious I'm missing but just can't see where I'm going wrong here.

MyCode

DatabaseHelper.java

public Integer deletePlayer(String id) {
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete(PLAYERS_TABLE, "id =?", new String[]{id});
}

ManagePlayers.java

I'm using a LongClickListener so a user of the app can delete any a player from a listview

playersListView.setOnItemLongClickListener(
            new AdapterView.OnItemLongClickListener() {
                @Override
                public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
                    removeItemFromList(position);
                    return true;
                }
            }
    );


 public void removeItemFromList(int position) {
    final int deletePosition = position;

    AlertDialog.Builder alert = new AlertDialog.Builder(
            ManagePlayers.this);

    alert.setTitle(getString(R.string.delete_player));
    alert.setMessage(getString(R.string.are_sure_delete_player));
    alert.setPositiveButton(R.string.positiveAnswer, new DialogInterface.OnClickListener() {
        @Override
        public void onClick(DialogInterface dialog, int which) {
           //these three lines delete the listview row
            playerNameArrayList.remove(deletePosition);
            myArrayAdapter.notifyDataSetChanged();
            myArrayAdapter.notifyDataSetInvalidated();

            if (dbh.deletePlayer(String.valueOf(deletePosition)) > 0) {
                Toast.makeText(ManagePlayers.this, "Player Deleted", Toast.LENGTH_LONG).show();
                finish();
            } else
                Toast.makeText(ManagePlayers.this,  "Player NOT Deleted", Toast.LENGTH_LONG).show();
        }
    });

    alert.setNegativeButton(R.string.negativeAnswer, new DialogInterface.OnClickListener() {
        @Override
        public void onClick(DialogInterface dialog, int which) {
            dialog.dismiss();
        }
    });

    alert.show();
}

Can you please advise how I can delete rows from my players table successfully?

Thks,

Sean

Possible duplicate of [Deleting Row in SQLite in Android](https://stackoverflow.com/questions/7510219/deleting-row-in-sqlite-in-android) — MarsAtomic, Mar 16 '18 at 01:20

score 1 · Answer 1 · answered Mar 16 '18 at 02:39

An issue that you have is that you are equating the position in the list to the id of the row. Rarely, if ever, will the two be the same.

When a new row is added it's id (if it is an alias of rowid i.e. the column has been declared with a column type as either INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT) will likely be 1 then 2 then 3 etc. However the first position in the list will be 0, then 1 then 2 .....

So assume you have something along the lines of :-

and then present the players as a list then:-

player Fred (id = 1) will be in position 0.
player Bert (id = 2) will be in position 1.
and so on.

Some think they can compensate by adding 1 to position. However, say Fred is deleted then not :-

player Bert (id = 2) is in position 0.
player Harry (id = 3) is in position 1.

So now the difference between position and id is 2. However, say Tom is deleted then

player Bert (id = 2) is in position 0.
player Harry (id = 3) is in position 1.
player Dick (id = 5) is in position 2.

What you need to so is ascertain the correct id, which probably means that the source of the list (playerNameArrayList) either contains the id or possibly that a complimentary array (i.e. element 0 will have the if of the first player and so on) be included/accessible and that position is used to then obtain the id from the respective array element.

An alternative could be to utilise a CursorAdapter (for ListViews) in which case the id (note id column in the Cursor must be _id ) is provided as the 4th parameter to onItemLongClick (if not a CursorAdapter then it is the position).

Mike thanks a million for clarifying this - as you say ids do start from 1. Appreciate it. — user3111329, Mar 16 '18 at 22:29

Deleting a row in SQLite in Android

1 Answers1