Ad:
 
  • VB.NET Discussion Thread
  • Unsolved
  • Views: 722
  • VB.NET RSS
Similar Threads
Mar 15th, 2010
0

Help to delete an entry in a database

Expand Post »
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!!!
Last edited by Miss Confused; Mar 15th, 2010 at 8:34 am.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Miss Confused is offline Offline
5 posts
since Mar 2010
Mar 15th, 2010
0

Re: Help to delete an entry in a database

Not sure how to do in python..but I think you can repopulate your list box items with out displaying the user selected item.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
web_test is offline Offline
10 posts
since Feb 2010
Mar 15th, 2010
0

Re: Help to delete an entry in a database

Click to Expand / Collapse  Quote originally posted by 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.
Hi
Im not using python im using Visual Basic 2008 and Microsoft Access???!!!!!
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Miss Confused is offline Offline
5 posts
since Mar 2010
Mar 17th, 2010
0

Re: Help to delete an entry in a database

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
Last edited by Oxiegen; Mar 17th, 2010 at 8:14 am.
Sponsor
Reputation Points: 41
Solved Threads: 79
Posting Pro in Training
Oxiegen is offline Offline
468 posts
since Jun 2006
Mar 17th, 2010
0

Re: Help to delete an entry in a database

Click to Expand / Collapse  Quote originally posted by 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
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

