parent-child and comboboxcolumn

Hi NG!
Is there any way to have a ComboBoxColumn display a different set of values
for each different row in a DataGridView? What I mean is, given the
following situation,

ParentTable
parent_id (1) ......... active_child_id_fk (3)
parent_id (2) ......... active_child_id_fk (4)
parent_id (3) ......... active_child_id_fk (7)


ChildTable
child_id (1) .......... parent_id_fk (1)
child_id (2) .......... parent_id_fk (1)
child_id (3) .......... parent_id_fk (1)

child_id (4) .......... parent_id_fk (2)
child_id (5) .......... parent_id_fk (2)

child_id (6) .......... parent_id_fk (3)
child_id (7) .......... parent_id_fk (3)

where the parent_id_fk field in ChildTable is part of a normal relationship
and describes the available set of child records available for each parent
and the active_child_id_fk in ParentTable holds an 'active' child, is there
any way to have a DataGridView display the ParentTable record-set and for
the active_child_column to display a combobox that lists only the child
records that are available for the current parent record? (It is different
from the usual situation where the combobox holds the same set of records
for all the records in the DataGridView, in the usual case those are the
parent records...) The relationship details in my case don't actually
matter, let' say that there is a 'generic' mechanism to get the available
set... not necessarily a parent-child relationship, but maybe some other
kind of filtering...


Thanks in advance.




pax

(VS2005 Standard, SQLExpress2005, WinXpSP2)

Hi,

<pax> wrote in message news:ulw5yX$KGHA.2492@TK2MSFTNGP10.phx.gbl...
>
>
> Hi NG!
> Is there any way to have a ComboBoxColumn display a different set of
> values for each different row in a DataGridView? What I mean is, given the
> following situation,
>
> ParentTable
> parent_id (1) ......... active_child_id_fk (3)
> parent_id (2) ......... active_child_id_fk (4)
> parent_id (3) ......... active_child_id_fk (7)
>
>
> ChildTable
> child_id (1) .......... parent_id_fk (1)
> child_id (2) .......... parent_id_fk (1)
> child_id (3) .......... parent_id_fk (1)
>
> child_id (4) .......... parent_id_fk (2)
> child_id (5) .......... parent_id_fk (2)
>
> child_id (6) .......... parent_id_fk (3)
> child_id (7) .......... parent_id_fk (3)

Ok, so lets say you have a DataSet with ParentTable and ChildTable. Though
the relation isn't that important as you pointed out, you could still use it
to your benifit.

Next, you have a BindingSource for the parent table and two BindingSource's
for the child table, as follow:

ParentBindingSource:
DataSource = SomeDataSet
DataMember = "ParentTable"

ChildBindingSource: (unfiltered)
DataSource = SomeDataSet
DataMember = "ChildTable"

ChildFilteredBindingSource: (filtered using relation)
DataSource = ParentBindingSource
DataMember = "Name of Relation"

DataGridView:
DataSource = ParentBindingSource

DGVComboBoxColumn:
DataPropertyName = "active_child_id_fk"
DataSource = ChildBindingSource (unfiltered)
DisplayMember = "...."
ValueMember = "child_id"

Next add an eventhandler for DataGridView.OnCellBeginEdit() and
DataGridView.OnCellEndEdit():

private void dataGridView1_CellBeginEdit(object sender,
DataGridViewCellCancelEventArgs e)
{
if ( e.ColumnIndex == 2 ) // suppose column2 is the combobox column
{
DataGridViewComboBoxCell cell =
(DataGridViewComboBoxCell)dataGridView1[e.ColumnIndex, e.RowIndex];

cell.DataSource = ChildFilteredBindingSource; // replace with filtered
BindingSource
}
}

private void dataGridView1_CellEndEdit(object sender,
DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 2)
{
DataGridViewComboBoxCell cell =
(DataGridViewComboBoxCell)dataGridView1[e.ColumnIndex, e.RowIndex];

cell.DataSource = ChildBindingSource; //restore to unfiltered
BindingSource
}
}


