Member Avatar for nadiam

Hi, im using vb2010 with MS Access 2013. I have this form that displays ID values from database into a listbox And when an ID is clicked the details that correspond with that ID are displyed into textboxes.

Screenshots :

Forms : http://imgur.com/a/NrTTD
Display details : http://imgur.com/a/twF7P

The delete button does delete the data from database but im having trouble with the form as when the data is deleted the "deleted" data is still displayed on the form. How do i refresh the forms so it does not display the deleted data after deletion?

Delete Button

Private Sub btn_delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_delete.Click
        Dim delete_confirmation = MsgBox("Are you sure you would like to delete the product '" & txt_pname.Text & "' with ID '" & txt_pid.Text & "'?", MsgBoxStyle.YesNo)

        If delete_confirmation = MsgBoxResult.Yes Then

            Dim deleteproduct As String = "DELETE FROM TBL_PRODUCTS_A154287 WHERE FLD_PRODUCT_ID = '" & txt_pid.Text & "'"
            Dim mywriter As New OleDb.OleDbCommand(deleteproduct, myconnection2)

            mywriter.Connection.Open()
            mywriter.ExecuteNonQuery()
            mywriter.Connection.Close()

            Beep()
            Dim msgbx As String = MsgBox("The product '" & txt_pname.Text & "' has been successfully deleted.")

        End If

            mywriter.Connection.Close()
        End If
    End Sub

Cat Button (Display Cat details)

Private Sub btn_cat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_cat.Click
        Dim getProduct As String = "SELECT FLD_PRODUCT_ID,FLD_TYPE FROM TBL_PRODUCTS_A154287 WHERE FLD_TYPE = 'Cat'"

        Dim theTable As New DataTable

        Dim reader As New OleDb.OleDbDataAdapter(getProduct, myconnection)

        reader.Fill(theTable)

        lst_product.DataSource = theTable
        lst_product.DisplayMember = "FLD_PRODUCT_ID"

        refresh_text(lst_product.Text)
        btn_edit.Enabled = True
        btn_update.Enabled = True
        btn_delete.Enabled = True
End Sub

refresh_text code

Private Sub refresh_text(ByVal id As String)

        Dim getID As String = "SELECT * FROM TBL_PRODUCTS_A154287 WHERE FLD_PRODUCT_ID='" & id & "'"
        Dim theTable As New DataTable
        Dim reader As New OleDb.OleDbDataAdapter(getID, myconnection)

        reader.Fill(theTable)

        txt_pid.Text = theTable.Rows(0).Item("FLD_PRODUCT_ID")
        txt_pname.Text = theTable.Rows(0).Item("FLD_PRODUCT_NAME")
        txt_brand.Text = theTable.Rows(0).Item("FLD_BRAND")
        txt_desc.Text = theTable.Rows(0).Item("FLD_DESC")
        txt_price.Text = theTable.Rows(0).Item("FLD_PRICE")
        txt_qty.Text = theTable.Rows(0).Item("FLD_QTY")

        Try
            pic_img.BackgroundImage = Image.FromFile("pictures/" & txt_pid.Text & ".jpg")
        Catch ex As Exception
            pic_img.BackgroundImage = Image.FromFile("pictures/nophoto.jpeg")
        End Try

    End Sub
Edited by nadiam because: wrong link
  • 3 Contributors
  • 2 Replies
  • 487 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by jhai_salvador

Dani AI

Generated

A short, practical summary and fixes.

The symptom happens because deleting the row only changes the database; the form still shows the last-loaded values and the listbox/DataTable isn't reloaded or cleared. The posted refresh_text also assumes a row exists (accessing Rows(0)), which is unsafe once the record is gone. 's idea to clear controls when the id is empty and 's advice about moving connection closes are on point — combine those with a safe reload flow.

Recommended flow after a successful DELETE:

  • Run a parameterized DELETE inside a Using block (avoids double-close and scope errors).
  • Call the same loader used by the Cat button to refill the listbox DataSource.
  • If the list still has items, set the SelectedIndex to the nearest valid item and call a safe refresh routine; otherwise clear all textboxes and restore the default image.
  • Make SafeRefreshText check String.IsNullOrWhiteSpace(id) and dt.Rows.Count before reading Rows(0).

Example (VB.NET sketch):

Private Sub DeleteSelectedProduct()
    Dim pid = txt_pid.Text
    If String.IsNullOrWhiteSpace(pid) Then
        ClearFields()
        Return
    End If

    Dim sql = "DELETE FROM TBL_PRODUCTS_A154287 WHERE FLD_PRODUCT_ID = ?"
    Using cmd As New OleDb.OleDbCommand(sql, myconnection2)
        cmd.Parameters.AddWithValue("?", pid)
        If myconnection2.State <> ConnectionState.Open Then myconnection2.Open()
        cmd.ExecuteNonQuery()
    End Using

    LoadProductsByType("Cat")  ' reuse the logic from btn_cat_Click
    If lst_product.Items.Count > 0 Then
        lst_product.SelectedIndex = Math.Min(Math.Max(0, lst_product.SelectedIndex), lst_product.Items.Count - 1)
        SafeRefreshText(Convert.ToString(lst_product.SelectedValue))
    Else
        ClearFields()
    End If
End Sub

Notes: set lst_product.ValueMember = "FLD_PRODUCT_ID" (and DisplayMember to a name field) so SelectedValue reliably returns the id. Implement ClearFields to empty textboxes and set the default image, and make SafeRefreshText use a parameterized SELECT and verify Rows.Count before assigning control values. This avoids stale UI, index errors, and connection-close bugs seen in the original handler.

Recommended Answers

All 2 Replies

Member Avatar for Santanu.Das

You can add a few lines in your codes in refresh_text subprocedure when id is null or whitespace, where you can nothing values to all textboxes and image control.

Member Avatar for jhai_salvador

  1. (sugestion) Move connection close inside "If delete_confirmation = MsgBoxResult.Yes Then"

  2. After delete, just clear out your textboxes

    txt_pid.Clear
    txt_pname.Clear
    ....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.