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

GrahamSAAsked:
Who is Participating?
 
ArkCommented:
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
0
 
CodeCruiserCommented:
Use another similar function to get a list of teams based on selection in shafts (or is that shifts?)
0
 
GrahamSAAuthor Commented:
Hi CodeCruiser
Wouldn't even know where to begin. Finding it very hard to find great help outside of EE
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CodeCruiserCommented:
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

0
 
GrahamSAAuthor Commented:
Hi CodeCruiser

If I ad
Select * From TeamLeaders
It will stop the query on the Table named performance.
0
 
CodeCruiserCommented:
I don't understand what you said.
0
 
GrahamSAAuthor 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?
0
 
CodeCruiserCommented:
>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?
0
 
ArkCommented:
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

0
 
GrahamSAAuthor 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?
0
 
ArkCommented:
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
0
 
ArkCommented:
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
0
 
GrahamSAAuthor 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?
0
 
ArkCommented:
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
0
 
GrahamSAAuthor Commented:
dtFiltered need to be declared.
Would this be
Dim dtFiltered As String?
0
 
ArkCommented:
Public Class Form1
    Private dtFiltered As New DataTable
0
 
GrahamSAAuthor 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)
0
 
ArkCommented:
Is there any error? Note:
Private dtFiltered As New DataTable
should be at form level, not inside sub/function
0
 
GrahamSAAuthor 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'.
0
 
ArkCommented:
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
0
 
GrahamSAAuthor Commented:
OK
Take a look
We must be so close!
Error
0
 
ArkCommented:
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

0
 
GrahamSAAuthor Commented:
Yes I have done that
0
 
ArkCommented:
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!
0
 
ArkCommented:
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.
0
 
GrahamSAAuthor 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.
0
 
ArkCommented:
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
0
 
GrahamSAAuthor Commented:
Fixed the errors
on running the code I got this

Error2
0
 
GrahamSAAuthor 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;
0
 
ArkCommented:
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
0
 
GrahamSAAuthor 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
0
 
ArkCommented:
Can you send your form (all 3 files: *.resx, *.designer.vb and *.vb)?
0
 
GrahamSAAuthor Commented:
0
 
ArkCommented:
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)
0
 
GrahamSAAuthor Commented:
The new Gridview fills up but the combo does not effect it
0
 
GrahamSAAuthor 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
0
 
ArkCommented:
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.