+- BP Forums (https://bpforums.info)
+-- Forum: Archived Forums (https://bpforums.info/forumdisplay.php?fid=55)
+--- Forum: Archived Forums (https://bpforums.info/forumdisplay.php?fid=56)
+---- Forum: VB.NET (Visual Basic 2010/2008) (https://bpforums.info/forumdisplay.php?fid=8)
+----- Forum: Programming Help (https://bpforums.info/forumdisplay.php?fid=9)
+----- Thread: How do I use Listview to delete MySQL DB records (/showthread.php?tid=705)
How do I use Listview to delete MySQL DB records - kismetgerald - 11-25-2012
Hello everyone,
I trust you're all doing well. My application has come a long way and is almost complete - can't wait to share it with you guys. There are two things left to do - create reports, and the second is what I'm asking about in this thread.
So, here's what I want to do and don't know how to - hope you can help:
I want to create a functionality that allows me to retrieve records from the MySQL DB, store the results in a Listview control, then use that along with some buttons to delete individual records or multiple records (either based on selecting multiple rows or checking multiple checkboxes provided by the control). In either case, I want to be able to show a confirmation dialog box before executing the query that will delete the records.
I'm really looking forward to your assistance with this. I am willing to make the entire solution and DB available if necessary.
Thanks.
Re: How do I use Listview to delete MySQL DB records - brandonio21 - 11-25-2012
Do you need help with showing the confirmation box? Or actually deleting the records?
If it is the former, the operation is really quick. Something like this would work:
[code2=vbnet]Dim confirmation As DialogResult = MessageBox.Show("Do you really want to delete these users?", "Are you sure?", MessageBoxButtons.YesNo)
If (confirmation = Windows.Forms.DialogResult.Yes) Then
'Delete users
Else
'Don't delete users
End If[/code2]
If you need help deleting the users, that code is a little more involved (since it relays with the DB), but let us know if you need help with that!
Re: How do I use Listview to delete MySQL DB records - kismetgerald - 11-25-2012
Hey Brandon,
I knew you'd come to the rescue. Yes, my need for help is with the former. I do know how to display the confirmation dialog, as well as write the DELETE statement for the DB.
What I don't know how to do is to base the delete action on the row or rows selected from the Listview. I've seen applications where each row displays a delete button on the row itself. And I've seen others where there's a delete button somewhere on the form that deletes the selected row or rows when clicked.
Hope this explanation helps.
Re: How do I use Listview to delete MySQL DB records - kismetgerald - 11-25-2012
Hey Brandon, I figured I'd share the code I'm using to populate my Listview. I'm calling this Sub on the FormLoad event.
[code2=vbnet]Private Sub loadRecords()
Dim dbConn As New MySqlConnection
Dim dbTable As New DataTable
dbTable.Clear()
Try
'FOR MySQL DATABASE USE
Dim dbQuery As String = ""
Dim dbCmd As New MySqlCommand
Dim dbAdapter As New MySqlDataAdapter
Dim i As Integer
If dbConn.State = ConnectionState.Closed Then
dbConn.ConnectionString = String.Format("Server={0};Port={1};Uid={2};Password={3};Database=accounting", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password)
dbConn.Open()
End If
dbQuery = "SELECT *" & _
"FROM cc_master INNER JOIN customer ON customer.accountNumber = cc_master.customer_accountNumber"
With dbCmd
.CommandText = dbQuery
.Connection = dbConn
End With
With dbAdapter
.SelectCommand = dbCmd
.Fill(dbTable)
End With
ListViewRecords.Items.Clear()
For i = 0 To dbTable.Rows.Count - 1
With ListViewRecords
.Items.Add(dbTable.Rows(i)("ccID"))
With .Items(.Items.Count - 1).SubItems
.Add(dbTable.Rows(i)("ccNumber"))
.Add(dbTable.Rows(i)("ccExpireMonth"))
.Add(dbTable.Rows(i)("ccExpireYear"))
.Add(dbTable.Rows(i)("nameCOMPANY"))
End With
End With
Next
Catch ex As MySqlException
MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
End Try
dbConn.Close()
End Sub[/code2]
Re: How do I use Listview to delete MySQL DB records - brandonio21 - 11-25-2012
Sorry for the limited post, I'm about to head to bed. I was thinking something like this:
[code2=vbnet]For Each item As ListViewItem In ListView1.SelectedItems
Dim ccNumber As String = item.SubItems(0).ToString
Dim ccExpireMonth As String = item.SubItems(1).ToString
Dim ccExpireYear As String = item.SubItems(2).ToString
Dim nameCompany As String = item.SubItems(3).ToString
Dim sqlQuery As String = "DELETE FROM cc_master WHERE ccNumber='" & ccNumber & "', " & _
"ccExpireMonth='" & ccExpireMonth & "', ccExpireYear='" & ccExpireYear
'Here we would run the query
Next[/code2]
I can further explain it later if you want me to!
Re: How do I use Listview to delete MySQL DB records - kismetgerald - 11-25-2012
Thanks for your response - really appreciate it. While waiting, I started playing around and managed to come up with this after watching a couple of Youtube videos and searching online:
HERE'S MY SOLUTION - PLEASE CRITIQUE IT:
[code2=vbnet]Private Sub ButtonDelete_Click(sender As System.Object, e As System.EventArgs) Handles ButtonDelete.Click
'FOR MySQL DATABASE USE
Dim dbConn As New MySqlConnection(String.Format("Server={0};Port={1};Uid={2};Password={3};Database=accounting", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password))
Dim dbTable As New DataTable
Dim dbQuery As String = ""
'Dim dbCmd As New MySqlCommand
Dim confirmation As DialogResult = MessageBox.Show("This action will delete the selected Customer Credit Cards from the database.", "Are you sure?", MessageBoxButtons.YesNo)
If (confirmation = Windows.Forms.DialogResult.Yes) Then
'Delete Records
Try
If ListViewRecords.Items.Count > 0 Then
For i = ListViewRecords.Items.Count - 1 To 0 Step -1
If ListViewRecords.Items(i).Checked = True Then
dbQuery = "DELETE FROM cc_master WHERE ccID = '" & ListViewRecords.Items(i).Text & "'"
Dim dbAdapter As New MySqlDataAdapter(dbQuery, dbConn)
dbAdapter.Fill(dbTable)
Call getRecordCount()
Call loadRecords()
End If
Next i
End If
Catch ex As Exception
MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
End Try
dbConn.Close()
dbConn.Dispose()
End If
End Sub[/code2]
Re: How do I use Listview to delete MySQL DB records - brandonio21 - 11-27-2012
Hm, interesting. Does this actually remove things from the database? Or does it simply delete things from the listview?
Re: How do I use Listview to delete MySQL DB records - kismetgerald - 11-27-2012
Yes, it works and actually deletes records from the DB. I checked the DB and the records are gone.
Re: How do I use Listview to delete MySQL DB records - brandonio21 - 11-28-2012
Hm, interesting! Well, I'm glad you got it all figured out! Good work!
Re: How do I use Listview to delete MySQL DB records - kismetgerald - 11-28-2012
Yeah, the only problem is it will only delete one record at a time. I'm still trying to figure out how to check multiple rows and delete all. Could use a little help..........lol. <!-- s
--><img src="{SMILIES_PATH}/icon_e_smile.gif" alt="" title="Smile" /><!-- s -->
Re: How do I use Listview to delete MySQL DB records - brandonio21 - 11-28-2012
If you hold down the control (or shift) key and select multiple rows, does that work?