Help to delete an entry in a database

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

Miss Confused

Newbie Poster

5 posts since Mar 2010

Reputation Points: 10

Solved Threads: 0

2 Years Ago

0

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

web_test

Newbie Poster

10 posts since Feb 2010

Reputation Points: 10

Solved Threads: 0

2 Years Ago

0

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

Miss Confused

Newbie Poster

5 posts since Mar 2010

Reputation Points: 10

Solved Threads: 0

2 Years Ago

0

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

Oxiegen

Master Poster

715 posts since Jun 2006

Reputation Points: 87

Solved Threads: 141

2 Years Ago

0

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

Miss Confused

Newbie Poster

5 posts since Mar 2010

Reputation Points: 10

Solved Threads: 0

2 Years Ago

0

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

Oxiegen

Master Poster

715 posts since Jun 2006

Reputation Points: 87

Solved Threads: 141

Help to delete an entry in a database

This article has been dead for over three months