Filtering a dataset in one combobox based on selection of another combobox contained within a datagridview
Hi experts,
I'm encountering some difficulties with combobox record filtering in a datagridview for a windows forms project. My table structures are below and will help illustrate what I need in terms of functionality (code used is also below). One stored proc returns tbl_stations and tbl_reasons (for populating the comboboxes), another returns tbl_Details for populating the details and setting the selectedvalue property of the contained comboboxes and populating the values of the other cells.
What I need to be able to do is to filter the colDTEReason, based on the selection of the colDTEStation column. What has been happening is that the columns in every row change every time the dataset for the column is filtered. I need selections to be remembered from row to row and have the program only apply filtering to the full recordset when the specific row is being edited. Is there a way to accomplish this??
Many thanks in advance,
Bob
tbl_Stations:
stationid stationdesc
1 stn 10
2 stn 20
3 stn 30
4 stn 40
5 stn 50
6 stn 60
tbl_Reasons:
ReasonID StationID ReasonDesc
2 2 Reason1
3 2 Reason2
4 2 Reason3
5 3 Reason4
6 3 Reason5
7 3 Reason6
tbl_Details:
DetailID Station Reason Duration Detail
1 2 3 22 BLah
2 3 4 1 Blah2
Public Class Form1
Private iRowDetailID As Integer
#Region "Datasets, DataViews, and DataTable Declares for the main form"
Dim dsSAR As New DataSet
#End Region
#Region "Columns for Downtime Tracking datagridview dgDTEntry"
Private colDTEDetailID As New DataGridViewTextBoxColumn
Private colDTELine As New DataGridViewComboBoxColumn
Private colDTEStation As New DataGridViewComboBoxColumn
Private colDTEReason As New DataGridViewComboBoxColumn
Private colDTESec As New DataGridViewTextBoxColumn(
Private colDTEDetail As New DataGridViewTextBoxColumn(
Private dvSubLine As New DataView
Private dvStation As New DataView
Private dvReason As New DataView
#End Region
Private Sub CreateColumns()
'+------------------------
'| Purpose: This function simply creates all necessary columns and populates the |
'| comboboxes with values used to make up the detail records. |
'+------------------------
Dim cDTTData As New cDTT.cData
Dim dsTemp As New DataSet
Try
If Not cDTTData.PopulateStnAndRea
Throw cDTTData.LastException
End If
dgDTEntry.Columns.Clear()
' For this column, combo box contents are retrieved from the database
' Size the column width so it is wide enough to display the header
With colDTEStation
dvStation.Table = dsTemp.Tables(0)
.DataSource = dvStation 'dsLSAR.Tables(1)
.ValueMember = "StationID"
.DisplayMember = "StationDesc"
.HeaderText = "Station"
.Name = "Station"
.ValueType = GetType(System.Int32)
.ReadOnly = False
.Width = 150
dgDTEntry.Columns.Add(colD
End With
dgDTEntry.Refresh()
' For this column, combo box contents are retrieved from the database
' Size the column width so it is wide enough to display the header
With colDTEReason
dvReason.Table = dsTemp.Tables(1)
.DataSource = dvReason 'dsLSAR.Tables(2)
.ValueMember = "ReasonID"
.DisplayMember = "ReasonDesc"
.HeaderText = "Reason"
.Name = "Reason"
.ValueType = GetType(System.Int32)
.ReadOnly = False
.Width = 150
dgDTEntry.Columns.Add(colD
End With
dgDTEntry.Refresh()
Catch ex As Exception
MsgBox(ex.Message.ToString
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Call CreateColumns()
Call GetDetails()
End Sub
Private Function GetDetails() As Boolean
Dim n As Integer, o As Integer
Dim cDTTData As New cDTT.cData
Try
dgDTEntry.DataSource = Nothing
If Not cDTTData.GetDetails(dsSAR)
Throw cDTTData.LastException
End If
With dsSAR
Dim dt As DataTable = Nothing
dt = .Tables(0)
If .Tables(0).Rows.Count > 0 Then
dgDTEntry.AutoGenerateColu
dgDTEntry.DataSource = dt
For n = 0 To .Tables(0).Rows.Count - 1
dgDTEntry.Item(0, n).Value =
CInt(.Tables(0).Rows(n).It
iRowDetailID = dgDTEntry.CurrentRow.Cells
dgDTEntry.Item(1, n).Value =
CInt(.Tables(0).Rows(n).It
dgDTEntry.Item(2, n).Value =
CInt(.Tables(0).Rows(n).It
dgDTEntry.Item(3, n).Value =
CInt(.Tables(0).Rows(n).It
dgDTEntry.Item(4, n).Value = .Tables(0).Rows(n).Item("D
Next
End If
End With
Return True
Catch ex As Exception
MsgBox(ex.Message.ToString
Return False
Finally
cDTTData = Nothing
End Try
End Function
Private Sub dgDTEntry_EditingControlSh
Handles dgDTEntry.EditingControlSh
'+------------------------
'| Purpose: For handling the selectedindexchanged property in our virtual combobox. |
'+------------------------
Dim cbo As ComboBox = CType(e.Control, ComboBox)
If (cbo IsNot Nothing) Then
RemoveHandler cbo.SelectedIndexChanged, New EventHandler(AddressOf
cboVirtualCboFordgDTEntryG
AddHandler cbo.SelectedIndexChanged, New EventHandler(AddressOf
cboVirtualCboFordgDTEntryG
End If
End Sub
Private Sub cboVirtualCboFordgDTEntryG
ByVal sender As Object, ByVal e As EventArgs)
'+------------------------
'| Purpose: To handle the SelectedIndexChanged event for all drop-down |
'| comboboxes in the dgDTEEntry datagridview.|
'+------------------------
Dim comboBox1 As ComboBox = CType(sender, ComboBox)
Dim iDGVCol As Integer
Debug.WriteLine(dgDTEntry.
iDGVCol = GetSelectedDatagridviewCol
dvReason.RowFilter = ""
Dim dr2 As DataRow = Nothing
Select Case iDGVCol
Case 1
dvReason.RowFilter = "StationID = " & dvStation.Table.Rows(colDT
colDTEReason.DataSource = dvReason
End Select
End Sub
Private Function GetSelectedDatagridviewCol
GetSelectedDatagridviewCol
End Function
End Class
I'm encountering some difficulties with combobox record filtering in a datagridview for a windows forms project. My table structures are below and will help illustrate what I need in terms of functionality (code used is also below). One stored proc returns tbl_stations and tbl_reasons (for populating the comboboxes), another returns tbl_Details for populating the details and setting the selectedvalue property of the contained comboboxes and populating the values of the other cells.
What I need to be able to do is to filter the colDTEReason, based on the selection of the colDTEStation column. What has been happening is that the columns in every row change every time the dataset for the column is filtered. I need selections to be remembered from row to row and have the program only apply filtering to the full recordset when the specific row is being edited. Is there a way to accomplish this??
Many thanks in advance,
Bob
tbl_Stations:
stationid stationdesc
1 stn 10
2 stn 20
3 stn 30
4 stn 40
5 stn 50
6 stn 60
tbl_Reasons:
ReasonID StationID ReasonDesc
2 2 Reason1
3 2 Reason2
4 2 Reason3
5 3 Reason4
6 3 Reason5
7 3 Reason6
tbl_Details:
DetailID Station Reason Duration Detail
1 2 3 22 BLah
2 3 4 1 Blah2
Public Class Form1
Private iRowDetailID As Integer
#Region "Datasets, DataViews, and DataTable Declares for the main form"
Dim dsSAR As New DataSet
#End Region
#Region "Columns for Downtime Tracking datagridview dgDTEntry"
Private colDTEDetailID As New DataGridViewTextBoxColumn
Private colDTELine As New DataGridViewComboBoxColumn
Private colDTEStation As New DataGridViewComboBoxColumn
Private colDTEReason As New DataGridViewComboBoxColumn
Private colDTESec As New DataGridViewTextBoxColumn(
Private colDTEDetail As New DataGridViewTextBoxColumn(
Private dvSubLine As New DataView
Private dvStation As New DataView
Private dvReason As New DataView
#End Region
Private Sub CreateColumns()
'+------------------------
'| Purpose: This function simply creates all necessary columns and populates the |
'| comboboxes with values used to make up the detail records. |
'+------------------------
Dim cDTTData As New cDTT.cData
Dim dsTemp As New DataSet
Try
If Not cDTTData.PopulateStnAndRea
Throw cDTTData.LastException
End If
dgDTEntry.Columns.Clear()
' For this column, combo box contents are retrieved from the database
' Size the column width so it is wide enough to display the header
With colDTEStation
dvStation.Table = dsTemp.Tables(0)
.DataSource = dvStation 'dsLSAR.Tables(1)
.ValueMember = "StationID"
.DisplayMember = "StationDesc"
.HeaderText = "Station"
.Name = "Station"
.ValueType = GetType(System.Int32)
.ReadOnly = False
.Width = 150
dgDTEntry.Columns.Add(colD
End With
dgDTEntry.Refresh()
' For this column, combo box contents are retrieved from the database
' Size the column width so it is wide enough to display the header
With colDTEReason
dvReason.Table = dsTemp.Tables(1)
.DataSource = dvReason 'dsLSAR.Tables(2)
.ValueMember = "ReasonID"
.DisplayMember = "ReasonDesc"
.HeaderText = "Reason"
.Name = "Reason"
.ValueType = GetType(System.Int32)
.ReadOnly = False
.Width = 150
dgDTEntry.Columns.Add(colD
End With
dgDTEntry.Refresh()
Catch ex As Exception
MsgBox(ex.Message.ToString
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Call CreateColumns()
Call GetDetails()
End Sub
Private Function GetDetails() As Boolean
Dim n As Integer, o As Integer
Dim cDTTData As New cDTT.cData
Try
dgDTEntry.DataSource = Nothing
If Not cDTTData.GetDetails(dsSAR)
Throw cDTTData.LastException
End If
With dsSAR
Dim dt As DataTable = Nothing
dt = .Tables(0)
If .Tables(0).Rows.Count > 0 Then
dgDTEntry.AutoGenerateColu
dgDTEntry.DataSource = dt
For n = 0 To .Tables(0).Rows.Count - 1
dgDTEntry.Item(0, n).Value =
CInt(.Tables(0).Rows(n).It
iRowDetailID = dgDTEntry.CurrentRow.Cells
dgDTEntry.Item(1, n).Value =
CInt(.Tables(0).Rows(n).It
dgDTEntry.Item(2, n).Value =
CInt(.Tables(0).Rows(n).It
dgDTEntry.Item(3, n).Value =
CInt(.Tables(0).Rows(n).It
dgDTEntry.Item(4, n).Value = .Tables(0).Rows(n).Item("D
Next
End If
End With
Return True
Catch ex As Exception
MsgBox(ex.Message.ToString
Return False
Finally
cDTTData = Nothing
End Try
End Function
Private Sub dgDTEntry_EditingControlSh
Handles dgDTEntry.EditingControlSh
'+------------------------
'| Purpose: For handling the selectedindexchanged property in our virtual combobox. |
'+------------------------
Dim cbo As ComboBox = CType(e.Control, ComboBox)
If (cbo IsNot Nothing) Then
RemoveHandler cbo.SelectedIndexChanged, New EventHandler(AddressOf
cboVirtualCboFordgDTEntryG
AddHandler cbo.SelectedIndexChanged, New EventHandler(AddressOf
cboVirtualCboFordgDTEntryG
End If
End Sub
Private Sub cboVirtualCboFordgDTEntryG
ByVal sender As Object, ByVal e As EventArgs)
'+------------------------
'| Purpose: To handle the SelectedIndexChanged event for all drop-down |
'| comboboxes in the dgDTEEntry datagridview.|
'+------------------------
Dim comboBox1 As ComboBox = CType(sender, ComboBox)
Dim iDGVCol As Integer
Debug.WriteLine(dgDTEntry.
iDGVCol = GetSelectedDatagridviewCol
dvReason.RowFilter = ""
Dim dr2 As DataRow = Nothing
Select Case iDGVCol
Case 1
dvReason.RowFilter = "StationID = " & dvStation.Table.Rows(colDT
colDTEReason.DataSource = dvReason
End Select
End Sub
Private Function GetSelectedDatagridviewCol
GetSelectedDatagridviewCol
End Function
End Class
when u change the rowfilter in one combobox u r changing the global dataview which is affecting all comboboxed....use a temp new dataview for the editing control
ASKER
If I use a temp dataview, the dataview goes out of scope the second it exits out of the cboVirtualCboFordgDTEntryG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, I have it sort of working. It seems to filter only sometimes, showing all the values seemingly ramdomly! Some lines will filter whilst some won't. It's more of a problem with any new rows added and the very first row. This problem of only sometimes filtering went away when I set my temporary dataview equal to the global one. That however would proceed to change all the other reason values that weren't of the one that I just selected in the current row (like before). Do you have any working examples that I could look at? The code that I modified is below.
Private Sub cboVirtualCboFordgDTEntryG
ByVal sender As Object, ByVal e As EventArgs)
'+------------------------
'| Purpose: To handle the SelectedIndexChanged event for all drop-down |
'| comboboxes in the dgDTEEntry datagridview. |
'+------------------------
Dim comboBox1 As ComboBox = CType(sender, ComboBox)
Dim iDGVCol As Integer
Dim dvTemp As New DataView
Dim cDTTData As New cDTT.cData
Dim dsTemp As New DataSet
iDGVCol = GetSelectedDatagridviewCol
If Not cDTTData.GetLinesStationsA
Throw cDTTData.LastException
End If
dvTemp.Table = dsTemp.Tables(2)
Select Case iDGVCol
Case 0 '** Sub-Line
Case 1 '** Station#
Case 2 '** Reason Code
dvTemp.RowFilter = "StationID = " & dgDTEntry.CurrentRow.Cells
comboBox1.DataSource = dvTemp
comboBox1.DisplayMember = "DTReasonDescription"
comboBox1.ValueMember = "DTReasonID"
Debug.WriteLine("Case2: " & dgDTEntry.CurrentRow.Cells
End Select
End Sub
Private Sub cboVirtualCboFordgDTEntryG
ByVal sender As Object, ByVal e As EventArgs)
'+------------------------
'| Purpose: To handle the SelectedIndexChanged event for all drop-down |
'| comboboxes in the dgDTEEntry datagridview. |
'+------------------------
Dim comboBox1 As ComboBox = CType(sender, ComboBox)
Dim iDGVCol As Integer
Dim dvTemp As New DataView
Dim cDTTData As New cDTT.cData
Dim dsTemp As New DataSet
iDGVCol = GetSelectedDatagridviewCol
If Not cDTTData.GetLinesStationsA
Throw cDTTData.LastException
End If
dvTemp.Table = dsTemp.Tables(2)
Select Case iDGVCol
Case 0 '** Sub-Line
Case 1 '** Station#
Case 2 '** Reason Code
dvTemp.RowFilter = "StationID = " & dgDTEntry.CurrentRow.Cells
comboBox1.DataSource = dvTemp
comboBox1.DisplayMember = "DTReasonDescription"
comboBox1.ValueMember = "DTReasonID"
Debug.WriteLine("Case2: " & dgDTEntry.CurrentRow.Cells
End Select
End Sub
ASKER
Further to my previous post, it is NOT random in the way that it is filtering. It only works for the second row down and not any below or above it. Now it did once or twice also work for the top row but I've never been able to replicate that again.
Also, I noticed the data in the tbl_Details that I posted is wrong! In the last line (2 3 4 1 Blah2) , the 4 should be a 5 which represents the ReasonID. If I get the filtering working as I want it to, the user should never be able to select ReasonID = 4 with a stationID =3!
Also, I noticed the data in the tbl_Details that I posted is wrong! In the last line (2 3 4 1 Blah2) , the 4 should be a 5 which represents the ReasonID. If I get the filtering working as I want it to, the user should never be able to select ReasonID = 4 with a stationID =3!
ASKER
Thank you for the help thus far Razo. Since I haven't found a solution to using the default datagridview that works for the column types in question I am going to close the question off as a B grade. I'll give the opportunity for you to post if you have something to add and will leave it be for a few days before closing it off.
I ended up just creating a panel with a row of edit text & combo boxes with edit/delete buttons. The panel is positioned over the current row whenever a CellEnter event occurs, mirroring the underlying datagrid row of data to/from the panel controls. Each row is editable one at a time this way. It is not the ideal, but at least I can control exactly what the user sees based on the combobox selections in the edit panel row.
It really is a shame that the default Microsoft datagrids couldn't be as easy to manuipulate as are some of the third party controls that are out there!
Regards,
Bbo
I ended up just creating a panel with a row of edit text & combo boxes with edit/delete buttons. The panel is positioned over the current row whenever a CellEnter event occurs, mirroring the underlying datagrid row of data to/from the panel controls. Each row is editable one at a time this way. It is not the ideal, but at least I can control exactly what the user sees based on the combobox selections in the edit panel row.
It really is a shame that the default Microsoft datagrids couldn't be as easy to manuipulate as are some of the third party controls that are out there!
Regards,
Bbo
ASKER
Ack! My apologies for leaving the question! I pride myself on closing questions off as quick as possible but it appears as though this one slipped below the radar! :-/ Ah, life in the fast lane...
Bob
Bob