DataGridViewComboBoxColumn databinding w/a many-to-many relationship?

[Mark@SF]

I have a DataGridView that has a DataGridViewComboBoxColumn column. I want to bind this column to a many-to-many relationship or "junction table" (MS Access terminology) and am having trouble getting things to work.

Here is the data diagram:

Attachment 176551

I would like the DataGridViewComboBoxColumn to show the ObjectType (DisplayMember) from the ObjectTypes datatable in the control's drop-down list, but have the control bound to the ObjectTypeID (ValueMember) in the RevisionObjects datatable. The RevisionObjects datatable would contain the ObjectType, not the ObjectType after the user makes their selection.

As the user moves between records in the RevisionNotes datatable, the grid should show 2 columns: ObjectType (e.g., "Form", "Module", "Class", etc.) and ObjectName ("Form1", "Module1", "Class1", etc.).

Here is how I've set up the Relations:

'RevisionNote and RevisionObjects
dsMain.Relations.Add(name:="RevisionNote_RevisionObjects", parentColumn:=dsMain.Tables("RevisionNotes").Columns("RecID"), childColumn:=dsMain.Tables("RevisionObjects").Columns("NoteID"))
fkc = dsMain.Relations("RevisionNote_RevisionObjects").ChildKeyConstraint
fkc.DeleteRule = Rule.Cascade
fkc.UpdateRule = Rule.Cascade
fkc.AcceptRejectRule = AcceptRejectRule.Cascade


'ObjectType and RevisionObjects
dsMain.Relations.Add(name:="ObjectType_RevisionObjects", parentColumn:=dsMain.Tables("ObjectTypes").Columns("RecID"), childColumn:=dsMain.Tables("RevisionObjects").Columns("ObjectTypeID"))
fkc = dsMain.Relations("ObjectType_RevisionObjects").ChildKeyConstraint
fkc.DeleteRule = Rule.SetNull   '...when an ObjectTypes table record is deleted, set the related child RevisionObjects table's RevisionObjects.ObjectTypeID = DBNull.Value (this field is not required and IsNullable)
fkc.UpdateRule = Rule.Cascade
fkc.AcceptRejectRule = AcceptRejectRule.Cascade

Here is how I'm configuring the DataGridView columns:

With dgvObjects

    Dim col0 As New DataGridViewTextBoxColumn
    With col0
        .DataPropertyName = "RecID"
        .Name = "RecID"
        .HeaderText = "RecID"
        .Visible = False
    End With
    .Columns.Add(col0)

    Dim col1 As New DataGridViewComboBoxColumn
    With col1
        col1.DisplayMember = "ObjectName"
        col1.ValueMember = "ObjectTypeID"
        col1.HeaderText = "Type"
        col1.DataSource = bsRevisionObjects
        .HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft
        .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        .SortMode = DataGridViewColumnSortMode.Automatic    '...sorting glyph is automatically displayed in the column header
    End With
    .Columns.Add(col1)

    Dim col2 As New DataGridViewTextBoxColumn
    With col2
        .DataPropertyName = "ObjectName"
        .Name = "Name"
        .HeaderText = "Name"
        .HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft
        .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft
        .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        .SortMode = DataGridViewColumnSortMode.Automatic    '...sorting glyph is automatically displayed in the column header
    End With
    .Columns.Add(col2)

End With

Here's how I'm binding the data:

            With bsObjects
                .DataSource = Nothing
                .DataMember = "ObjectTypes"    '...bind to relationship (before setting the DataSource property, which fires the PositionChanged() event of the BindingSource)
                .DataSource = dsMain    '...fires the BindingSource's PositionChanged() event
                .Sort = "ObjectType"
            End With
            If flgDebug Then Debug.WriteLine("bsObjects datasource has " & bsObjects.Count & " items.")

            With bsRevisionObjects
                .DataSource = Nothing
                .DataMember = "ObjectType_RevisionObjects"    '...bind to relationship (before setting the DataSource property, which fires the PositionChanged() event of the BindingSource)
                .DataSource = bsObjects    '...fires the BindingSource's PositionChanged() event
                .Sort = "ObjectName"
            End With

            If flgDebug Then Debug.WriteLine("bsRevisionObjects datasource has " & bsRevisionObjects.Count & " items.")

            dgvObjects.DataSource = bsRevisionObjects

