[RESOLVED] How do I create a many-to-many Relation?

[Mark@SF]

jmc -

Thanks for looking at this for me. I cannot get your code to work.

As you suggested, I have bound the dgvVendors.DataSource to the Vendors DataTable (via the bsVendors BindingSource) and I have bound the dgvVendorTypes.DataSource to the VendorTypes DataTable (via the bsVendorTypes BindingSource).

In your example, I have set BindingSource1 to be bsVendorTypes and BindingSource2 to be bsVendors.

Here are the two Relations that are used by the junction table (VendorMember):

'VendorTypes and VendorMember
dsMain.Relations.Add("VendorTypes_VendorMember", dsMain.Tables("VendorTypes").Columns("RecID"), dsMain.Tables("VendorMember").Columns("VendorTypeID"))
fkc = dsMain.Relations("VendorTypes_VendorMember").ChildKeyConstraint
fkc.DeleteRule = Rule.Cascade
fkc.UpdateRule = Rule.Cascade
fkc.AcceptRejectRule = AcceptRejectRule.None

'Vendors and VendorMember
dsMain.Relations.Add("Vendors_VendorMember", dsMain.Tables("Vendors").Columns("RecID"), dsMain.Tables("VendorMember").Columns("VendorID"))
fkc = dsMain.Relations("Vendors_VendorMember").ChildKeyConstraint
fkc.DeleteRule = Rule.Cascade
fkc.UpdateRule = Rule.Cascade
fkc.AcceptRejectRule = AcceptRejectRule.None

When I call the GetParentRow method it returns Nothing (I've tried passing both of the above Relations). The returned parentRow is always Nothing. I'm not sure why you've suggested calling the GetParentRow method on the VendorTypes table because VendorTYpes is the "parent" table and VendorMembers is the "child" table (i.e., one VendorType can have many Vendors).

Also, the Vendors DataTable does not have the "ParentID" field (which as per the "VendorTypes_VendorMember" Relation will be the "VendorTypeID" field in the VendorMember junction table) your line #8 won't work.

BindingSource2.Filter = $"ParentId = {parentId}"

I would like to figure out how to get your example to work - what am I missing?

As an alternate way to populate the dgvVendors from the Vendors table in my dataset (instead of populating it from the underlying table in the MS Access database), I've added the following to the bsVendorTypes.CurrentChanged method.

dtVendors = dsMain.Tables("Vendors").Clone

For Each rowParent As DataRow In dsMain.Tables("VendorMember").Select("VendorTypeID = " & lngVendorID)
    strFilter = "RecID = " & CLng(rowParent("VendorID"))
    For Each rowChild As DataRow In dsMain.Tables("Vendors").Select(filterExpression:=strFilter)
        row = dtVendors.NewRow
        With row
            For Each col As DataColumn In dsMain.Tables("Vendors").Columns
                .Item(col.ColumnName) = rowChild(col.ColumnName)
            Next col
        End With
        dtVendors.Rows.Add(row)
    Next rowChild
Next rowParent

If dtVendors.Rows.Count <> 0 Then
    dgvVendors.DataSource = dtVendors.Select(filterExpression:="", sort:=strSort).CopyToDataTable
Else
    dgvVendors.DataSource = Nothing
End If

lblGridInfo.Text = "Vendors (" & dgvVendors.Rows.Count & CStr(IIf(dgvVendors.Rows.Count = 1, " item", " items")) & "):"

So I've got a couple of work-arounds that are working, but I would like to figure out how to make your suggested code work.

Thanks for your help with this question.