Delete ListView items in VB.NET

Question

How do i delete items in the ListView at the same time in my database? I have this code but i don't know what's wrong,but it functions well and it has no errors but it seems that it's not deleting any items?

here's my code:

Imports MySql.Data.MySqlClient
Public Class Form5

Dim id As Integer
Dim con As New MySqlConnection
Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim con As New MySqlConnection
    If con.State = ConnectionState.Closed Then
        con.ConnectionString = "server=localhost;user id=root;database=db;password=root"
        con.Open()
    End If
    LoadPeople()
End Sub

Public Sub LoadPeople()
    Dim sConnection As New MySqlConnection
    sConnection.ConnectionString = "server=localhost;user id=root;database=db;password=root"
    sConnection.Open()
    Dim sqlQuery As String = "SELECT * FROM candidate WHERE cfname<>'Select a Candidate' AND candidacy='Filed'"
    Dim sqlAdapter As New MySqlDataAdapter
    Dim sqlCommand As New MySqlCommand
    Dim TABLE As New DataTable
    Dim i As Integer


    With sqlCommand
        .CommandText = sqlQuery
        .Connection = sConnection
    End With

    With sqlAdapter
        .SelectCommand = sqlCommand
        .Fill(TABLE)
    End With

    LvPeople.Items.Clear()

    For i = 0 To TABLE.Rows.Count - 1
        With LvPeople
            .Items.Add(TABLE.Rows(i)("idn"))
            With .Items(.Items.Count - 1).SubItems
                .Add(AddFieldValue(TABLE.Rows(i), ("cpos")))
                .Add(AddFieldValue(TABLE.Rows(i), ("cfname")))
                .Add(AddFieldValue(TABLE.Rows(i), ("cmname")))
                .Add(AddFieldValue(TABLE.Rows(i), ("clname")))
                .Add(AddFieldValue(TABLE.Rows(i), ("cparty")))
            End With
        End With
    Next
End Sub

Private Function AddFieldValue(ByVal row As DataRow, ByVal fieldName As String) As String
    If Not DBNull.Value.Equals(row.Item(fieldName)) Then
        Return CStr(row.Item(fieldName))
    Else
        Return Nothing
    End If
End Function

