在MySQL&中构建类似查询的结构VB.Net [英] Structuring Like Query in mySQL & VB.Net

问题描述

我有ListView&多个过滤器作为checkboxList.一切正常，但Like子句无法正常运行.就像下面的

I have ListView & multiple filters as checkboxList. Everything working properly but Like clause not coming properly. It is like below

select * from products where  category Like '% Apparels, Bluetooth%'

但是应该是这样

select * from products where  category Like '% Apparels%' or category Like '%Bluetooth%'

如何更改代码以正确构造查询?

How do I change my code to structure my query properly?

Public Function buildWhereClause() As String
        Dim constr As String = ConfigurationManager.ConnectionStrings("conio").ConnectionString
        Dim query As String = "select * from products"
        Dim joiner As String = " "
    Dim condition As String = String.Empty
    Dim priceCondition As String = String.Empty

    For i = 0 To priceFilter.Items.Count - 1
        If priceFilter.Items(i).Selected Then
            Dim price As String = priceFilter.Items(i).ToString
            priceCondition = String.Concat(priceCondition, joiner, String.Format("'{0}'", price))
            If joiner = " " Then joiner = ", "
        End If
    Next

    Dim categoryCondition As String = String.Empty
    joiner = " "

    For i = 0 To categoryFilter.Items.Count - 1
        If categoryFilter.Items(i).Selected Then
            Dim category As String = categoryFilter.Items(i).ToString
            categoryCondition = String.Concat(categoryCondition, joiner, String.Format("{0}", category))
            If joiner = " " Then joiner = ", "
        End If
    Next

    Dim whereClause As String = String.Empty
    joiner = " where "
    If Not String.IsNullOrEmpty(priceCondition) Then
        whereClause = String.Concat(whereClause, joiner, String.Format(" price_range IN ({0})", priceCondition)) ' and sub_category IN ({0})", condition.Substring(0, condition.Length - 1))
        joiner = " and "
    End If

    If Not String.IsNullOrEmpty(categoryCondition) Then
        whereClause = String.Concat(whereClause, joiner, String.Format(" category Like '%{0}%'", categoryCondition))
        joiner = " and "
    End If

    'If Not String.IsNullOrEmpty(brandCondition) Then
    '    whereClause = String.Concat(whereClause, joiner, String.Format(" sub_category in ({0})", categoryCondition))
    '    joiner = " and "
    'End If

    Dim masterClause As String = String.Empty
    masterClause = (query & whereClause)
End Sub

推荐答案

因此，通过遍历categoryFilter.Items您将得到categoryCondition="Apparels, Bluetooth"，您可以在此处执行的操作是将,替换为%' or category Like '%，然后一切都会凉爽的.考虑以下内容:

So you are getting categoryCondition="Apparels, Bluetooth" by looping through categoryFilter.Items What you can do here is Replace that , with %' or category Like '% then every thing will be cool. consider the following:

If Not String.IsNullOrEmpty(categoryCondition) Then
    whereClause = String.Concat(whereClause, joiner, String.Format("category Like '%{0}%'", categoryCondition.Replace(",","%' or category Like '%"))       
End If

这篇关于在MySQL&中构建类似查询的结构VB.Net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！