Solved

Dynamically build query based on multiple combo-boxes

Posted on 2013-01-10
8
533 Views
Last Modified: 2013-01-11
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
Comment
Question by:fizzlefry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 38764833
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38765029
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
 
LVL 9

Expert Comment

by:sognoct
ID: 38767463
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:fizzlefry
ID: 38767694
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
 

Author Comment

by:fizzlefry
ID: 38767735
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
 
LVL 9

Accepted Solution

by:
sognoct earned 350 total points
ID: 38767792
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
 

Author Closing Comment

by:fizzlefry
ID: 38767839
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38767869
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question