mysql - 删除 VB.NET 中的 ListView 项目

Question

如何在数据库中同时删除 ListView 中的项目？我有这段代码，但我不知道出了什么问题，但它运行良好并且没有错误，但它似乎没有删除任何项目？

这是我的代码:

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

我意识到这是一篇旧帖子；但是，我想指出，创建一个处理数据库连接的子例程是一个很好的做法。我注意到，在每个子程序中，您都会使值变暗并一遍又一遍地启动查询连接。为了简单起见，我这样做了。

我创建了 1 个变量和 2 个函数。当然，这些应该放在一个模块中。该变量名为“MyConnection”。这会将您的连接字符串声明为常量。

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

现在我们将拥有从 SQL 数据库获取数据的函数:

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

现在我们在 SQL 数据库中设置数据的函数:

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

我还使用数据集而不是表，以简化您可能想要一次提取整个数据库的情况。无论如何，这就是您将如何使用它们。

要获取数据，只需执行以下操作:

Dim Cmd as String = ""
Dim Data as DataSet

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

还有责备。 4 行代码即可获取您的信息。此查询将加载包含 1 个表的数据集，无论您的命令提取多少行；像这样:

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

我只是将其留在这里，供任何使用它的人使用。在我看来，简化日常工作是更好的做法。