mysql - 使用组合框和文本框进行搜索

Question

我有一个 VB 迷你作业，将搜索学生记录

搜索文本框用于记录和在组合框中选择的列，

Private Sub search()
    'search method for Search Record'
    Try
        If cmbKeywords.Text = "ID" Then
            Dim dbQuery As String = "Select * from tblstudents where id =" & txtSearch.Text
        ElseIf cmbKeywords.Text = "Name" Then
            Dim dbQuery As String = "Select * from tblstudents where id =" & txtSearch.Text
        ElseIf cmbKeywords.Text = "Address" Then
            Dim dbQuery As String = "Select * from tblstudents where id =" & txtSearch.Text
        ElseIf cmbKeywords.Text = "Contact" Then
            Dim dbQuery As String = "Select * from tblstudents where id =" & txtSearch.Text
        ElseIf cmbKeywords.Text = "Civil Status" Then
            Dim dbQuery As String = "Select * from tblstudents where id =" & txtSearch.Text
        End If
        Dim dbConnection As New MySqlConnection(dbConString)
        Dim dbCmd As New MySqlCommand(dbQuery, dbConnection)
        Dim dbReader As MySqlDataReader
        dbConnection.Open()
        dbReader = dbCmd.ExecuteReader()

        dgvRecords.ReadOnly = True

        dgvRecords.ColumnCount = 6
        dgvRecords.Columns(0).Name = "Stud ID"
        dgvRecords.Columns(1).Name = "Name"
        dgvRecords.Columns(2).Name = "Address"
        dgvRecords.Columns(3).Name = "Gender"
        dgvRecords.Columns(4).Name = "Status"
        dgvRecords.Columns(5).Name = "Contact No"

        While dbReader.Read()
            dgvRecords.Rows.Add(dbReader("id"), dbReader("lastname") & ", " & dbReader("firstname") & " " & dbReader("middlename"), dbReader("address"), dbReader("gender"), dbReader("civil_status"), dbReader("contact"))
        End While
        dbReader.Close()
        dbConnection.Close()
    Catch ex As Exception
        MsgBox("ERROR: " & ErrorToString(), MsgBoxStyle.Critical)
    End Try
End Sub

我在这一行有一个错误。

Dim dbCmd As New MySqlCommand(dbQuery, dbConnection)

它说 dbQuery 没有定义。有人可以帮我吗？抱歉，这里是 VB 新手。

Answer 1

这是由于 dbQuery 变量的范围造成的。正如您在 If- ElseIf-End If 中声明的那样，它是此条件的局部变量，在该条件之外它不存在。您必须将其设置为整个函数的本地函数，以便您可以在整个函数中引用它。下面是更好的答案。试试这个

Private Sub search()
'search method for Search Record'
Dim dbQuery As String
Try
    If cmbKeywords.Text = "ID" Then
        dbQuery = "Select * from tblstudents where id =" & txtSearch.Text
    ElseIf cmbKeywords.Text = "Name" Then
        dbQuery = "Select * from tblstudents where id =" & txtSearch.Text
    ElseIf cmbKeywords.Text = "Address" Then
        dbQuery = "Select * from tblstudents where id =" & txtSearch.Text
    ElseIf cmbKeywords.Text = "Contact" Then
        dbQuery = "Select * from tblstudents where id =" & txtSearch.Text
    ElseIf cmbKeywords.Text = "Civil Status" Then
        dbQuery = "Select * from tblstudents where id =" & txtSearch.Text
    End If
    Dim dbConnection As New MySqlConnection(dbConString)
    Dim dbCmd As New MySqlCommand(dbQuery, dbConnection)
    Dim dbReader As MySqlDataReader
    dbConnection.Open()
    dbReader = dbCmd.ExecuteReader()

    dgvRecords.ReadOnly = True

    dgvRecords.ColumnCount = 6
    dgvRecords.Columns(0).Name = "Stud ID"
    dgvRecords.Columns(1).Name = "Name"
    dgvRecords.Columns(2).Name = "Address"
    dgvRecords.Columns(3).Name = "Gender"
    dgvRecords.Columns(4).Name = "Status"
    dgvRecords.Columns(5).Name = "Contact No"

    While dbReader.Read()
        dgvRecords.Rows.Add(dbReader("id"), dbReader("lastname") & ", " & dbReader("firstname") & " " & dbReader("middlename"), dbReader("address"), dbReader("gender"), dbReader("civil_status"), dbReader("contact"))
    End While
    dbReader.Close()
    dbConnection.Close()
Catch ex As Exception
    MsgBox("ERROR: " & ErrorToString(), MsgBoxStyle.Critical)
End Try
End Sub