How To Delete Rows in a SQLite Database that may hold the same data depending on which row is selected in a listbox

Question

As the title suggests, I want to delete rows of a database depending on which row in a listbox the user will select.The Listbox. This is my current code, but when run, there is an SQL Logic Error.

        string SelectedItemToDelete = LBoxCurrentBasket.SelectedItem.ToString();

        sqlite_conn = new SQLiteConnection("Data Source=RetailSystem.db; Version = 3; New = True; Compress = True;");
        sqlite_conn.Open();

        try
        {
            sqlite_cmd = sqlite_conn.CreateCommand();
            sqlite_cmd.CommandText = "DELETE * FROM TblBasket WHERE BasketItemName = @ItemToDelete";
            sqlite_cmd.Parameters.AddWithValue("@ItemToDelete", SelectedItemToDelete);
            SQLiteDataReader sqlite_datareader;
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            sqlite_conn.Close();
            MessageBox.Show("Record Deleted!");
            LBoxCurrentBasket.Refresh();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

Also is there a way to merge together the T-Shirt Records so there is only one record with a higher quantity?The Database if it helps

Answer 1

I think that you should execute sqlite_cmd.ExecuteNonQuery(); method instead of executing sqlite_cmd.ExecuteReader(); on SqliteCommand object.

As per documentation SqliteCommand.ExecuteNonQuery method return the number of rows inserted, updated, or deleted.

So, in my opinion I will do it like this:

 string SelectedItemToDelete = LBoxCurrentBasket.SelectedItem.ToString();

        sqlite_conn = new SQLiteConnection("Data Source=RetailSystem.db; Version = 3; New = True; Compress = True;");
        sqlite_conn.Open();

        try
        {
            sqlite_cmd = sqlite_conn.CreateCommand();
            sqlite_cmd.CommandText = "DELETE FROM TblBasket WHERE BasketItemName = @ItemToDelete";
            sqlite_cmd.Parameters.AddWithValue("@ItemToDelete", SelectedItemToDelete);
            sqlite_cmd.ExecuteNonQuery();
            MessageBox.Show("Record Deleted!");
            LBoxCurrentBasket.Refresh();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
          sqlite_conn.Close();
        }

Also, don't forget to pass parameters like connection string, and query string to your command object in order to perform the operation on database, as I can see, you did not do that in current state of code .