sql - 使用组合框搜索数据库并显示多个结果

Question

我正在尝试创建一个根据名称搜索动物的程序。我目前有这部分工作，但我想添加一个可选搜索，以便用户可以搜索特定数据，例如体重，组合框将显示所有具有匹配体重的动物的结果，并且用户可以然后选择他想要的那个，它会打开我设计的表格，显示正确的数据。不幸的是，我对编程只有非常基本的了解，因此非常感谢任何帮助。

这是我当前的代码。主要搜索功能有效，但可选功能无效。有人告诉我这是由于我的编程方式所致，因为每一行 sql 都会自行替换，因此它只会以最后一个答案结束。

Private Sub btnsear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsear.Click
    If (txtname.Text = "") Then
        MsgBox("Invalid Search")
    Else
        Try
            Dim newsql As String
            newsql = "select * from Animals where AnimalName like " & "'%" & txtname.Text & "%'"
            'MsgBox("select * from Animals where AnimalName like " & "'" & txtname.Text & "'")
            'msgbox(newsql)
            Dim con As New OleDb.OleDbConnection
            Dim da As New OleDb.OleDbDataAdapter

            ' dim ds as NewDataTable
            Dim dt As New DataTable("Animals")
            ' uses the 2010 compatible connection string
            con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = h:\Animals.accdb"
            con.Open()

            da = New OleDb.OleDbDataAdapter(newsql, con)
            da.Fill(dt)

            Form2.Show()

            'show name in unbound text box
            Form2.nametxt.Text = dt.Rows(0).Item(1)
            Form2.latintxt.Text = dt.Rows(0).Item(2)
            Form2.locationtxt.Text = dt.Rows(0).Item(3)
            Form2.heighttxt.Text = dt.Rows(0).Item(4)
            Form2.weighttxt.Text = dt.Rows(0).Item(5)
            Form2.diettxt.Text = dt.Rows(0).Item(6)
            Form2.statustxt.Text = dt.Rows(0).Item(7)
            Form2.lifetxt.Text = dt.Rows(0).Item(8)
            Form2.breedtxt.Text = dt.Rows(0).Item(9)
            Form2.lengthtxt.Text = dt.Rows(0).Item(10)
            Form2.txtimage.Text = dt.Rows(0).Item(11)

        Catch
            MsgBox("Item Not Found")
            'con.close()
        End Try
    End If

    If (txtopt.Text = "'") Then
        Try
            Dim sql1 As String
            Dim sql2 As String
            Dim sql3 As String
            Dim sql4 As String
            Dim sql5 As String
            Dim sql6 As String
            Dim sql7 As String
            Dim sql8 As String
            Dim sql9 As String
            Dim sql10 As String

            sql1 = "select * from Animals where AnimalName like " & "'%" & txtopt.Text & "%'"
            sql2 = "select * from Animals where LatinName like " & "'%" & txtopt.Text & "%'"
            sql3 = "select * from Animals where Location like " & "'%" & txtopt.Text & "%'"
            sql4 = "select * from Animals where AverageHeight like " & "'%" & txtopt.Text & "%'"
            sql5 = "select * from Animals where AverageWeight like " & "'%" & txtopt.Text & "%'"
            sql6 = "select * from Animals where DietaryNeeds like " & "'%" & txtopt.Text & "%'"
            sql7 = "select * from Animals where ConservationStatus like " & "'%" & txtopt.Text & "%'"
            sql8 = "select * from Animals where AverageLifeSpan like " & "'%" & txtopt.Text & "%'"
            sql9 = "select * from Animals where BreedingSeason like " & "'%" & txtopt.Text & "%'"
            sql10 = "select * from Animals where AverageLength like " & "'%" & txtopt.Text & "%'"

        Catch
        End Try
    End If

End Sub

Answer 1

尝试类似...

Private Sub btnsear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsear.Click
    If txtname.Text = "" Then
        MsgBox("Invalid Search")
    Else
        Try
            Dim newsql As String = "SELECT * FROM Animals WHERE AnimalName LIKE " & "'%" & txtname.Text & "%'"

            If txtopt.Text <> "" Then

                newsql &= " AND (AnimalName LIKE " & "'%" & txtopt.Text & "%'" & _
                          " OR LatinName LIKE " & "'%" & txtopt.Text & "%'" & _
                          " OR Location LIKE " & "'%" & txtopt.Text & "%'" & _
                          " OR AverageHeight LIKE " & "'%" & txtopt.Text & "%'" & _
                          " OR AverageWeight LIKE " & "'%" & txtopt.Text & "%'" & _
                          " OR DietaryNeeds LIKE " & "'%" & txtopt.Text & "%'" & _
                          " OR ConservationStatus LIKE " & "'%" & txtopt.Text & "%'" &    _
                          " OR AverageLifeSpan LIKE " & "'%" & txtopt.Text & "%'" & _
                          " OR BreedingSeason LIKE " & "'%" & txtopt.Text & "%'" & _
                          " OR AverageLength LIKE " & "'%" & txtopt.Text & "%')"
            End If

            Dim con As New OleDb.OleDbConnection
            Dim da As New OleDb.OleDbDataAdapter

            Dim dt As New DataTable("Animals")

            con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = h:\Animals.accdb"
            con.Open()

            da = New OleDb.OleDbDataAdapter(newsql, con)
            da.Fill(dt)

            Form2.Show()

            'show name in unbound text box
            Form2.nametxt.Text = dt.Rows(0).Item(1)
            Form2.latintxt.Text = dt.Rows(0).Item(2)
            Form2.locationtxt.Text = dt.Rows(0).Item(3)
            Form2.heighttxt.Text = dt.Rows(0).Item(4)
            Form2.weighttxt.Text = dt.Rows(0).Item(5)
            Form2.diettxt.Text = dt.Rows(0).Item(6)
            Form2.statustxt.Text = dt.Rows(0).Item(7)
            Form2.lifetxt.Text = dt.Rows(0).Item(8)
            Form2.breedtxt.Text = dt.Rows(0).Item(9)
            Form2.lengthtxt.Text = dt.Rows(0).Item(10)
            Form2.txtimage.Text = dt.Rows(0).Item(11)

        Catch
            MsgBox("Item Not Found")
            'con.close()
        End Try
    End If

End Sub

我没有对此进行测试，并且更详细地查看，这假设您的所有列都是字符列。

如果您混合使用整数、日期、字符等(您很可能拥有)，那么您可能需要更具体地搜索术语。您需要根据用户尝试的可选搜索将可选 SQL 分开。