SQLite Delete function working but not deleting entry

Question

The record is selected from a ListView which can then be clicked on, promting the user with a dialog to see if they would like to confirm or cancel. The record should then be deleted and the ListView should update. However, I haven't been able to achieve this so far despite the provided tutorial matching up with my code. The application does not crash but after the dialog, it refreshes the page but does not delete the record?

DeleteRecord Actvity

public class DeleteRecordActivity extends AppCompatActivity
{
    OpenDatabase sqh;
    SQLiteDatabase sqdb;

    ListView deleteRecordListView;
    Button backButtonDelete;


    @Override
    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_delete_record);

        InitDatabase();

        setupControls();

    } //  protected void onCreate(Bundle savedInstanceState)

    public void InitDatabase()
    {
        sqh = new OpenDatabase(this);
        sqdb = sqh.getWritableDatabase();

    } //  public void InitDatabase()

    protected void setupControls()
    {
        backButtonDelete = findViewById(R.id.backButtonDelete);
        backButtonDelete.setOnClickListener(new View.OnClickListener()
        {
            @Override
            public void onClick(View v)
            {
                finish();
            }
        });

        setupListView();

    } //  protected void setupControls()

    protected void setupListView()
    {
        deleteRecordListView = findViewById(R.id.deleteRecordListview);

        ArrayList<String> list = new ArrayList<String>();
        list.addAll( sqh.allFilmsReturnArrayList( sqdb ));

        ArrayAdapter adapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, list);

        deleteRecordListView.setAdapter( adapter );

        deleteRecordListView.setOnItemClickListener(new AdapterView.OnItemClickListener()
        {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id)
            {
                String item = (String) parent.getItemAtPosition(position);

               // String[] subString = item.split(",");

               // sqh.deleteRecordFromFilmography( sqdb, subString[0] );

                deleteRecordDialog( item );

                Log.i("RECORD", "Record = " + item);

                refreshListView();

            }
        });


    } // protected void setupListView()

    protected void refreshListView()
    {
        ArrayList<String> list = new ArrayList<String>();
        list.addAll( sqh.allFilmsReturnArrayList( sqdb ));

        ArrayAdapter adapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, list);

        deleteRecordListView.setAdapter( adapter );

    } // protected void refreshListView()

    protected void deleteRecordDialog(String selectedRecord)
    {
        String[] subString = selectedRecord.split(",");

        Dialog dialog = new Dialog(this);
        dialog.setContentView(R.layout.dialog_layout);

        TextView recordTextView = dialog.findViewById(R.id.recordTextView);
        recordTextView.setText( selectedRecord );

        Button yesButton = dialog.findViewById(R.id.yesButton);
        yesButton.setOnClickListener(new View.OnClickListener()
        {
            @Override
            public void onClick(View v)
            {
                sqh.deleteRecordFromFilmography( sqdb, subString[0] );
                dialog.dismiss();
                refreshListView();;
            }
        });

        Button noButton = dialog.findViewById(R.id.noButton);
        noButton.setOnClickListener(new View.OnClickListener()
        {
            @Override
            public void onClick(View v)
            {
                dialog.dismiss();

            }
        });

        dialog.show();

    } //   protected void deleteRecordDialog(String selectedRecord)



} // public class DeleteRecordActivity extends AppCompatActivity

The call method in the OpenDatabase class:

public void deleteRecordFromFilmography(SQLiteDatabase sqdb, String id)
    {
        String deleteQuery = "DELETE FROM filmography WHERE id = '" + id + ";'";

        sqdb.execSQL( deleteQuery );

    } // public void deleteRecordfromFilmography(SQLiteDatabase sqdb, String id)

Answer 1

I don't know which tutorial you are following, but my advice is to stop and find something else.

The problem with your code is just a typo:

String deleteQuery = "DELETE FROM filmography WHERE id = '" + id + "';";

instead of:

String deleteQuery = "DELETE FROM filmography WHERE id = '" + id + ";'";

But by fixing just that you will only learn to use unsafe code and prone to typos like that.

Your code is unsafe because you are concatenating parameters to sql code.
This is always a bad idea and poses the risk of sql injection.

Use ? placeholders for the parameters.

The recommended method to delete a row is not execSQL() but delete():

public void deleteRecordFromFilmography(SQLiteDatabase sqdb, String id) {
    sqdb.delete("filmography", "id = ?", new String[] {id});
}

The 1st argument is the name of the table, the 2nd is the WHERE clause without the WHERE keyword and the 3d is an array of strings which contains the values for the parameters (? placeholders) which is only 1 in your case.