I cant delete some rows by id in SQLite

Question

I always cant delete first two sometimes three records. They are on Listview, when you press element you will see delete button on another layout. On Log im getting correct index for every element.

So here is my code:

Main Activity:

        viewOfT.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

                Intent i=new Intent(MainActivity.this, popupWindow.class);
                i.putExtra("index",id);
                startActivity(i);
            }
        });         
}

public void populateListView() {


    Cursor data = db.getData();
    ArrayList<String> listData = new ArrayList<>();
    while(data.moveToNext()){


               k.setId(data.getInt(0));
               k.setTask(data.getString(1));

        listData.add("- " + k.getTask());


    }

    ArrayAdapter<String> arrayAdapter = new ArrayAdapter<String>(
            this,
            android.R.layout.simple_list_item_1,
            listData);

    viewOfT.setAdapter(arrayAdapter);
    arrayAdapter.notifyDataSetChanged();
    viewOfT.invalidateViews();

Delete button in other activity:

  del.setOnClickListener(new View.OnClickListener(){
    @Override
    public void onClick(View v) {
        Bundle bundle=getIntent().getExtras();
        long value=bundle.getLong("index");

        db.deleteRecord(value);

        finish(); }
});

And SQLHelper:

public void deleteRecord(long id) {
    SQLiteDatabase db = this.getWritableDatabase();

    db.delete(TABLE_NAME, KEY_ID + "=" + id, null);
    close();
}

what adapter are you using? if you used `SimpleCursorAdapter` it would work with no problems — pskink, Oct 17 '17 at 12:23
What about your logcat when deleting such entries? No errors/warnings logged? — pleft, Oct 17 '17 at 12:25
Have you examined if this happens to all entries and not only on the first 2 or 3? — pleft, Oct 17 '17 at 12:28
@pleft i guess OP just need to [update the cursor](https://stackoverflow.com/questions/1985955/android-simplecursoradapter-doesnt-update-when-database-changes) — Pavneet_Singh, Oct 17 '17 at 12:29
When you re-do a select in the db, do you still find the records? It might be that you are deleting from the db, but you are not re-creating the new adapter, or removing the element from your current adapter. — Laert, Oct 17 '17 at 12:30
I uploaded my code, look at my adapter please, log - no error/warnings. — Terran, Oct 17 '17 at 12:38
**do not** use `ArrayAdapter`, use `SimpleCursorAdapter` instead — pskink, Oct 17 '17 at 12:40
When you delete the record and return to the MainActivity do you also remove the relevant record from `listData` or call again the `populateListView()` method? — pleft, Oct 17 '17 at 12:41

score 1 · Answer 1 · answered Oct 17 '17 at 20:59

1) Delete populateListView method

2) Add the following as class variables:-

SimpleCursorAdpater sca;
Cursor data;

3) in the onCreate method add :-

data = getData();
sca = new SimpleCursorAdapter(
    this,        // Context same as for array adapter
    android.R.layout.simple_list_item_1, // layout to be used
    data, // <<<<<<<< the cursor to be used for the list
    new String[]{"columnname_to_be_displayed"}, // <<<<<<<< cursor column to display name
    new int[android.R.id.text1], // the view into which the cursor data will be placed
    0 // a flag 0 is fine
);

viewOfT.setAdapter(arrayAdapter);

4) Add a new method to override the 'onResume' method (or alter it if already overridden) :-

@Override
protected void onResume() {
    super.onResume();
    data = getData();
    sca.swapCursor(); // can you sca.notifyDatasetChanged()
}

As you are calling another activity to delete the task, onResume will be called when returning from the other activity so the data is again retrieved from the database (deleted row will not exist) and the adpater is told to refresh the data.

You should ideally also override the onDestroy() method to close the cursor (data.close();)

Important Consideration

A cursor column named _id must exist for CursorAdapters (that's how the SimpleCursorAdapter knows what pass to the onItemClickListener).

if KEY_ID does not equate to _id; you either need to change KEY_ID to _id or amend the getData() method to include the _id column (which should be the value of the row's identifier) e.g. assuming a very basic query:

public Cursor getData() {
    return db.query(TABLE_NAME,"rowid AS _id, *",null,null,null,null,null);
} // Note! will add an extra column so beware if using column offsets

or perhaps :-

public Cursor getData() {
    return db.query(TABLE_NAME,KEY_ID + " AS _id, *",null,null,null,null,null);
} // Note! will add an extra column so beware if using column offsets

A Note on Column offsets

In your code you have :-

           k.setId(data.getInt(0));
           k.setTask(data.getString(1));

0 and 1 are column offsets (and could change e.g. the two alternative getData() methods). As such it's generally better to take advantage of the Cursor method getColumnIndex(columnname) e.g. the above could be :-

           k.setId(data.getInt(data.getColumnIndex(KEY_ID));
           k.setTask(data.getString(data.getColumnIndex("TASK_COLUMN")));

Note! not that you will need to create an Array as the SimpleCursorAdpater takes the cursor as the source. (KEY_ID would likely have to be prefixed with the DatabaseHelper Class).

I cant delete some rows by id in SQLite

1 Answers1

Important Consideration