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 0 Newbie Poster
Edited by Miss Confused because: n/a
web_test 0 Newbie Poster
Not sure how to do in python..but I think you can repopulate your list box items with out displaying the user selected item.
Miss Confused 0 Newbie Poster
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???!!!!!
Oxiegen 88 Basically an Occasional Poster Featured Poster
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
Miss Confused 0 Newbie Poster
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).
Oxiegen 88 Basically an Occasional Poster Featured Poster
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 boxDim 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.OleDbCommandConnectionString = "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 IfCatch
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 IfIf 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 SubPrivate 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.