We help IT Professionals succeed at work.

Populate combo box with filtered data (SQL Tabls) before main query runs

Hi Experts.

I have a windows form that pulls data into a gridview based on a few controls.

There are from and to dates and a CheckListBox that determin which Shafts should be included in the gridview, the last filter is a combobox "CbxTeamLeader". what I need to do is once the Shafts Get selected in the checkbox ONLY the teams specific to the shafts must populate the combo box then based on that selection, the gridview gets updated

The Original Code looks like this (Thank you Ark for making this possible so far)
Dim dt As New DataTable
Dim strSQL As String = "SELECT * FROM Performance WHERE [Date] BETWEEN @DateFrom AND @DateTo"
Dim lstFilters As New List(Of String)
For Each s As String In ClbShaftSelect.CheckedItems
     lstFilters.Add(String.Format("(Shaft='{0}')", s))
Next
If lstFilters.Count > 0 Then
    strSQL += " AND (" & String.Join(" OR ", lstFilters.ToArray) & ")"
End If
If CbxTeamLeader.SelectedIndex > 0 Then 'or
'If  CbxTeamLeader.Text<>"" Then
    strSQL += " AND (TeamLeader='" & CbxTeamLeader.Text & "')"
End If

       Using conn As New SqlClient.SqlConnection(connString)
            conn.Open()
            Using da As New SqlClient.SqlDataAdapter(strSQL, conn)
                da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
                da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
                da.Fill(dt)
            End Using
            conn.Close()
        End Using

Return dt

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Use another similar function to get a list of teams based on selection in shafts (or is that shifts?)

Author

Commented:
Hi CodeCruiser
Wouldn't even know where to begin. Finding it very hard to find great help outside of EE
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Something like

Dim dt As New DataTable
Dim strSQL As String = "SELECT * FROM TeamLeaders"
Dim lstFilters As New List(Of String)
For Each s As String In ClbShaftSelect.CheckedItems
     lstFilters.Add(s)
Next
If lstFilters.Count > 0 Then
    strSQL += "  WHERE Shaft In (" & String.Join(", ", lstFilters.ToArray) & ")"
End If
       Using conn As New SqlClient.SqlConnection(connString)
            conn.Open()
            Using da As New SqlClient.SqlDataAdapter(strSQL, conn)
                da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
                da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
                da.Fill(dt)
            End Using
            conn.Close()
        End Using

Return dt

Open in new window

Author

Commented:
Hi CodeCruiser

If I ad
Select * From TeamLeaders
It will stop the query on the Table named performance.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I don't understand what you said.

Author

Commented:
Sorry
The original code has this line.

Dim strSQL As String = "SELECT * FROM Performance WHERE [Date] BETWEEN @DateFrom AND @DateTo"

Do I delete this line to add yours?
Dim strSQL As String = "SELECT * FROM TeamLeaders"

Wont this negate the date filters?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>what I need to do is once the Shafts Get selected in the checkbox ONLY the teams specific to the shafts must populate the combo box

So the date filter and shaft filter will apply to teamleader list as well? If so, do you not need to just change the table name as the rest of the filter string is same?
Ark
CERTIFIED EXPERT

Commented:
Hi
I suggest populate combo AFTER main filter:
'Assuming "TeamLeader" is a column name
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Init filter with dates and checked listbox
        dgv.datasource=Nothing
        Dim dt As DataTable=FilterSQL(...)
         'Select distinct leaders
        Dim dtLeaders As DataTable = New DataView(dt).ToTable(True, "TeamLeader")
        CbxTeamLeader.Items.Clear()
'Populate combo, add first item with no filter
        CbxTeamLeader.Items.Add("All Teams")
        For Each r As DataRow In dtLeaders.Rows
            CbxTeamLeader.Items.Add(r("TeamLeader"))
        Next
        dgv.datasource=dt 'Fill datagridview
    End Sub
 
'Filter datagridview data according to combobox selection
    Private Sub CbxTeamLeader_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CbxTeamLeader.SelectedIndexChanged
        If dgv.datasource Is Nothing Then Return
        Dim sFilter As String = String.Empty
        If CbxTeamLeader.SelectedIndex > 0 Then
            sFilter = "TeamLeader='" & CbxTeamLeader.Text & "'"
        End If
        dgv.datasource.DefaultView.RowFilter = sFilter
    End Sub
Private Function FilterSQL(ByVal connString As String, _
                               ByVal dateFrom As Date, _
                               ByVal dateTo As Date) As DataTable
Dim strSQL As String = "SELECT * FROM YorTableName WHERE [Date] BETWEEN @DateFrom AND @DateTo"
Dim lstFilters As New List(Of String)
For Each s As String In ClbShaftSelect.CheckedItems
     lstFilters.Add(String.Format("(Shaft='{0}')", s))
Next
If lstFilters.Count > 0 Then
    strSQL += " AND (" & String.Join(" OR ", lstFilters.ToArray) & ")"
