mysql - 在 ListView 中应用多个过滤器

Question

我有 listview 和 onPage 加载所有数据都显示在其中。为了过滤 ListView ，我有 4 种类型的 RedioButtonList(区域、服务、设施、付款)。现在假设我应用第一个过滤器，即区域，然后它工作正常，但之后我应用服务过滤器，而不是区域变为空白。它应该在选定的区域找到选定的服务。调试后我看到查询实际传递的内容如下

select * from table where areaName = '' and services = 'selected service'

但应该是这样的

select * from table where areaName = 'SelectedArea' and services = 'selected service'

我在每个过滤器 IndexChanged 上运行以下函数

Private Sub getResult()
    Try
        Dim citySelector As DropDownList = Page.Master.FindControl("locationSelector")
        Dim where As String = String.Empty
        Session("Data") = Nothing
        If areasList.SelectedValue <> "All" Then
            where = "address like '%" & areasList.SelectedValue & "%'"
        End If

        If servicesList.SelectedValue <> "All" Then
            If String.IsNullOrEmpty(where) Then
                where = "serviceID like '%" & servicesList.SelectedValue.ToString & "%'"
            Else
                where &= "and serviceID like '" & servicesList.SelectedValue.ToString & "%'"
            End If
        End If

        If facilitiesList.SelectedValue <> "All" Then
            If String.IsNullOrEmpty(where) Then
                where = "facilities like '%" & facilitiesList.SelectedValue & "%'"
            Else
                where &= "and facilities like '" & facilitiesList.SelectedValue & "%'"
            End If
        End If

        If paymentsList.SelectedValue <> "All" Then
            If String.IsNullOrEmpty(where) Then
                where = "payment like '%" & paymentsList.SelectedValue.ToString & "%'"
            Else
                where &= "and payment like '%" & paymentsList.SelectedValue.ToString & "%'"
            End If
        End If

        Dim query As String = "SELECT hospitalID, name, address, thumbnail, knownFor, mondayFrom, mondayTo, consultancyFees FROM hospitals where city = '" + citySelector.SelectedItem.ToString + "' and status = 'active'"
        If Not String.IsNullOrEmpty(where) Then
            query &= " and " & where
        End If

        Dim cmd As New MySqlCommand(query, con)
        Dim da As New MySqlDataAdapter(cmd)
        Dim table As New DataTable
        da.Fill(table)
        Session("Data") = table
        hospitals.DataSource = table
        hospitals.DataBind()
    Catch ex As Exception
        Response.Write(ex)
    End Try
End Sub

我哪里做错了，请帮忙。

Answer 1

每次您更改下拉框之一的索引时，您的 where 子句都会被重置。您需要创建一个对所有下拉框通用的函数，该函数将循环遍历每个下拉框的值并创建一个包含所有变量的 where 。

dim where as string;        
Private Sub changeWherestatement(sender As Object, e As EventArgs) Handles    ComboBox1.SelectedIndexChanged, ComboBox2.SelectedIndexChanged
where=string.empty
 If areasList.SelectedValue <> "All" Then
        where = "address like '%" & areasList.SelectedValue & "%'"
    End If
If servicesList.SelectedValue <> "All" Then
        If String.IsNullOrEmpty(where) Then
            where += " and serviceID like '%" & servicesList.SelectedValue.ToString & "%'"
        Else
            where += "and serviceID like '" & servicesList.SelectedValue.ToString & "%'"
        End If
    End If
Etc

End Sub