Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

Dynamically build query based on multiple combo-boxes

I have a form used for data analysis.  This form has a date range selector, and a series of 4 combo boxes (Brand, Size, Flavor, Style).  These combo boxes, when used in conjunction with the date range, give me a very filtered datagridview.  What I'm trying to do is build a query based on those combo box selections.  The tricky part is the user may not want to filter by all results.  They may only want to filter by 1, or 2, or 3 options.  I can't find the logic to build the query, using those combo boxes, without having to code for every scenario.  It more comes from the issue of how to treat the box when the user doesn't select an option.  I would like the system to assume the user wants all options for that filter, if the value is not indicated.  Presently I have the ALL option working (that is, the user doesn't select ANY filter options, other than date).  That was easy.  Basically just ignore the combo boxes.  I do that by establishing all combo boxes are empty, and then Dim a SELECT query, based solely on date.

I.E.  User selects current week as date range and wants to filter by Size (20) and Brand (Beefy).  So the system needs to assume the other 2 boxes (Flavor and Style) are NOT filtered options and will return ALL Styles and Flavors matching the Size = 20 and Brand = Beefy.

Here's my code:

Private Sub LoadSpecific()

        Dim strBrand As String
        Dim strSize As String
        Dim strFlavor As String
        Dim strFilter As String

        If chkBrand.CheckState = CheckState.Checked Then
            strBrand = cboBrand.Text
        ElseIf chkBrand.CheckState = CheckState.Unchecked Then
            strBrand = "%"
        End If

        If chkSize.CheckState = CheckState.Checked Then
            strSize = cboSize.Text
        ElseIf chkSize.CheckState = CheckState.Unchecked Then
            strSize = "%"
        End If

        If chkFlavor.CheckState = CheckState.Checked Then
            strFlavor = cboFlavor.Text
        ElseIf chkFlavor.CheckState = CheckState.Unchecked Then
            strFlavor = "%"
        End If

        If chkFilter.CheckState = CheckState.Checked Then
            strFilter = cboFilter.Text
        ElseIf chkFilter.CheckState = CheckState.Unchecked Then
            strFilter = "%"
        End If

        Dim sql As String = "SELECT DateTime, RodId, TstFile, Weight, PressureDrop, PressureDrop_FE, FilterVent, Dia_Cir, Ovality FROM dbo.tblSODI WHERE ((DateTime Between '" & DTStart.Text & "' And '" & DTEnd.Text & "') AND (Brand = '" & strBrand & "') AND (Size = '" & strSize & "') AND (Flavor = '" & strFlavor & "') AND (Filter = '" & strFilter & "')) ORDER BY DateTime ASC"
        Dim sAdapter As New SqlDataAdapter(sCommand)
        Dim sBuilder As New SqlCommandBuilder(sAdapter)
        Dim sDs As New DataSet()
        Dim sTable As DataTable = sDs.Tables("SODI")

        If connection.sqlSodi.State = ConnectionState.Closed Then
            connection.sqlSodi.Open()
        End If

        sAdapter.Fill(sDs, "SODI")

        DataGridView1.DataSource = sDs.Tables("SODI")

        DataGridView1.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        DataGridView1.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        DataGridView1.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        DataGridView1.Columns(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        DataGridView1.Columns(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        DataGridView1.Columns(5).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        DataGridView1.Columns(6).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        DataGridView1.Columns(7).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        DataGridView1.Columns(8).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells

        sDs = Nothing

        If connection.sqlSodi.State = ConnectionState.Open Then
            connection.sqlSodi.Close()
        End If


    End Sub

The problem is the variable values for the filtered options are declared within the IF statement that establishes if that option is a selected filter option.  So my grid is coming back empty.  I've been searching about dynamically building queries and have come up empty.  Any help would be most appreciated!!!
0
fizzlefry
Asked:
fizzlefry
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Ron MalmsteadInformation Services ManagerCommented:
you might make use of an IIF() statement.

IIF(logical test,value if true, value if false)

...so for example..

IIF(strBrand <> "", " AND (Brand = " & "'" & strBrand & "')","")

....if the strBrand is blank, the entire "AND" portion you would have added to the string will be "" string empty.
0
 
CodeCruiserCommented:
They way I do it is using an OR inside my query. For example

Select * From mytable
Where (Brand=@Brand OR @Brand='')
AND (Style=@Style OR @Style='')


Then you can either pass an empty string (or ALL if you want) or pass a value and you would not need all that query building logic on client side.
0
 
sognoctCommented:
I do it in this way :

Dim sql As String = " SELECT DateTime, RodId, TstFile, Weight, PressureDrop, PressureDrop_FE, FilterVent, Dia_Cir, Ovality FROM dbo.tblSODI WHERE 1=1 " 
if cbDstart.Text <> ""  then sql &= " AND DateTime >= '" & DTStart.Text & "' "  
if DTEnd.Text <> "" then sql &= " AND DateTime <= '" & DTEnd.Text & "' " 
if strBrand.Text  <> "" then   sql &= " AND Brand = '" & strBrand & "' "
...etc ..

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
fizzlefryAuthor Commented:
xuserx2000, I've never used IIF statements.  I'm not sure how to write it for multiple circumstances.  I'm using the following, but forgive my ignorance on how to globally apply it:

IIf(strBrand <> "", " AND (Brand = " & "'" & strBrand & "')", "")

CodeCruiser, I get where you're going, I'm just not sure how to specify "ALL".  I don't want to pass an empty string for the unselected values.  I don't know how to specify all instead of empty string.

sognoct, using your concept (unless I misunderstood application) did not work when I did a search just by size = 100 over a range I know to have 100's in it.  My adapted code is as follows:

Dim strBrand As String = cboBrand.Text
        Dim strSize As String = cboSize.Text
        Dim strFlavor As String = cboFlavor.Text
        Dim strFilter As String = cboFilter.Text
        Dim sql As String = " SELECT DateTime, RodId, TstFile, Weight, PressureDrop, PressureDrop_FE, FilterVent, Dia_Cir, Ovality FROM dbo.tblSODI WHERE (DateTime Between '" & DTStart.Text & "' And '" & DTEnd.Text & "') "
        If cboBrand.Text <> "" Then sql &= " AND Brand = '" & strBrand & "' "
        If cboSize.Text <> "" Then sql &= " AND Brand = '" & strSize & "' "
        If cboFlavor.Text <> "" Then sql &= " AND Brand = '" & strFlavor & "' "
        If cboFilter.Text <> "" Then sql &= " AND Brand = '" & strFilter & "' "
0
 
fizzlefryAuthor Commented:
CodeCruiser,  I was able to find the "ALL' designation, but it's returning everything.  I'm filtering by only size = 100, and I'm getting 120's as well, using the following:

Dim sql As String = "SELECT DateTime, RodId, TstFile, Weight, PressureDrop, PressureDrop_FE, FilterVent, Dia_Cir, Ovality FROM dbo.tblSODI WHERE ((DateTime Between '" & DTStart.Text & "' And '" & DTEnd.Text & "') AND (Brand = '" & strBrand & "' OR Brand Like '%') AND (Size = '" & strSize & "' OR Size Like '%') AND (Flavor = '" & strFlavor & "' OR Flavor Like '%') AND (Filter = '" & strFilter & "' OR Filter Like '%')) ORDER BY DateTime ASC"
0
 
sognoctCommented:
maybe there is a typing error in the query that you wrote, it was intended this

 Dim sql As String = " SELECT DateTime, RodId, TstFile, Weight, PressureDrop, PressureDrop_FE, FilterVent, Dia_Cir, Ovality FROM dbo.tblSODI WHERE (DateTime Between '" & DTStart.Text & "' And '" & DTEnd.Text & "') "
        If cboBrand.Text <> "" Then sql &= " AND Brand = '" & strBrand & "' "
        If cboSize.Text <> "" Then sql &= " AND Size = '" & strSize  & "' "
        If cboFlavor.Text <> "" Then sql &= " AND Flavor = '" & strFlavor & "' "
        If cboFilter.Text <> "" Then sql &= " AND Filter = '" & strFilter & "' "

Open in new window

0
 
fizzlefryAuthor Commented:
sognoct, thank you very much for the help.  I was able to get it to work using the following adapted code:

Dim strBrand As String = cboBrand.Text
        Dim strSize As String = cboSize.Text
        Dim strFlavor As String = cboFlavor.Text
        Dim strFilter As String = cboFilter.Text

        Dim sql As String = " SELECT DateTime, RodId, TstFile, Weight, PressureDrop, PressureDrop_FE, FilterVent, Dia_Cir, Ovality FROM dbo.tblSODI WHERE (DateTime Between '" & DTStart.Text & "' And '" & DTEnd.Text & "') "
        If cboBrand.Text <> "" Then sql &= " AND Brand = '" & strBrand & "' "
        If cboSize.Text <> "" Then sql &= " AND Size = '" & strSize & "' "
        If cboFlavor.Text <> "" Then sql &= " AND Flavor = '" & strFlavor & "' "
        If cboFilter.Text <> "" Then sql &= " AND Filter = '" & strFilter & "' "

This is perfect!  Thank you again!
0
 
CodeCruiserCommented:
Good that you found a solution.

>CodeCruiser,  I was able to find the "ALL' designation, but it's returning everything.

That's because you were not using ( ) as suggested in my comment

Where (Brand=@Brand OR @Brand='All') AND (Style=@Style OR @Style='All')
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now