How do I use Listview to delete MySQL DB records

[kismetgerald]

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.

[brandonio21]

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!

[kismetgerald]

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.

[kismetgerald]

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]

[brandonio21]

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!

[kismetgerald]

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]

[brandonio21]

Hm, interesting. Does this actually remove things from the database? Or does it simply delete things from the listview?

[kismetgerald]

Yes, it works and actually deletes records from the DB. I checked the DB and the records are gone.

[brandonio21]

Hm, interesting! Well, I'm glad you got it all figured out! Good work!

[kismetgerald]

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

[brandonio21]

If you hold down the control (or shift) key and select multiple rows, does that work?