Delete from database SQLite Android

Question

I have a problem. I try to delete the record from the database. I have no idea, but how to do it. I tried something like this, but not quite work. The basic gist is that I trying to delete from the database based on list item position, not database row ID. How delete base database row ID?

ListView listawszystkich;
public int pozycja;
DatabaseDEO db = new DatabaseDEO(this);
final String[] skad = new String[]{Database.tables.Transakcje.Kolumny.kwota, Database.tables.Transakcje.Kolumny.data, Kategorie.KOLUMNY.nazwa_kategorii, Database.tables.Transakcje.Kolumny.komentarz};
final int[] dokad = new int[]{R.id.kwotaglowna,R.id.dataglowna,R.id.kategoriaglowna,R.id.komentarzglowny};
private SimpleCursorAdapter adapterspinner;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_transakcje2);

    listawszystkich = (ListView) findViewById(R.id.listawszystkich);

    listawszystkich.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
            pozycja = position+1;
        }
    });
    Cursor kursorwszystkie = db.wszystkietransakcje();
    adapterspinner = new SimpleCursorAdapter(getApplicationContext(), R.layout.activity_transakcje,kursorwszystkie,skad, dokad,0);
    adapterspinner.notifyDataSetChanged();
    listawszystkich.setAdapter(adapterspinner);
    registerForContextMenu(listawszystkich);

}



@Override
public void onCreateContextMenu(ContextMenu menu, View v, ContextMenu.ContextMenuInfo menuInfo) {
    super.onCreateContextMenu(menu, v, menuInfo);
    if (v.getId()==R.id.listawszystkich) {
        MenuInflater inflater = getMenuInflater();
        inflater.inflate(R.menu.menu_list,menu);
    }
}

@Override
public boolean onContextItemSelected(MenuItem item) {
    AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo();
    switch (item.getItemId()) {
        case R.id.delete:
            db.usuwanietransakcji(pozycja);
            Transakcje.this.recreate();
            Toast.makeText(getApplicationContext(), "Delete", Toast.LENGTH_SHORT).show();
        default:
            return super.onContextItemSelected(item);
    }

Method to delete row (in DatabaseDEO):

    public void usuwanietransakcji(int id_transakcji){
    SQLiteDatabase db = DbHelper.getWritableDatabase();
    db.execSQL("DELETE FROM " + Transakcje.NAZWA_TABELI+ " WHERE "+Transakcje.Kolumny.id_transakcji+"='"+id_transakcji+"'");
    db.close();
}

Possibly a duplicate of http://stackoverflow.com/questions/7510219/deleting-row-in-sqlite-in-android - Though what is `DatabaseDEO`? — Michael Dodd, Jan 06 '17 at 20:57
Found it. `DatabaseDEO` is a custom class as posted by yourself in a previous question. http://stackoverflow.com/questions/41085675/nullpointerexception-android - In which case, definitely a duplicate. Use `DbHelper.delete()`. — Michael Dodd, Jan 06 '17 at 20:59
Possible duplicate of [Deleting Row in SQLite in Android](http://stackoverflow.com/questions/7510219/deleting-row-in-sqlite-in-android) — Michael Dodd, Jan 06 '17 at 21:00
In DatabaseDEO I have method to delete record from database, but I would like to know how to download id_transakcji when the clicks on a particular elemnt listview. Cursor? But how? — Martin, Jan 06 '17 at 21:01
Also the method `wszystkietransakcje()` to see what columns are being retrieved — Michael Dodd, Jan 06 '17 at 21:11
Yes. But after click on element of listview I would likethat removed the entry from the database. Once "the way I work," but what if the user removes the first element of id = 1 then you will want to remove id = 2? Variable pozycja would be 1 ! — Martin, Jan 06 '17 at 21:15
Got it, writing up an answer now. The basic gist is that you're trying to delete from the database based on list item position, not database row ID. — Michael Dodd, Jan 06 '17 at 21:17
Yes. But I don't know how delete from database based on database row ID, after click on element from list view. — Martin, Jan 06 '17 at 21:23

score 1 · Accepted Answer · answered Jan 06 '17 at 21:30

Looks like you're trying to delete from the database based on list position rather than row ID:

db.usuwanietransakcji(pozycja);

I'm guessing id_transakcji is a unique value similar to a row ID? First you'll need to declare Cursor kursorwszystkie as a member variable outside of the onCreate method, but still populate it on the same line.

In onContextItemSelected() you'll need to query your cursor again:

case R.id.delete: {
        // Move to the selected row
        kursorwszystkie.moveToPosition(info.position);

        // Get the column ID of id_transakcji
        final int col_id = kursorwszystkie.getColumnIndex(Database.tables.Transakcje.Kolumny.id_transakcji);

        // Get id_transakcji
        final int id_transakcji = kursorwszystkie.getInt(col_id);

        // Query the database
        db.usuwanietransakcji(id_transakcji);
        Transakcje.this.recreate();
        Toast.makeText(getApplicationContext(), "Delete", Toast.LENGTH_SHORT).show();
        break;
}

Note the addition of {} as I'm declaring a variable inside a switch case. Also add a break; at the end of the case so the code doesn't fall through into the default block. Let me know if that works.

I get error after click delete: `java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.` — Martin, Jan 06 '17 at 21:45
That means it couldn't find the column. I was only guessing with `Database.tables.Transakcje.Kolumny.id_transakcji` as I don't know what columns you're using in `DatabaseDEO`. You'll need to replace it with the name of the column you've stored `id_transakcji` in, as used in `DatabaseDEO`. — Michael Dodd, Jan 06 '17 at 21:50
Ok. I must correct my Cursor. Thank you for help. Good luck! ;) — Martin, Jan 06 '17 at 21:56

Delete from database SQLite Android

1 Answers1