mysql - 搜索数据网格查看确切的关键字

Question

我想让搜索结果准确。例如，我输入“L”，我的程序只显示其中包含字母“L”的任何结果，而不显示第一个字母是“L”的结果。那么如何做到这一点呢？

这是我的代码:

***在搜索框上搜索或输入之前，用户必须首先选择一个单选按钮来搜索列中的数据，在我的程序中有 3 个单选按钮(ID、姓氏、名字)

 Private Sub txtSearch_TextChanged(sender As Object, e As EventArgs) Handles txtSearch.TextChanged
        Dim DataTable As New DataTable
        Dim DataAdapter As New MySqlDataAdapter
        MySqlConn = New MySqlConnection
        MySqlConn = New MySqlConnection("server=localhost;user id=root;password=root;database=PatientManagementSystem;")
        MySqlConn.Open()
        'SEARCH ID
        If rdbtnID.Checked = True Then
            txtSearch.MaxLength = 9
            With cmd
                .Connection = MySqlConn
                .CommandText = "SELECT * FROM newpatient WHERE ID like '%" & txtSearch.Text & "%'"
            End With
            DataAdapter.SelectCommand = cmd
            DataTable.Clear()
            DataAdapter.Fill(DataTable)
            dgvRecords.DataSource = DataTable
            MySqlConn.Close()
            'SEARCH LASTNAME
        ElseIf rdbtnLastname.Checked = True Then
            txtSearch.MaxLength = 32767
            With cmd
                .Connection = MySqlConn
                .CommandText = "select * from newpatient where Lastname like '%" & txtSearch.Text & "%'"
            End With
            DataAdapter.SelectCommand = cmd
            DataTable.Clear()
            DataAdapter.Fill(DataTable)
            dgvRecords.DataSource = DataTable
            MySqlConn.Close()
            'SEARCH FIRSTNAME
        ElseIf rdbtnFirstname.Checked = True Then
            txtSearch.MaxLength = 32767
            With cmd
                .Connection = MySqlConn
                .CommandText = "SELECT * FROM newpatient WHERE Firstname like '%" & txtSearch.Text & "%'"
            End With
            DataAdapter.SelectCommand = cmd
            DataTable.Clear()
            DataAdapter.Fill(DataTable)
            dgvRecords.DataSource = DataTable
            MySqlConn.Close()

        End If

    End Sub

Answer 1

您需要从开头删除 % 之类的符号。

改变

"SELECT * FROM newpatient WHERE ID like '%" & txtSearch.Text & "%'"

进入:

"SELECT * FROM newpatient WHERE ID like '" & txtSearch.Text & "%'"

我还可以建议:(1) 如果这是生产系统，请使用参数化 sql；(2) 如果您坚持 sql 连接，则需要转义 txtSearch.Text 中用户键入 2 个单引号的任何单引号。否则sql语句将会失败。