Solved

Dynamically build query based on multiple combo-boxes

Posted on 2013-01-10
8
525 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Expert Comment

by:Ron M
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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now