Member Avatar for Miss Confused

Hi!
I'm trying to delete an entry from my database, i have the code written to display the details in a listbox but now i need to write the code do the user can select the item they want to delete and then click the delete button to delete it!????? I just want to flag it as deleted not get rid of it completely from the database???
Pls can someone help!!!

Edited by Miss Confused because: n/a
  • 3 Contributors
  • 5 Replies
  • 123 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Oxiegen

Dani AI

Generated

Two quick reasons why a newly added customer can disappear from the delete list: the code that builds the list is fragile, and the SELECT/UPDATE logic around identifying a record is broken. Good call from to use a boolean "Deleted"/"DeletedFlag" — keep that — but the posted code then (a) concatenates a display string and never resets it, (b) parses fields with fixed Substring offsets instead of using a record ID, (c) builds malformed WHERE clauses by concatenating text, and (d) uses different database filenames in different places. Those bugs explain the missing row more often than Access itself.

Fix plan (practical, minimal changes):

  • Store and use the primary key (CustomerID) as the link between the ListBox and the record. Do not parse strings with Substring.
  • Bind the ListBox to a DataTable (DisplayMember for the label, ValueMember = CustomerID) so SelectedValue gives the ID.
  • Use parameterized UPDATE/SELECT queries and Using blocks to open/dispose the connection safely.
  • Ensure inserts explicitly set DeletedFlag = False and that every form points to the same .accdb file (use an absolute path or Application.StartupPath).

Example pattern (VB.NET):

' Load list (binds DisplayMember and ValueMember)
Private Sub LoadCustomers()
    Dim connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & IO.Path.Combine(Application.StartupPath, "CarRentalsSystem.accdb")
    Dim dt As New DataTable()
    Using conn As New OleDb.OleDbConnection(connStr)
        Using da As New OleDb.OleDbDataAdapter("SELECT CustomerID,Surname,Forename FROM CustomerTable WHERE Nz(DeletedFlag,False)=False", conn)
            da.Fill(dt)
        End Using
    End Using
    dt.Columns.Add("DisplayName", GetType(String))
    For Each r As DataRow In dt.Rows
        r("DisplayName") = $"{r("CustomerID")} - {r("Surname")}, {r("Forename")}"
    Next
    DisplayCustomersListBox.DataSource = dt
    DisplayCustomersListBox.DisplayMember = "DisplayName"
    DisplayCustomersListBox.ValueMember = "CustomerID"
End Sub

' Mark deleted by CustomerID (parameterized)
Private Sub MarkDeleted(id As Integer)
    Dim connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & IO.Path.Combine(Application.StartupPath, "CarRentalsSystem.accdb")
    Using conn As New OleDb.OleDbConnection(connStr)
        Using cmd As New OleDb.OleDbCommand("UPDATE CustomerTable SET DeletedFlag = True WHERE CustomerID = ?", conn)
            cmd.Parameters.AddWithValue("?", id)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
    LoadCustomers()
End Sub

Troubleshooting checklist: confirm the same .accdb file is used everywhere, explicitly set DeletedFlag on INSERT, avoid swallowing exceptions (use Catch ex and inspect ex.Message), and use strings for phone numbers (phones are not integers). For data-binding details and safe disposal patterns see Microsoft docs: and Using statement (VB).

Recommended Answers

All 5 Replies

Member Avatar for web_test

Not sure how to do in python..but I think you can repopulate your list box items with out displaying the user selected item.

Member Avatar for Miss Confused

Not sure how to do in python..but I think you can repopulate your list box items with out displaying the user selected item.

Hi
Im not using python im using Visual Basic 2008 and Microsoft Access???!!!!!

Member Avatar for Oxiegen

Add a field in the MS Access database table called "Deleted" or something like it with a datatype of Boolean.
And then add a WHERE-clause in the database query to populate the listbox. SELECT * FROM <table> WHERE Deleted = 0

Edited by Oxiegen because: n/a
Member Avatar for Miss Confused

Add a field in the MS Access database table called "Deleted" or something like it with a datatype of Boolean.
And then add a WHERE-clause in the database query to populate the listbox. SELECT * FROM <table> WHERE Deleted = 0

Hi Thanks for that i got it working i can delete the customer from the database or flag it as deleted!! but now when i add a new customer and go to the delete it the new customer is not displayed in the listbox!!? im not sure whether this is a problem with microsoft access or my code?? could you help me by having a look at my code???
Public Class DeleteCustomer

