VS2005 combox question

[Bob]

The scenario is that of the employees table wherein there's an EmployeeId
and a ReportsTo field in the same table. The combobox is one in a datagrid
view that lets you select an employee from the employees table to fill in
the reportsto field, but since a person can't report to himself. I'm
wondering how to exclude the EmployeeId of the selected record from the
dropdown list of the combox. Ideally I think it would be to apply a filter
to the table that fills the combobox each time a new row is selected. Does
any one have any code snippets showing how to do that?
Any help would be appreciated.

Bob

 

[Bart Mermuys]

Hi,

The scenario is that of the employees table wherein there's an EmployeeId
and a ReportsTo field in the same table. The combobox is one in a datagrid
view that lets you select an employee from the employees table to fill in
the reportsto field, but since a person can't report to himself. I'm
wondering how to exclude the EmployeeId of the selected record from the
dropdown list of the combox. Ideally I think it would be to apply a filter
to the table that fills the combobox each time a new row is selected. Does
any one have any code snippets showing how to do that?
Any help would be appreciated.

Have a look at the DataGridView faq(A.18) at:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=152467&SiteID=1

Applying it to your case, since you have circular reference you only need
one DataTable (and TableAdapter) eg. Employee. But you need 3
BindingSource's. One BindingSource is for the DataGridView, one for the
ReportsToComboBoxColumn and another only for the ComboBoxCell currently
being edited (and filtered). It's important that only the currently editing
ComboBoxCell has a filtered BindingSource, the others need the unfiltered
BindingSource so that they can correctly paint the lookup values.

What you need on the Form is the following:

- DataSet (eg. DataSet1) with a DataTable in it (eg. "Employee" )

- EmployeeBindingSource ( DataSource = DataSet1, DataMember="Employee" )
- ReportsToBindingSource ( DataSource = DataSet1, DataMember="Employee" )
- ReportsToFilteredBindingSource ( DataSource = DataSet1,
DataMember="Employee" )

- EmlpoyeeDataGridView ( DataSource = EmployeeBindingSource )
- ReportsToDataGridViewColumn
DataSource = ReportsToBindingSource
DisplayMember="EmployeeName"
ValueMember="EmployeeId"
DataPropertyName="ReportsTo"

Then in code you need:

Private Sub EmployeesDataGridView_CellBeginEdit(...) Handles ...

If (
EmlpoyeesDataGridView.Columns(e.ColumnIndex).DataPropertyName="ReportsTo" )
Then

Dim editingCombo As DataGridViewComboBoxCell = _
EmployeesDataGridView(e.ColumnIndex, e.RowIndex)

ReportsToFilteredBindingSource.Filter = "EmployeeId<>" + _
EmployeesDataGridView("EmployeeId", e.RowIndex).Value.ToString()

editingCombo.DataSource = ReportsToFilteredBindingSource
End If
End Sub

Private Sub EmployeesDataGridView_CellEndEdit(...) Handles ...

If (
EmlpoyeesDataGridView.Columns(e.ColumnIndex).DataPropertyName="ReportsTo" )
Then
Dim editingCombo As DataGridViewComboBoxCell = _
EmployeesDataGridView(e.ColumnIndex, e.RowIndex)

editingCombo.DataSource = ReportsToBindingSource
End If
End Sub


HTH,
Greetings

 

[Bob]

Thanks a lot Bart.

Hi,



Have a look at the DataGridView faq(A.18) at:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=152467&SiteID=1

Applying it to your case, since you have circular reference you only need
one DataTable (and TableAdapter) eg. Employee. But you need 3
BindingSource's. One BindingSource is for the DataGridView, one for the
ReportsToComboBoxColumn and another only for the ComboBoxCell currently
being edited (and filtered). It's important that only the currently
editing ComboBoxCell has a filtered BindingSource, the others need the
unfiltered BindingSource so that they can correctly paint the lookup
values.

What you need on the Form is the following:

- DataSet (eg. DataSet1) with a DataTable in it (eg. "Employee" )

- EmployeeBindingSource ( DataSource = DataSet1, DataMember="Employee" )
- ReportsToBindingSource ( DataSource = DataSet1, DataMember="Employee" )
- ReportsToFilteredBindingSource ( DataSource = DataSet1,
DataMember="Employee" )

- EmlpoyeeDataGridView ( DataSource = EmployeeBindingSource )
- ReportsToDataGridViewColumn
DataSource = ReportsToBindingSource
DisplayMember="EmployeeName"
ValueMember="EmployeeId"
DataPropertyName="ReportsTo"

Then in code you need:

Private Sub EmployeesDataGridView_CellBeginEdit(...) Handles ...

If (
EmlpoyeesDataGridView.Columns(e.ColumnIndex).DataPropertyName="ReportsTo"
) Then

Dim editingCombo As DataGridViewComboBoxCell = _
EmployeesDataGridView(e.ColumnIndex, e.RowIndex)

ReportsToFilteredBindingSource.Filter = "EmployeeId<>" + _
EmployeesDataGridView("EmployeeId", e.RowIndex).Value.ToString()

editingCombo.DataSource = ReportsToFilteredBindingSource
End If
End Sub

Private Sub EmployeesDataGridView_CellEndEdit(...) Handles ...

If (
EmlpoyeesDataGridView.Columns(e.ColumnIndex).DataPropertyName="ReportsTo"
) Then
Dim editingCombo As DataGridViewComboBoxCell = _
EmployeesDataGridView(e.ColumnIndex, e.RowIndex)

editingCombo.DataSource = ReportsToBindingSource
End If
End Sub


HTH,
Greetings