If there wouldn't be a relation, then you still need an unfiltered and
filtered BindingSource, the filtered BindingSource could then be filtered at
CellBeginEdit.


HTH,
Greetings



>
> where the parent_id_fk field in ChildTable is part of a normal
> relationship and describes the available set of child records available
> for each parent and the active_child_id_fk in ParentTable holds an
> 'active' child, is there any way to have a DataGridView display the
> ParentTable record-set and for the active_child_column to display a
> combobox that lists only the child records that are available for the
> current parent record? (It is different from the usual situation where the
> combobox holds the same set of records for all the records in the
> DataGridView, in the usual case those are the parent records...) The
> relationship details in my case don't actually matter, let' say that there
> is a 'generic' mechanism to get the available set... not necessarily a
> parent-child relationship, but maybe some other kind of filtering...
>
>
> Thanks in advance.
>
>
>
>
> pax
>
> (VS2005 Standard, SQLExpress2005, WinXpSP2)
>
>
>
>

Thanks Bart.
As far as I can see the whole thing is based on the fact that each filtered
set is part of a larger set that contains all records. Hm... never saw it
this way. I guess it fits my needs for now. But if all the options in each
combobox were part of some completely disjunct sets, having, let's say,
completely different semantics then we needed to create that set (union or
something). I think about a case where two different types of objects are
displayed in the same grid for space and usability's sake, they conform
loosely to a certain template... then a fictitious 'ItemState' column should
be filled with completely different values, maybe coming from different
tables... What I'm trying to say is that in your solution (a very useful
one) the set of available values is not intrinsically linked to the main
(grid) record... I can't help thinking to shapes/chapter-fields... :-)

Thanks a bunch.





pax




"Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message
news:OY6ZA9$KGHA.524@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> <pax> wrote in message news:ulw5yX$KGHA.2492@TK2MSFTNGP10.phx.gbl...
>>
>>
>> Hi NG!
>> Is there any way to have a ComboBoxColumn display a different set of
>> values for each different row in a DataGridView? What I mean is, given
>> the following situation,
>>
>> ParentTable
>> parent_id (1) ......... active_child_id_fk (3)
>> parent_id (2) ......... active_child_id_fk (4)
>> parent_id (3) ......... active_child_id_fk (7)
>>
>>
>> ChildTable
>> child_id (1) .......... parent_id_fk (1)
>> child_id (2) .......... parent_id_fk (1)
>> child_id (3) .......... parent_id_fk (1)
>>
>> child_id (4) .......... parent_id_fk (2)
>> child_id (5) .......... parent_id_fk (2)
>>
>> child_id (6) .......... parent_id_fk (3)
>> child_id (7) .......... parent_id_fk (3)
>
> Ok, so lets say you have a DataSet with ParentTable and ChildTable.
> Though the relation isn't that important as you pointed out, you could
> still use it to your benifit.
>
> Next, you have a BindingSource for the parent table and two
> BindingSource's for the child table, as follow:
>
> ParentBindingSource:
> DataSource = SomeDataSet
> DataMember = "ParentTable"
>
> ChildBindingSource: (unfiltered)
> DataSource = SomeDataSet
> DataMember = "ChildTable"
>
> ChildFilteredBindingSource: (filtered using relation)
> DataSource = ParentBindingSource
> DataMember = "Name of Relation"
>
> DataGridView:
> DataSource = ParentBindingSource
>
> DGVComboBoxColumn:
> DataPropertyName = "active_child_id_fk"
> DataSource = ChildBindingSource (unfiltered)
> DisplayMember = "...."
> ValueMember = "child_id"
>
> Next add an eventhandler for DataGridView.OnCellBeginEdit() and
> DataGridView.OnCellEndEdit():
>
> private void dataGridView1_CellBeginEdit(object sender,
> DataGridViewCellCancelEventArgs e)
> {
> if ( e.ColumnIndex == 2 ) // suppose column2 is the combobox column
> {
> DataGridViewComboBoxCell cell =
> (DataGridViewComboBoxCell)dataGridView1[e.ColumnIndex, e.RowIndex];
>
> cell.DataSource = ChildFilteredBindingSource; // replace with
> filtered BindingSource
> }
> }
>
> private void dataGridView1_CellEndEdit(object sender,
> DataGridViewCellEventArgs e)
> {
> if (e.ColumnIndex == 2)
> {
> DataGridViewComboBoxCell cell =
> (DataGridViewComboBoxCell)dataGridView1[e.ColumnIndex, e.RowIndex];
>
> cell.DataSource = ChildBindingSource; //restore to unfiltered
> BindingSource
> }
> }
>
>
> If there wouldn't be a relation, then you still need an unfiltered and
> filtered BindingSource, the filtered BindingSource could then be filtered
> at CellBeginEdit.
>
>
> HTH,
> Greetings
>
>
>
>>
>> where the parent_id_fk field in ChildTable is part of a normal
>> relationship and describes the available set of child records available
>> for each parent and the active_child_id_fk in ParentTable holds an
>> 'active' child, is there any way to have a DataGridView display the
>> ParentTable record-set and for the active_child_column to display a
>> combobox that lists only the child records that are available for the
>> current parent record? (It is different from the usual situation where
>> the combobox holds the same set of records for all the records in the
>> DataGridView, in the usual case those are the parent records...) The
>> relationship details in my case don't actually matter, let' say that
>> there is a 'generic' mechanism to get the available set... not
>> necessarily a parent-child relationship, but maybe some other kind of
>> filtering...
>>
>>
>> Thanks in advance.
>>
>>
>>
>>
>> pax
>>
>> (VS2005 Standard, SQLExpress2005, WinXpSP2)
>>
>>
>>
>>
>
>