When this code executes, the DataGridView shows every column from the datatable (all 6 from the RevisionObjects datable plus the 2 that I explicitly configured above), even though I have set the grid's AutoGenerateColumns = False.



I need some guidance to get this figured out and would appreciate some help.

[jmcilhinney]

How many grids do you have? It seems to me that you should have two grids in a parent/child relationship: one for RevisionNotes and one for RevisionObjects. When the user selects a RevisionNotes record in the parent grid, the child grid displays all the related RevisionObjects records. That second grid would then contain the combo box column and it's then only a simple parent/child relationship between the data in the column and the data in the grid. Does that sound reasonable to you?

[Mark@SF]

jmc -

Thanks for taking a look at this for me. I have 1 grid.



I'm using the tvMain (TreeView) control's AfterSelect event to change the bsMain's Position (which should then also update the dgvObjects (DataGridView). The text box ("This is a dummy note.") is from the RevisionNotes datatable and is bound to the bsMain (BindingSource).

The user of this app will select a node from the treeview control, which will then move the bsMain bindingsource to the corresponding record in the RevisionNotes datatable, and the grid should also update as per the binding relationship to show the ObjectType/ObjectName records that are related to the RevisionNotes record.

The image in this post is a mock-up of the actual form (I commented out the databinding and combo box column configuration). The grid in the image has 2 textbox columns that aren't bound to anything (just to show you what it "should" look like).

[jmcilhinney]

In that case, I suggest that you check out a couple of my CodeBank threads.

http://www.vbforums.com/showthread.p...T-2-0-WinForms)

That one demonstrates how to setup two BindingSources so that selecting a parent record automatically filters the child records. It doesn't really matter what the two BindingSources are bound to. Any bound controls will automatically update based on the filter.

http://www.vbforums.com/showthread.p...a-DataGridView

That one demonstrates how to bind a parent table to a combo box column in a DataGridView bound to a child table. In your case, the RevisionObjects table would be bound to the child BindingSource bound to the grid and the ObjectTypes table would be bound to a BindingSource that is bound to the combo box column.

[Mark@SF]

Thanks, jmc. I've got it working now. Your examples are great!

I was stuck on trying to setup the grid's combobox using a relation (like I do with parent/child grids where the child's DataMember is the Relation between the parent/child tables and the child's DataSource is the parent's BindingSource). Also throwing me off was the many-to-many relationship (3 tables). With just a pair of grids (parent/child), the relation is one-to-many.

In the data relationship diagram from my post #1, the binding between the RevisionNotes and RevisionObjects tables is just like previously mentioned for parent/child grids. Your example helped me to see that what I needed was a "lookup" table for the ObjectTypeID field in the RevisionObjects table, so that the grid would "lookup" value from the ObjectTypes table.

Dim col1 As New DataGridViewComboBoxColumn
With col1
    .DataPropertyName = "ObjectTypeID"  '...this is the field that is bound to the underlying datatable via the bsRevisionObjects (BindingSource)
    .DataSource = LookupTableObjectType()
    .ValueMember = "ObjectTypeID"  '...field from the LookupTableObjectType for the ComboBox's drop-down list -- must be the same data type (BigInt) as the "RecID" field in the ObjectTypes table
    .DisplayMember = "ObjectType"   '...the name of a column in the data source
    .Name = "Type"
    .HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft
    .DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing   '...when it is not in edit mode, the DataGridViewComboBoxCell is displayed without a drop-down button 
    .FlatStyle = FlatStyle.Flat '...gets rid of the DataGridViewComboBoxCell highlighting and border effects
    .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
    .SortMode = DataGridViewColumnSortMode.Automatic    '...sorting glyph is automatically displayed in the column header
End With
.Columns.Add(col1)

Here's the "lookup" function LookupTableObjectType() that returns a DataTable.

rows = dsMain.Tables("ObjectTypes").Select(filterExpression:="ObjectState = " & True, sort:="ObjectType")
If rows.Length > 0 Then
    table = rows.CopyToDataTable
    table.TableName = "ObjectTypeLookUp"
    table.Columns.Remove("AddDate")
    table.Columns.Remove("ModDate")
    table.Columns.Remove("RecDate")
    table.Columns.Remove("ObjectState")
    table.Columns.Remove("ObjectDescription")
    table.Columns("RecID").ColumnName = "ObjectTypeID"
End If

Thanks again for your help