Private Sub lvPeople_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles LvPeople.MouseClick
    id = LvPeople.SelectedItems(0).Selected
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim con As New MySqlConnection
    Dim sqlCommand As New MySqlCommand
    con.ConnectionString = "server = localhost; user id = root; database = db; password = root"
    sqlCommand.Connection = con
    con.Open()

    If id = Nothing Then
        MsgBox("Please choose a record to remove.", MsgBoxStyle.Exclamation)
    Else
        Dim result1 As DialogResult = MessageBox.Show("Are you sure to delete this entry?", "Delete Entry", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If result1 = DialogResult.Yes Then

            Dim sqlQuery As String = "DELETE FROM candidate WHERE cid='" & LvPeople.SelectedItems(0).Text & "'"

            With sqlCommand
                .CommandText = sqlQuery
                .Connection = con
                .ExecuteNonQuery()
            End With
            MsgBox("Record Removed", MsgBoxStyle.Information)
            LoadPeople()
            id = Nothing
        End If
    End If
End Sub
End Class

Answer 1

I realize that this is an old post; however, I would like to point out that it would be good practice to make a sub routine that handles your database connection. I noticed that in each of your subs you are dimming the values and initiating the query connection over and over again. Here's what I do for simplicity.

I make 1 variable and 2 functions. These should be placed in a module, naturally. The variable is named 'MyConnection'. This will have your connection string declared as a constant.

Public Const MyConnection as String = "server=localhost;user id=root;database=db;password=root"

Now we'll have our function for getting data from the SQL database:

Public Function GetData(ByVal Command As String, Optional ByVal TableName As String = "", Optional ByRef RecErr As Boolean = False) As DataSet
    Dim DB As New MySqlConnection
    Dim DA As New MySqlDataAdapter
    Dim Cmd As New MySqlCommand
    Dim tmpData As New DataSet

    Try
        DB.ConnectionString = MyConnection
        DB.Open()
        Cmd.Connection = DB
        Cmd.CommandText = Command
        DA.SelectCommand = Cmd
        If Len(TableName) > 0 Then
            DA.Fill(tmpData, TableName)
        Else
            DA.Fill(tmpData)
        End If

        Return tmpData
        DB.Close()
        DB.Dispose()
        DA.Dispose()
        Cmd.Dispose()
        tmpData.Dispose()

    Catch ex As MySqlException
        RecErr = True
        MessageBox.Show("Unable to access database: " & ex.Message)
    End Try

    Return tmpData

End Function

And now our function for setting data in the SQL database:

Public Function SetData(ByVal Command As String, ByVal TableName As String, Optional ByRef RecErr As Boolean = False) As Integer
    Dim DB As New MySqlConnection
    Dim Cmd As New MySqlCommand
    Dim Ret As Integer

    Try
        DB.ConnectionString = MyConnection
        DB.Open()
        Cmd.Connection = DB
        Cmd.CommandText = Command
        Ret = Cmd.ExecuteNonQuery()

        DB.Close()
        DB.Dispose()
        Cmd.Dispose()

    Catch ex As MySqlException
        RecErr = True
        MessageBox.Show("Unable to access database: " & ex.Message)
    End Try

    Return Ret
End Function

I also use DataSets instead of Tables for the simplicity of an occasion where you might want to pull the entire database at one time. Anyway, here's how you would use them.

To get data, just do this:

Dim Cmd as String = ""
Dim Data as DataSet

Cmd = "SELECT * FROM candidate WHERE cfname<>'Select a Candidate' AND candidacy='Filed'"
Data = GetData(Cmd, "candidate")

And blam. You have your info in 4 lines of code. This query will load the dataset with 1 table and however many rows your command pulls; like such:

CandidateName = Data.Tables(0).Rows(WhateverRowHasYourData).Item(CandidateNameColumnID).ToString

I'm just going to leave this here for whomever finds use of it. It's just better practice to simplify your routines, in my opinion.

Answer 2

Okay, problem solved. the only thing wrong here is in my DELETE query in WHERE clause.

i should've changed this:

"DELETE FROM candidate WHERE cid='" & LvPeople.SelectedItems(0).Text & "'"

to this:

"DELETE FROM candidate WHERE idn='" & LvPeople.SelectedItems(0).Text & "'"

because that cid is not in the ListView column.

Public Sub LoadPeople()
Dim sConnection As New MySqlConnection
sConnection.ConnectionString = "server=localhost;user id=root;database=db;password=root"
sConnection.Open()
Dim sqlQuery As String = "SELECT * FROM candidate WHERE cfname<>'Select a Candidate' AND candidacy='Filed'"
Dim sqlAdapter As New MySqlDataAdapter
Dim sqlCommand As New MySqlCommand
Dim TABLE As New DataTable
Dim i As Integer


With sqlCommand
    .CommandText = sqlQuery
    .Connection = sConnection
End With

With sqlAdapter
    .SelectCommand = sqlCommand
    .Fill(TABLE)
End With

LvPeople.Items.Clear()

For i = 0 To TABLE.Rows.Count - 1
    With LvPeople
        .Items.Add(TABLE.Rows(i)("idn"))      <--------HERE----|
        With .Items(.Items.Count - 1).SubItems
            .Add(AddFieldValue(TABLE.Rows(i), ("cpos")))
            .Add(AddFieldValue(TABLE.Rows(i), ("cfname")))
            .Add(AddFieldValue(TABLE.Rows(i), ("cmname")))
            .Add(AddFieldValue(TABLE.Rows(i), ("clname")))
            .Add(AddFieldValue(TABLE.Rows(i), ("cparty")))
        End With
    End With
Next
End Sub

Wooh! that took me 1 day.