Hi,

<pax> wrote in message news:uZMkf9OLGHA.536@TK2MSFTNGP09.phx.gbl...
>
> Thanks Bart.
> As far as I can see the whole thing is based on the fact that each
> filtered set is part of a larger set that contains all records.

Exactly. It saves you from creating a set of DataView's or BindingSource's,
just filter on the fly when the user edit the row/cell. But if you use a
single filtered BindingSource then you need an unfiltered one too.
Otherwise all ComboBoxCell's would all be limited to the same set once you
change the filtered BindingSource, and since cells from other rows may be
repainted while selecting from a combobox this wouldn't work.

> Hm... never saw it this way. I guess it fits my needs for now. But if all
> the options in each combobox were part of some completely disjunct sets,
> having, let's say, completely different semantics then we needed to create
> that set (union or something). I think about a case where two different
> types of objects are displayed in the same grid for space and usability's
> sake, they conform loosely to a certain template... then a fictitious
> 'ItemState' column should be filled with completely different values,
> maybe coming from different tables...

But i don't think anything prevents you from assigning a set of (different)
DataSource's to the DGVDataGridViewComboBoxCell's, if that's what you need.

HTH,
Greetings

> What I'm trying to say is that in your solution (a very useful one) the
> set of available values is not intrinsically linked to the main (grid)
> record... I can't help thinking to shapes/chapter-fields... :-)
>
> Thanks a bunch.
>
>
>
>
>
> pax
>
>
>
>
> "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message
> news:OY6ZA9$KGHA.524@TK2MSFTNGP09.phx.gbl...
>> Hi,
>>
>> <pax> wrote in message news:ulw5yX$KGHA.2492@TK2MSFTNGP10.phx.gbl...
>>>
>>>
>>> Hi NG!
>>> Is there any way to have a ComboBoxColumn display a different set of
>>> values for each different row in a DataGridView? What I mean is, given
>>> the following situation,
>>>
>>> ParentTable
>>> parent_id (1) ......... active_child_id_fk (3)
>>> parent_id (2) ......... active_child_id_fk (4)
>>> parent_id (3) ......... active_child_id_fk (7)
>>>
>>>
>>> ChildTable
>>> child_id (1) .......... parent_id_fk (1)
>>> child_id (2) .......... parent_id_fk (1)
>>> child_id (3) .......... parent_id_fk (1)
>>>
>>> child_id (4) .......... parent_id_fk (2)
>>> child_id (5) .......... parent_id_fk (2)
>>>
>>> child_id (6) .......... parent_id_fk (3)
>>> child_id (7) .......... parent_id_fk (3)
>>
>> Ok, so lets say you have a DataSet with ParentTable and ChildTable.
>> Though the relation isn't that important as you pointed out, you could
>> still use it to your benifit.
>>
>> Next, you have a BindingSource for the parent table and two
>> BindingSource's for the child table, as follow:
>>
>> ParentBindingSource:
>> DataSource = SomeDataSet
>> DataMember = "ParentTable"
>>
>> ChildBindingSource: (unfiltered)
>> DataSource = SomeDataSet
>> DataMember = "ChildTable"
>>
>> ChildFilteredBindingSource: (filtered using relation)
>> DataSource = ParentBindingSource
>> DataMember = "Name of Relation"
>>
>> DataGridView:
>> DataSource = ParentBindingSource
>>
>> DGVComboBoxColumn:
>> DataPropertyName = "active_child_id_fk"
>> DataSource = ChildBindingSource (unfiltered)
>> DisplayMember = "...."
>> ValueMember = "child_id"
>>
>> Next add an eventhandler for DataGridView.OnCellBeginEdit() and
>> DataGridView.OnCellEndEdit():
>>
>> private void dataGridView1_CellBeginEdit(object sender,
>> DataGridViewCellCancelEventArgs e)
>> {
>> if ( e.ColumnIndex == 2 ) // suppose column2 is the combobox column
>> {
>> DataGridViewComboBoxCell cell =
>> (DataGridViewComboBoxCell)dataGridView1[e.ColumnIndex, e.RowIndex];
>>
>> cell.DataSource = ChildFilteredBindingSource; // replace with
>> filtered BindingSource
>> }
>> }
>>
>> private void dataGridView1_CellEndEdit(object sender,
>> DataGridViewCellEventArgs e)
>> {
>> if (e.ColumnIndex == 2)
>> {
>> DataGridViewComboBoxCell cell =
>> (DataGridViewComboBoxCell)dataGridView1[e.ColumnIndex, e.RowIndex];
>>
>> cell.DataSource = ChildBindingSource; //restore to unfiltered
>> BindingSource
>> }
>> }
>>
>>
>> If there wouldn't be a relation, then you still need an unfiltered and
>> filtered BindingSource, the filtered BindingSource could then be filtered
>> at CellBeginEdit.
>>
>>
>> HTH,
>> Greetings
>>
>>
>>
>>>
>>> where the parent_id_fk field in ChildTable is part of a normal
>>> relationship and describes the available set of child records available
>>> for each parent and the active_child_id_fk in ParentTable holds an
>>> 'active' child, is there any way to have a DataGridView display the
>>> ParentTable record-set and for the active_child_column to display a
>>> combobox that lists only the child records that are available for the
>>> current parent record? (It is different from the usual situation where
>>> the combobox holds the same set of records for all the records in the
>>> DataGridView, in the usual case those are the parent records...) The
>>> relationship details in my case don't actually matter, let' say that
>>> there is a 'generic' mechanism to get the available set... not
>>> necessarily a parent-child relationship, but maybe some other kind of
>>> filtering...
>>>
>>>
>>> Thanks in advance.
>>>
>>>
>>>
>>>
>>> pax
>>>
>>> (VS2005 Standard, SQLExpress2005, WinXpSP2)
>>>
>>>
>>>
>>>
>>
>>
>
>

...................
> Exactly. It saves you from creating a set of DataView's or
> BindingSource's, just filter on the fly when the user edit the row/cell.
> But if you use a single filtered BindingSource then you need an unfiltered
> one too. Otherwise all ComboBoxCell's would all be limited to the same set
> once you change the filtered BindingSource, and since cells from other
> rows may be repainted while selecting from a combobox this wouldn't work.

Yep, that's exactly what happens, I know it for sure since I have tried to
do the same thing with the old DataGrid (1.x). Thanks for pointing me to the
DataGridViewComboBoxCell.DataSource, I guess that's what makes the whole
thing possible.

Thanks.



pax