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 6 Years Ago by Miss Confused: n/a

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

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???!!!!!

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 6 Years Ago by Oxiegen: n/a

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 6 Years Ago by __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

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

This article has been dead for over six months. Start a new discussion instead.