Delete only selected ListView item from Access database

Question

I have inventory received table in access database where productID is repeated many time. I can populate this data in listview. Currently, when i select any row in listview and press delete button, all entries of the selected product id is deleted from the listview as well as from datbase from the beginning up to end. What i want is if i select any item in listview and press delete button, only selected row should be deleted form listview and from database not all even if it is same productid. Please help me if someone can?? Please see the code below.

Private Sub Button9_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button9.Click

    Dim item1 As ListViewItem
    item1 = ListView1.SelectedItems(0)
    Dim Del As DialogResult
    Del = MessageBox.Show("Are you sure you want to delete the record", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
    If Del = DialogResult.Yes Then
        'Dim item As ListViewItem
        con.Open()
        Dim sql As String = "DELETE FROM Receipt WHERE ProductID='" & item1.SubItems(0).Text & "'"

        With cmd
            .CommandText = sql
            .Connection = con
            .ExecuteNonQuery()
        End With
        MsgBox("Record Removed", MsgBoxStyle.Information)
    End If
    con.Close()
End Sub

If you want help, you have to show us the code that is giving you problems. — LarsTech, Apr 20 '20 at 14:39
I have added the code. Kindly advise required changes to get desired result. — Talat Farooq, Apr 21 '20 at 11:37
Use parameters to avoid sql injection and formatting errors. ExecuteNonQuery returns a number when it runs. See what that number is. This is a debugging problem — good day to learn the debugger. — LarsTech, Apr 21 '20 at 13:08
See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/719186) — LarsTech, Apr 21 '20 at 13:38

Alan · Answer 1 · 2020-04-22T18:21:07.237

Without seeing all of the relevant information about your context and design it is difficult to be certain in helping you fix the problem, but 2 things jump out from your post.

You have stated your database has ProductID repeated many times. That may be exactly what you want, but on the surface, one could take the position a ProductID is normally a unique number. You didn't state that ProductID is the Key field, but normally an ID is a unique key field. But this may be leading to why multiple records are deleted at the same time, because...

"DELETE FROM Receipt WHERE ProductID='" specifically asks to delete records with whatever the ProductID specification is. Therefore, all records with that ProductID could be expected to be deleted. If you only want one record to be deleted, you must have a unique identifier, yes? You need to include in your ListView, even if hidden, a unique record identifier if you only want to delete a unique record.

Please advise if this helps, or return a question to focus in on your problem.

Added after comment below: The SQL query/command needs to be limited by Purchase Order # as well. The SQL above only selects based on WHERE ProductID=something. You need to also select based on Purchase Order number in order to delete more uniquely.

Something like: WHERE ProductID=something AND PO=something.

Alan, Thanks for your reply. ProdouctID is unique in productlist table but in receive table it is not because one item can be purchased/receive many times. To explain further, a purchase order contains many products the similar products can repeat in other PO what i want is to delete or update a product received entry from a particular PO. What is happening now that all entries of the product deleting from from database irrespective of LPO number. — Talat Farooq, Apr 22 '20 at 05:25
OK. Changes in my proposed answer above based on your comment. By the way, when posting, be careful to not use too many abbreviations. Not everyone will always understand what they mean. I am assuming LPO means something like `Line item in the PO`? Also try to be careful with punctuation to separate your sentences/ideas. — Alan, Apr 22 '20 at 18:18
Thanks alan for giving idea to create hidden column in listview and database. I have created one field called transactionID which has done the job. Although, i have to add some more coading separately to generate TransactionID automatically. But anyway thanks for your help. By the way, your suggestions are well noted. — Talat Farooq, Apr 22 '20 at 18:50

Delete only selected ListView item from Access database

1 Answers1