GrahamSA
asked on
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)
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
Use another similar function to get a list of teams based on selection in shafts (or is that shifts?)
ASKER
Hi CodeCruiser
Wouldn't even know where to begin. Finding it very hard to find great help outside of EE
Wouldn't even know where to begin. Finding it very hard to find great help outside of EE
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
ASKER
Hi CodeCruiser
If I ad
Select * From TeamLeaders
It will stop the query on the Table named performance.
If I ad
Select * From TeamLeaders
It will stop the query on the Table named performance.
I don't understand what you said.
ASKER
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?
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?
>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?
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?
Hi
I suggest populate combo AFTER main filter:
'Assuming "TeamLeader" is a column name
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
ASKER
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?
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?
dgv is your datagridview:
DataGridViewPerformance.Da taSource = Nothing 'Clear old datasource
Dim dt As DataTable=FilterSQL(YourCo nnStringHe re, DateTimePickerFrom.Value, DateTimePickerTo.Value)
'........ rest of code Button_Click(...)
DataGridViewPerformance.Da taSource=d t
DataGridViewPerformance.Da
Dim dt As DataTable=FilterSQL(YourCo
'........ rest of code Button_Click(...)
DataGridViewPerformance.Da
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_SelectedIndexChan ged event it filters entire DataGridView (without call to SQL server) according to selection
1. FilterSQL function apply filter to Date and 'Shaft' columns. Then
2. Dim dtLeaders As DataTable = New DataView(dt).ToTable(True,
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_SelectedIndexChan
ASKER
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?
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?
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
Replace "YourConnectionString" with real connection string, "YourTableName" with table name and "TeamLeader","[Date]" and "Shaft" with real column names
ASKER
dtFiltered need to be declared.
Would this be
Dim dtFiltered As String?
Would this be
Dim dtFiltered As String?
Public Class Form1
Private dtFiltered As New DataTable
ASKER
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)
dtFiltered = FilterSQL("Data Source=196.220.43.247,1444
Is there any error? Note:
Private dtFiltered As New DataTable
should be at form level, not inside sub/function
Private dtFiltered As New DataTable
should be at form level, not inside sub/function
ASKER
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'.
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'.
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
COPY_PASTE_THIS_FUNCTION_I NSTEAD_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
ASKER
Yes I have done that
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!
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!
PS. Open your form code, clear ALL (or save somewhere in notepad, if you like), then copy/paste my code from https://www.experts-exchange.com/questions/27830939/Populate-combo-box-with-filtered-data-SQL-Tabls-before-main-query-runs.html?anchorAnswerId=38303800#a38303800
Replace "YourConnectionString" with real connection string, "YourTableName" with table name and "TeamLeader","[Date]" and "Shaft" with real column names and run.
Replace "YourConnectionString" with real connection string, "YourTableName" with table name and "TeamLeader","[Date]" and "Shaft" with real column names and run.
ASKER
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.
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.
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
dtpTo is second datetimepicker, probably dtpEnd on your form
Check which Handles. is underlined and replace with your control name
Form1.zip
ASKER
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
Connection = Data Source=196.220.43.247,1444 ;Initial Catalog=psql;User ID=MYNAME;Password=MY{PASS ;
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
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
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
ASKER
Can you send your form (all 3 files: *.resx, *.designer.vb and *.vb)?
ASKER
Here you go
FrmPerformanceView.zip
FrmPerformanceView.zip
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)
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)
ASKER
The new Gridview fills up but the combo does not effect it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you ark
You really went the extra mile for me here.
Nice work.
strFilter = "[Team Leader]='" & CbxTeamLeader.Text & "'"
did it in the end
You really went the extra mile for me here.
Nice work.
strFilter = "[Team Leader]='" & CbxTeamLeader.Text & "'"
did it in the end
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 :)
It's a good practice to surround field names with square brackets always, but I'm too lazy to follow it :)