Private Sub DeleteCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DisplayItems(5) ' Loads Method to display the list
    End Sub
    Private Sub DisplayItems(ByVal num As Integer)


        'Loads recordset & outputs to list box

        Dim ConnectionString As String
        Dim SQLString As String
        Dim TitleString As String = "   "
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dr As System.Data.OleDb.OleDbDataReader
        Dim cmd As System.Data.OleDb.OleDbCommand

        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "

        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

        'Access the fields
        SQLString = "SELECT CustomerID,Surname,Forename FROM CustomerTable "
        Try 'was database found etc.

            cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
            conn.Open()

            If (ConnectionState.Open.ToString = "Open") Then

                dr = cmd.ExecuteReader()

                If dr.HasRows Then
                    DisplayCustomersListBox.Items.Clear()

                    While dr.Read
                        If Not IsDBNull(dr.Item("CustomerId")) Then
                            TitleString += dr.Item("CustomerID") & "   "
                            TitleString += dr.Item("Surname") & "   "
                            TitleString += dr.Item("Forename") & "   "

                            DisplayCustomersListBox.Items.Add(TitleString)

                        End If
                    End While
                End If
            End If

        Catch
            MessageBox.Show("Error accessing database")
        End Try
        conn.Close()
        DisplayCustomersListBox.Items.Add(" ")
        DisplayCustomersListBox.Items.Add("Count:" & DisplayCustomersListBox.Items.Count - 1)

    End Sub

    Private Sub DisplayCustomersListbox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DisplayCustomersListBox.SelectedIndexChanged
        Dim ConnectionString As String
        Dim SQLString As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dr As System.Data.OleDb.OleDbDataReader
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "CarRentalsSystem.accdb "
        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
        StreetTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
        TownTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
        CountyTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
        TelephoneTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)

        SQLString = "SELECT * FROM CustomerTable "
        SQLString += "Where '" & StreetTextBox.Text & "','" & TownTextBox.Text & "','" & CountyTextBox.Text & "','" & TelephoneTextBox.Text & ")"

        Try
            conn.Open()
            If ConnectionState.Open.ToString = "Open" Then
                cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
                dr = cmd.ExecuteReader()
                If dr.HasRows Then
                    dr.Read()
                    If Not IsDBNull(dr.Item("Street")) Then
                        StreetTextBox.Text = dr.Item("Street").ToString
                    End If
                    If Not IsDBNull(dr.Item("Town")) Then
                        TownTextBox.Text = dr.Item("Town").ToString
                    End If
                    If Not IsDBNull(dr.Item("County")) Then
                        CountyTextBox.Text = dr.Item("County").ToString
                    End If
                    If Not IsDBNull(dr.Item("Telephone")) Then
                        TelephoneTextBox.Text = dr.Item("Telephone").ToString
                    End If

                    If Not IsDBNull(dr.Item("DeletedFlag")) Then
                        DeleteCheckBox.Checked = dr.Item("DeletedFlag").ToString
                    End If
                End If
            End If
        Catch ex As Exception
        End Try
    End Sub

    Private Sub DeleteCustomerButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteCustomerButton.Click
        Dim ConnectionString As String
        Dim SQLString As String
        Dim whichButtonDialogResult As DialogResult
        Dim Street As String = ""
        Dim Town As String = ""
        Dim County As String = ""
        Dim Telephone As Integer = (0)
        Dim numRowsAddedInteger As Integer
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim conn As System.Data.OleDb.OleDbConnection
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "CarRentalsSystem.accdb "
        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
        SQLString = "UPDATE CustomerTable Set "
        SQLString += "DeletedFlag= True"        'UPDATES DELETED FLAG TO TRUE
        SQLString += "Where '" & StreetTextBox.Text & "'" & "= Street" & "','" & TownTextBox.Text & "= Town" & "','" & CountyTextBox.Text & "= County" & "','" & TelephoneTextBox.Text & "= Telephone"""
        cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
        whichButtonDialogResult = MessageBox.Show("Are You Sure You Want To Mark Record As Deleted?", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If whichButtonDialogResult = DialogResult.Yes Then  'VERIFIES SELECTION
            Try
                conn.Open()
                If ConnectionState.Open.ToString = "Open" Then
                    numRowsAddedInteger = cmd.ExecuteNonQuery()

                    MessageBox.Show("Number of rows deleted :" + numRowsAddedInteger.ToString)
                End If
            Catch                               'CONFIRMS DELETION
                MessageBox.Show("Number of rows deleted :" + numRowsAddedInteger.ToString)
            End Try
            conn.Close()
            Me.Focus()
            DisplayItems(5)
        Else
        End If
    End Sub
End Class
Edited by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
Member Avatar for Oxiegen

In your code where you add a new customer, remember to also include "DeleteFlag = False" in your SQL for storing the customer. INSERT INTO CustomerTable (......,DeleteFlag) VALUES (......,False) Add or change the code written i red:

Private Sub DisplayItems(ByVal num As Integer)
'Loads recordset & outputs to list box

Dim ConnectionString As String
Dim SQLString As String
Dim TitleString As String = " "
Dim conn As System.Data.OleDb.OleDbConnection
Dim dr As System.Data.OleDb.OleDbDataReader
Dim cmd As System.Data.OleDb.OleDbCommand

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & "Opticians.accdb "

conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

'Access the fields
SQLString = "SELECT CustomerID,Surname,Forename FROM CustomerTable WHERE DeletedFlag=false OR DeletedFlag IS NULL"
Try 'was database found etc.

cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
conn.Open()

If (ConnectionState.Open.ToString = "Open") Then

dr = cmd.ExecuteReader()

If dr.HasRows Then
DisplayCustomersListBox.Items.Clear()

While dr.Read
If Not IsDBNull(dr.Item("CustomerId")) Then
TitleString += dr.Item("CustomerID") & " "
TitleString += dr.Item("Surname") & " "
TitleString += dr.Item("Forename") & " "

DisplayCustomersListBox.Items.Add(TitleString)

End If
End While
End If
End If

Catch
MessageBox.Show("Error accessing database")
End Try
conn.Close()
DisplayCustomersListBox.Items.Add(" ")
DisplayCustomersListBox.Items.Add("Count:" & DisplayCustomersListBox.Items.Count - 1)

End Sub

Private Sub DisplayCustomersListbox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DisplayCustomersListBox.SelectedIndexChanged
Dim ConnectionString As String
Dim SQLString As String
Dim cmd As System.Data.OleDb.OleDbCommand
Dim conn As System.Data.OleDb.OleDbConnection
Dim dr As System.Data.OleDb.OleDbDataReader
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & "CarRentalsSystem.accdb "
conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
StreetTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
TownTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
CountyTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
TelephoneTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)

