大家好!我想在这里寻求有关我的代码的帮助。主要关注的是从 MySQL 数据库中搜索文本框提供的关键字。这是我的 GUI 以供引用。

当我在文本框中键入搜索键并在组合框中选择一列时，搜索查询将在 ListView 中显示其输出。我尝试了多种组合来获得输出，但无济于事。

这是我的代码，您可以帮助我。

Private Sub Search()

    lviClientList.Items.Clear()
    Dim strSqlSearch As String = "SELECT code, Company, StAdd, City, ContactPerson, Phone, Mobile, Email, Remarks FROM tblclients WHERE '@Column' LIKE '%" & txtSearchCriteria.Text & "%'"

    Dim item As New ListViewItem()

    If cboColumns.SelectedIndex = 0 Then
        column = "code"
    ElseIf cboColumns.SelectedIndex = 1 Then
        column = "Company"
    ElseIf cboColumns.SelectedIndex = 2 Then
        column = "StAdd"
    ElseIf cboColumns.SelectedIndex = 3 Then
        column = "City"
    ElseIf cboColumns.SelectedIndex = 4 Then
        column = "ContactPerson"
    ElseIf cboColumns.SelectedIndex = 5 Then
        column = "Phone"
    ElseIf cboColumns.SelectedIndex = 6 Then
        column = "Mobile"
    ElseIf cboColumns.SelectedIndex = 7 Then
        column = "Email"
    ElseIf cboColumns.SelectedIndex = 8 Then
        column = "Remarks"
    End If

    Dim mysqlCommand As New MySqlCommand(strSqlSearch, mysqlConnection)
    mysqlCommand.Parameters.AddWithValue("@Column", column)

    Try
        mysqlConnection.Open()
        mysqlReader = mysqlCommand.ExecuteReader()

        Do While mysqlReader.Read()

            item = lviClientList.Items.Add(mysqlReader("code").ToString)
            item.SubItems.Add(mysqlReader("Company").ToString)
            item.SubItems.Add(mysqlReader("StAdd").ToString)
            item.SubItems.Add(mysqlReader("City").ToString)
            item.SubItems.Add(mysqlReader("ContactPerson").ToString)
            item.SubItems.Add(mysqlReader("Phone").ToString)
            item.SubItems.Add(mysqlReader("Mobile").ToString)
            item.SubItems.Add(mysqlReader("Email").ToString)
            item.SubItems.Add(mysqlReader("Remarks").ToString)

        Loop

    Catch ex As Exception

        MsgBox("No results found.", MsgBoxStyle.OkOnly, "Project Analysis System")

    Finally

        mysqlReader.Close()
        mysqlConnection.Close()

    End Try

End Sub

最佳答案

目前尚不清楚为什么您的代码无法正常工作。尝试更改 Catch 子句中的代码

MsgBox("No results found.", MsgBoxStyle.OkOnly, "Project Analysis System")

进入

Msgbox(ex.Message.ToString(), MsgBoxStyle.OkOnly, "Project Analysis System")

所以你会知道确切的错误是什么。

您可以连接 ColumnName 的值，因为它是在您的代码中静态设置的。但是 WHERE 上的值应该被参数化，因为它是用户输入的值。

试试这个修改后的代码，

Private Sub Search()

    lviClientList.Items.Clear()
    Dim item As New ListViewItem()
    Dim _isFound As Boolean = False

    Dim colName() As String = {"code", "Company", "StAdd", "City", "ContactPerson", "Phone", "Mobile", "Email", "Remarks"}

    Dim strSqlSearch As String = "SELECT code, Company, StAdd, City, " & _
                                    "ContactPerson, Phone, Mobile, Email, Remarks " & _
                                    "FROM tblclients " & _
                                    "WHERE " & colName(cboColumns.SelectedIndex) & " LIKE CONCAT('%', @valueName, '%')"

    Using myConn As New MySqlConnection("connectionStringHere")
        Using myComm As New MySqlCommand()
            With myComm
                .Connection = myConn
                .CommandType = CommandType.Text
                .CommandText = strSqlSearch
                .Parameters.AddWithValue("@valueName", txtSearchCriteria.Text);
            End With
            Try
                myConn.Open()
                Dim myReader As MySqlDataReader = myComm.ExecuteReader()

                While myReader.Read()
                    _isFound = True
                    item = lviClientList.Items.Add(myReader("code").ToString)
                    item.SubItems.Add(myReader("Company").ToString)
                    item.SubItems.Add(myReader("StAdd").ToString)
                    item.SubItems.Add(myReader("City").ToString)
                    item.SubItems.Add(myReader("ContactPerson").ToString)
                    item.SubItems.Add(myReader("Phone").ToString)
                    item.SubItems.Add(myReader("Mobile").ToString)
                    item.SubItems.Add(myReader("Email").ToString)
                    item.SubItems.Add(myReader("Remarks").ToString)
                End While

                If Not _isFound Then
                    MsgBox("No results found.", MsgBoxStyle.OkOnly, "Project Analysis System")
                End If

            Catch ex As MySqlException
                Msgbox(ex.Message.ToString(), MsgBoxStyle.OkOnly, "Project Analysis System")
            End Try
        End Using
    End Using

End Sub

关于mysql - VB.NET - 使用文本框和组合框的搜索功能，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/12662768/