Solved

Dynamically build query based on multiple combo-boxes

Posted on 2013-01-10
8
528 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

932 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

17 Experts available now in Live!

Get 1:1 Help Now