Not removing from database

Question

My code does not remove any row from the database. These rows were shown in the listview in forms. They can be remove and disappear from the listview in the form but they are not removed from the database table.

Also, Im using MS Access for the database.

Here's the code

 Public Sub DeleteProduct()

        If ListView1.SelectedIndices.Count <= 0 Then
            Return
        End If

        Dim ItemNo As Integer = ListView1.SelectedIndices(0)
        Try
            Dim I As Integer = MsgBox("Are you sure you want to delete this record? You can't Undo", MsgBoxStyle.YesNo, "Are you sure?")
            If I = MsgBoxResult.Yes Then
                conn.Open()
                Dim cmd2 As New OleDb.OleDbCommand("DELETE FROM PRODUCT WHERE product_id = '" & ListView1.Items(ItemNo).SubItems(0).Text & "'", conn)
                cmd2.ExecuteNonQuery()
                MsgBox("Record removed successfully", MsgBoxStyle.OkOnly, "Remove Succeeded")
            Else
                Return
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try
        Refresh()

    End Sub

Answer 1

Separate your user interface code from your database code.

Connections and commands need to have their Dispose methods called so they should be declared locally in a Using block. Get in the habit of using Parameters instead of concatenating strings to build sql statements.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If ListView1.SelectedIndices.Count = 0 Then
        MessageBox.Show("You must select an item to delete.")
        Exit Sub
    End If
    If MessageBox.Show("Are you sure you want to delete this record? You can't Undo", "Are you sure?", MessageBoxButtons.YesNo) = DialogResult.Yes Then
        Dim ItemNo As Integer = ListView1.SelectedIndices(0)
        Dim ProductID = CInt(ListView1.Items(ItemNo).Text)
        Dim RecordsDeleted As Integer
        Try
            RecordsDeleted = DeleteProduct(ProductID)
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try
        If RecordsDeleted = 1 Then
            MessageBox.Show("Record removed successfully", "Remove Succeeded")
            ListView1.Items.RemoveAt(ItemNo)
        Else
            MessageBox.Show("Record was not deleter")
        End If
    End If
End Sub

Private OPConStr As String = "Your connection string."

Public Function DeleteProduct(ID As Integer) As Integer
    Dim retval As Integer
    Using conn As New OleDbConnection(OPConStr),
            cmd As New OleDbCommand("DELETE FROM PRODUCT WHERE product_id = @ID", conn)
        cmd.Parameters.Add("@ID", OleDbType.Integer).Value = ID
        conn.Open()
        retval = cmd.ExecuteNonQuery
    End Using
    Return retval
End Function