VB.NET Syntax (Toggle Plain Text)
  1. Private Sub DeleteCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2. DisplayItems(5) ' Loads Method to display the list
  3. End Sub
  4. Private Sub DisplayItems(ByVal num As Integer)
  5.  
  6.  
  7. 'Loads recordset & outputs to list box
  8.  
  9. Dim ConnectionString As String
  10. Dim SQLString As String
  11. Dim TitleString As String = " "
  12. Dim conn As System.Data.OleDb.OleDbConnection
  13. Dim dr As System.Data.OleDb.OleDbDataReader
  14. Dim cmd As System.Data.OleDb.OleDbCommand
  15.  
  16. ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
  17. ConnectionString += "Source=" & "Opticians.accdb "
  18.  
  19. conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
  20.  
  21. 'Access the fields
  22. SQLString = "SELECT CustomerID,Surname,Forename FROM CustomerTable "
  23. Try 'was database found etc.
  24.  
  25. cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
  26. conn.Open()
  27.  
  28. If (ConnectionState.Open.ToString = "Open") Then
  29.  
  30. dr = cmd.ExecuteReader()
  31.  
  32. If dr.HasRows Then
  33. DisplayCustomersListBox.Items.Clear()
  34.  
  35. While dr.Read
  36. If Not IsDBNull(dr.Item("CustomerId")) Then
  37. TitleString += dr.Item("CustomerID") & " "
  38. TitleString += dr.Item("Surname") & " "
  39. TitleString += dr.Item("Forename") & " "
  40.  
  41. DisplayCustomersListBox.Items.Add(TitleString)
  42.  
  43. End If
  44. End While
  45. End If
  46. End If
  47.  
  48. Catch
  49. MessageBox.Show("Error accessing database")
  50. End Try
  51. conn.Close()
  52. DisplayCustomersListBox.Items.Add(" ")
  53. DisplayCustomersListBox.Items.Add("Count:" & DisplayCustomersListBox.Items.Count - 1)
  54.  
  55. End Sub
  56.  
  57. Private Sub DisplayCustomersListbox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DisplayCustomersListBox.SelectedIndexChanged
  58. Dim ConnectionString As String
  59. Dim SQLString As String
  60. Dim cmd As System.Data.OleDb.OleDbCommand
  61. Dim conn As System.Data.OleDb.OleDbConnection
  62. Dim dr As System.Data.OleDb.OleDbDataReader
  63. ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
  64. ConnectionString += "Source=" & "CarRentalsSystem.accdb "
  65. conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
  66. StreetTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
  67. TownTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
  68. CountyTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
  69. TelephoneTextBox.Text = DisplayCustomersListBox.Text.Substring(0, 11)
  70.  
  71. SQLString = "SELECT * FROM CustomerTable "
  72. SQLString += "Where '" & StreetTextBox.Text & "','" & TownTextBox.Text & "','" & CountyTextBox.Text & "','" & TelephoneTextBox.Text & ")"
  73.  
  74. Try
  75. conn.Open()
  76. If ConnectionState.Open.ToString = "Open" Then
  77. cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
  78. dr = cmd.ExecuteReader()
  79. If dr.HasRows Then
  80. dr.Read()
  81. If Not IsDBNull(dr.Item("Street")) Then
  82. StreetTextBox.Text = dr.Item("Street").ToString
  83. End If
  84. If Not IsDBNull(dr.Item("Town")) Then
  85. TownTextBox.Text = dr.Item("Town").ToString
  86. End If
  87. If Not IsDBNull(dr.Item("County")) Then
  88. CountyTextBox.Text = dr.Item("County").ToString
  89. End If
  90. If Not IsDBNull(dr.Item("Telephone")) Then
  91. TelephoneTextBox.Text = dr.Item("Telephone").ToString
  92. End If
  93.  
  94. If Not IsDBNull(dr.Item("DeletedFlag")) Then
  95. DeleteCheckBox.Checked = dr.Item("DeletedFlag").ToString
  96. End If
  97. End If
  98. End If
  99. Catch ex As Exception
  100. End Try
  101. End Sub
  102.  
  103. Private Sub DeleteCustomerButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteCustomerButton.Click
  104. Dim ConnectionString As String
  105. Dim SQLString As String
  106. Dim whichButtonDialogResult As DialogResult
  107. Dim Street As String = ""
  108. Dim Town As String = ""
  109. Dim County As String = ""
  110. Dim Telephone As Integer = (0)
  111. Dim numRowsAddedInteger As Integer
  112. Dim cmd As System.Data.OleDb.OleDbCommand
  113. Dim conn As System.Data.OleDb.OleDbConnection
  114. ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
  115. ConnectionString += "Source=" & "CarRentalsSystem.accdb "
  116. conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
  117. SQLString = "UPDATE CustomerTable Set "
  118. SQLString += "DeletedFlag= True" 'UPDATES DELETED FLAG TO TRUE
  119. SQLString += "Where '" & StreetTextBox.Text & "'" & "= Street" & "','" & TownTextBox.Text & "= Town" & "','" & CountyTextBox.Text & "= County" & "','" & TelephoneTextBox.Text & "= Telephone"""
  120. cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
  121. whichButtonDialogResult = MessageBox.Show("Are You Sure You Want To Mark Record As Deleted?", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
  122. If whichButtonDialogResult = DialogResult.Yes Then 'VERIFIES SELECTION
  123. Try
  124. conn.Open()
  125. If ConnectionState.Open.ToString = "Open" Then
  126. numRowsAddedInteger = cmd.ExecuteNonQuery()
  127.  
  128. MessageBox.Show("Number of rows deleted :" + numRowsAddedInteger.ToString)
  129. End If
  130. Catch 'CONFIRMS DELETION
  131. MessageBox.Show("Number of rows deleted :" + numRowsAddedInteger.ToString)
  132. End Try
  133. conn.Close()
  134. Me.Focus()
  135. DisplayItems(5)
  136. Else
  137. End If
  138. End Sub
  139. End Class
Last edited by adatapost; Mar 18th, 2010 at 10:40 pm. Reason: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Miss Confused is offline Offline
5 posts
since Mar 2010
Mar 18th, 2010
0

Re: Help to delete an entry in a database

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
Sponsor
Reputation Points: 41
Solved Threads: 79
Posting Pro in Training
Oxiegen is offline Offline
468 posts
since Jun 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in VB.NET Forum Timeline: TextBox save to txt file without using the save file dialog
Next Thread in VB.NET Forum Timeline: hai...





About Us | Contact Us | Advertise | Acceptable Use Policy
Build Custom RSS Feed


Follow us on Twitter


© 2010 DaniWeb® LLC