CODEWITHSUNDEEP
vb.netwinformslistviewms-access
Talat Farooq
Talat Farooq

Reputation: 17

Delete only selected ListView item from Access database

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

Upvotes: 0

Views: 451

Answers (1)

Alan
Alan

Reputation: 1637

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.

Upvotes: 0

Related Questions