End If  

       Using conn As New SqlClient.SqlConnection(connString)
            conn.Open()
            Using da As New SqlClient.SqlDataAdapter(strSQL, conn)
                da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
                da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
                da.Fill(dt)
            End Using
            conn.Close()
        End Using

    Return dt 
End Function 

Open in new window

Author

Commented:
Hi Ark

Am I correct in adding this to your code?
 Dim dt As New DataTable
 Dim dgv As DataGridView

Then I get an error

Error      5      Expected beginning '<' for an XML tag.
On this line

Dim dt As DataTable=FilterSQL(...)

How do I resolve this?
Ark
CERTIFIED EXPERT

Commented:
dgv is your datagridview:
DataGridViewPerformance.DataSource = Nothing 'Clear old datasource
Dim dt As DataTable=FilterSQL(YourConnStringHere, DateTimePickerFrom.Value, DateTimePickerTo.Value)
'........ rest of code Button_Click(...)
DataGridViewPerformance.DataSource=dt
Ark
CERTIFIED EXPERT

Commented:
A brief explanation:
1. FilterSQL function apply filter to Date and 'Shaft' columns. Then
2. Dim dtLeaders As DataTable = New DataView(dt).ToTable(True, "TeamLeader")
Populates dtLeader table with distinct values from dt (datatable, filtered at #1), then
3. Fill combobox using dtLeader datatable
4. Set DataGridView datasource to dt - it'll show all dt records
5. At ComboBox_SelectedIndexChanged event it filters entire DataGridView (without call to SQL server) according to selection

Author

Commented:
Brilliant.
OK I select from and to then a few shafts hit view button
then
I get my data and the Team Leader column populates the combo perfectly
Then I select a team and hit View again
but the team filter does not change the data?
Ark
CERTIFIED EXPERT

Commented:
No need hit View button second time - when you choose leader from combobox? datagridview will filter its records according selection. To view all records choose first combo entry (All Teams)
Public Class Form1
    Private dtFiltered As New DataTable

    Private Sub BtnView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnView.Click
        DataGridViewPerformance.DataSource = Nothing
        dtFiltered = FilterSQL("YourConnectionStringHere", dtpFrom.Value, dtpTo.Value)
        Dim dtLeaders As DataTable = dtFiltered.DefaultView.ToTable(True, "TeamLeader")
        CbxTeamLeader.Items.Clear()
        CbxTeamLeader.Items.Add("All Teams")
        For Each dr As DataRow In dtLeaders.Rows
            CbxTeamLeader.Items.Add(dr("TeamLeader"))
        Next
        CbxTeamLeader.SelectedIndex = 0
        DataGridViewPerformance.DataSource = dtFiltered
    End Sub

    Private Function FilterSQL(ByVal connString As String, _
                               ByVal dateFrom As Date, _
                               ByVal dateTo As Date) As DataTable
        Dim strSQL As String = "SELECT * FROM YorTableName WHERE [Date] BETWEEN @DateFrom AND @DateTo"
        Dim lstFilters As New List(Of String)
        For Each s As String In ClbShaftSelect.CheckedItems
            lstFilters.Add(String.Format("(Shaft='{0}')", s))
        Next
        If lstFilters.Count > 0 Then
            strSQL += " AND (" & String.Join(" OR ", lstFilters.ToArray) & ")"
        End If
        Dim dt As New DataTable
        Using conn As New SqlClient.SqlConnection(connString)
            conn.Open()
            Using da As New SqlClient.SqlDataAdapter(strSQL, conn)
                da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
                da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
                da.Fill(dt)
            End Using
            conn.Close()
        End Using

        Return dt
    End Function

    Private Sub CbxTeamLeader_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CbxTeamLeader.SelectedIndexChanged
        If DataGridViewPerformance.DataSource Is Nothing Then Return
        If CbxTeamLeader.SelectedIndex = -1 Then Return
        Dim strFilter As String = String.Empty
        If CbxTeamLeader.SelectedIndex > 0 Then
            strFilter = "TeamLeader='" & CbxTeamLeader.Text & "'"
        End If
        DataGridViewPerformance.DataSource = dtFiltered.Select(strFilter).CopyToDataTable
    End Sub
End Class

Open in new window

Replace "YourConnectionString" with real connection string, "YourTableName" with table name and "TeamLeader","[Date]" and "Shaft" with real column names

Author

Commented:
dtFiltered need to be declared.
Would this be
Dim dtFiltered As String?
Ark
CERTIFIED EXPERT

Commented:
Public Class Form1
    Private dtFiltered As New DataTable

Author

Commented:
It doesnt like this
dtFiltered = FilterSQL("Data Source=196.220.43.247,1444;Initial Catalog=psql;User ID=MyName;Password=MyPass", DtpStart.Value, DtpEnd.Value)
Ark
CERTIFIED EXPERT

Commented:
Is there any error? Note:
Private dtFiltered As New DataTable
should be at form level, not inside sub/function

Author

Commented:
Sorry

Error      5      Argument not specified for parameter 'dateFrom' of 'Private Function FilterSQL(connString As String, tableName As String, fieldName As String, dateFrom As Date, dateTo As Date) As System.Data.DataTable'.
Ark
CERTIFIED EXPERT

Commented:
Replace FilterSQL function with one from my last post - I've hardcoded Table name as well as fieldName inside function, so now function accepts 3 parameters only

Author

Commented:
OK
Take a look
We must be so close!
Error
Ark
CERTIFIED EXPERT

Commented:
COPY_PASTE_THIS_FUNCTION_INSTEAD_OF_OLD_ONE
  Private Function FilterSQL(ByVal connString As String, _
                               ByVal dateFrom As Date, _
                               ByVal dateTo As Date) As DataTable
        Dim strSQL As String = "SELECT * FROM YorTableName WHERE [Date] BETWEEN @DateFrom AND @DateTo"
        Dim lstFilters As New List(Of String)
        For Each s As String In ClbShaftSelect.CheckedItems
            lstFilters.Add(String.Format("(Shaft='{0}')", s))
        Next
        If lstFilters.Count > 0 Then
            strSQL += " AND (" & String.Join(" OR ", lstFilters.ToArray) & ")"
        End If
        Dim dt As New DataTable
        Using conn As New SqlClient.SqlConnection(connString)
            conn.Open()
            Using da As New SqlClient.SqlDataAdapter(strSQL, conn)
                da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
                da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
                da.Fill(dt)
            End Using
            conn.Close()
        End Using

        Return dt
    End Function

Open in new window

Author

Commented:
Yes I have done that
Ark
CERTIFIED EXPERT

Commented:
Nope!
Image you sent describes the error: Argument not specified.... and then function signature u'r using: FilterSQL(connString As string, tableName As String, FieldName As String....
New function doesn't contain tablename and fieldName - they are hardcoded inside function body!
Ark
CERTIFIED EXPERT

Commented:
PS. Open your form code, clear ALL (or save somewhere in notepad, if you like), then copy/paste my code from http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_27830939.html#a38303800
Replace "YourConnectionString" with real connection string, "YourTableName" with table name and "TeamLeader","[Date]" and "Shaft" with real column names and run.

Author

Commented:
Cool only have 2 errors

Handles clause requires a WithEvents variable defined in the containing type or one of its base types.      
 and

Error      7      'DtpTo' is not declared. It may be inaccessible due to its protection level.
Ark
CERTIFIED EXPERT

Commented:
Replace control's names with your form controls
dtpTo is second datetimepicker, probably dtpEnd on your form
Check which Handles. is underlined and replace with your control name
Form1.zip

Author

Commented:
Fixed the errors
on running the code I got this

Error2

Author

Commented:
oops fixed that

The combo filter still does not do what it is supposed to
Could this be me not doing the string right?

db = psql
Table = Performance
Date = Date
Team Leader = Team Leader
Shaft = Shaft

Replace "YourConnectionString" with real connection string, "YourTableName" with table name and "TeamLeader","[Date]" and "Shaft" with real column names and run.

Connection = Data Source=196.220.43.247,1444;Initial Catalog=psql;User ID=MYNAME;Password=MY{PASS;
Ark
CERTIFIED EXPERT

Commented:
What do you expect?
My code works as follows:
1. Select date period
2. (Optionaly) select 'Shaft'
3. Press View button - datagridview populates records for all teams
4. Choose team leader from combo - datagridview filter records and leave only those where team leader=selected in combo

Author

Commented:
That's what I want but 4 does not happen
When I select a team nothing changes.

I have done everything you said.

Example
Ark
CERTIFIED EXPERT

Commented:
Can you send your form (all 3 files: *.resx, *.designer.vb and *.vb)?

Author

Commented:
Ark
CERTIFIED EXPERT

Commented:
Just to experiment:
Rename your datagridview to any other name
Make it small
Add fresh clear datagridview and name it DataGridViewPerformance (don't set ANY properties to new DataGridView at design time)

Author

Commented:
The new Gridview fills up but the combo does not effect it
CERTIFIED EXPERT
Commented:
Hmm, it's very strange - I've tested same code on my database (though it's MSSQL DB) - it works as expexted and combobox selected item affects datagridview. Try to place FiledName  into square brackets:
was: strFilter = "TeamLeader='" & CbxTeamLeader.Text & "'"
replace with:
strFilter = "[Team Leader]='" & CbxTeamLeader.Text & "'"
=====
What data type is in Team Leader column in your data base? probably it's fixed length string with spaces (or null-chars) at the end? If so, try
strFilter = "[Team Leader] LIKE '" & trim(CbxTeamLeader.Text) & "%'"
====
You can test it manually - add command button and write in Button_Click event
dim dt as DataTable = dtFiltered.Select( "[Team Leader]='moab tigers'"
).CopyToDataTable
DataGridViewPerformance.DataSource=dt

Author

Commented:
Thank you ark
You really went the extra mile for me here.
Nice work.

strFilter = "[Team Leader]='" & CbxTeamLeader.Text & "'"

did it in the end
Ark
CERTIFIED EXPERT

Commented:
Glad I could help.
It's a good practice to surround field names with square brackets always, but I'm too lazy to follow it :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.