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();
}

Answer 1

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.