SQLString = "SELECT * FROM CustomerTable "
SQLString += "Where '" & StreetTextBox.Text & "','" & TownTextBox.Text & "','" & CountyTextBox.Text & "','" & TelephoneTextBox.Text & ")"

Try
conn.Open()
If ConnectionState.Open.ToString = "Open" Then
cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
dr = cmd.ExecuteReader()
If dr.HasRows Then
dr.Read()
If Not IsDBNull(dr.Item("Street")) Then
StreetTextBox.Text = dr.Item("Street").ToString
End If
If Not IsDBNull(dr.Item("Town")) Then
TownTextBox.Text = dr.Item("Town").ToString
End If
If Not IsDBNull(dr.Item("County")) Then
CountyTextBox.Text = dr.Item("County").ToString
End If
If Not IsDBNull(dr.Item("Telephone")) Then
TelephoneTextBox.Text = dr.Item("Telephone").ToString
End If

If Not IsDBNull(dr.Item("DeletedFlag")) Then
DeleteCheckBox.Checked = dr.Item("DeletedFlag").ToString
End If
End If
End If
Catch ex As Exception
End Try
End Sub

Private Sub DeleteCustomerButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteCustomerButton.Click
Dim ConnectionString As String
Dim SQLString As String
Dim whichButtonDialogResult As DialogResult
Dim Street As String = ""
Dim Town As String = ""
Dim County As String = ""
Dim Telephone As Integer = (0)
Dim numRowsAddedInteger As Integer
Dim cmd As System.Data.OleDb.OleDbCommand
Dim conn As System.Data.OleDb.OleDbConnection
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & "CarRentalsSystem.accdb "
conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
SQLString = "UPDATE CustomerTable Set "
SQLString += "DeletedFlag= True" 'UPDATES DELETED FLAG TO TRUE
SQLString += "Where '" & StreetTextBox.Text & "'" & "= Street" & "','" & TownTextBox.Text & "= Town" & "','" & CountyTextBox.Text & "= County" & "','" & TelephoneTextBox.Text & "= Telephone"""
cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
whichButtonDialogResult = MessageBox.Show("Are You Sure You Want To Mark Record As Deleted?", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If whichButtonDialogResult = DialogResult.Yes Then 'VERIFIES SELECTION
Try
conn.Open()
If ConnectionState.Open.ToString = "Open" Then
numRowsAddedInteger = cmd.ExecuteNonQuery()

MessageBox.Show("Number of rows deleted :" + numRowsAddedInteger.ToString)
End If
Catch 'CONFIRMS DELETION
MessageBox.Show("Number of rows deleted :" + numRowsAddedInteger.ToString)
End Try
conn.Close()
Me.Focus()
DisplayItems(5)
Else
End If
End Sub
End Class

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.