Link to home
Start Free TrialLog in
Avatar of scking
scking

asked on

Updating Tables with many-to-many Relationships Using BindingNavigator

I've 3 tables defined with relationships in the dataset and one is the link table defining the many-to-many relationship between them.  When I click on the Add button the record is added for the main table but not for the linked table.  I've attempted using the AddingNew event but didn't need it after setting defaults for the table.  This issue has stumped me for way too long.  If I could get the PrimaryKey ID value from the bindingnavigator for the created row I could handle it by adding a new row.  I've also attempted to add the linktable.AddNew call within the bindingnavigator to determine if they system would use the values created with the Add, but that failed too.
Private Sub BindingNavigatorAddNewCompItem_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewCompItem.Click
        Try
            Me.cboPnTypeComp.Select()
            Me.bsCompLinks.AddNew()
        Catch ex As Exception
            Message.AppMessageBox(ex.Message, "", MessageBoxCaptions.ErrorCaption, CType(sender, Control).Name & "_Click")
        End Try
    End Sub

Open in new window

Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Is the BindingSource attached to the primary table?  PrimaryKey values for new records are not known until the data is written to the database.

Bob
Avatar of scking
scking

ASKER

The dataset contains 3 tables; tblPPR (bsPpr), tblPprComponents (bsComp) and tblPpr_PprComp_Links (bsPprCompLinks) for PPR->tblPpr_PprComp_Links relationship and (bsCompLinks) for the tblPprComponents->tblPpr_PprComp_Links relationship.  The bsPpr bindingsource binds to controls on the main form (frame) and the bsComp bindingsource binds to controls on a tab.  Both the main form and tab have bindingnavigators so I can read all the PPRs and all the Components for each PPR.  This design works perfectly for the navigation.  I even have one-many relationship tabs that also work perfectly; but my many-many relationship BindindNavigatorAddNew fails as described in my original post.  I also tried handling the AddingNew event for a new Component and within that eventhandler calling bsCompLinks.AddNew and handling it but ran into other issues there.  
With bsPpr
	.DataSource = GetCurrentDataSet()
	.DataMember = "tblPPR"
End Wit
Me.pprBindingNavigator.BindingSource = bsPpr
 
With bsPprCompLinks
.DataSource = bsPpr
.DataMember= "FK_tblPpr_PprComp_Links_tblPPR" '"FK_tblPprComponents_tblPpr_PprComp_Links"
End With
 
With bsComp
	.AllowNew = True
	.Filter = ""
	.DataSource = aideDS
	.DataMember = "tblPprComponents"
End With
compBindingNavigator.BindingSource = bsComp
 
With bsCompLinks
	.DataSource = bsComp
         .DataMember = "FK_tblPprComponents_tblPpr_PprComp_Links"
End With

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of scking

ASKER

I do that frequently, but since I'm using bindingsources, I would need to also refresh the DataSet from the database; a round trip, and was trying to use the most elegant design.  after it is refreshed I would need to make sure the bindingsource was positioned on the correct record.  It's one of those things, where I know it can be done with the bindingsource, just not how to do it, and it's not very well documented anywhere that I can find.  My AddingNew code seemed to work most of the time but not always.
I don't use the BindingSource enough to be able to suggest a solution for this complex configuration.

Bob
Avatar of scking

ASKER

I really appreciate your answering my question though.  I've worked through it by using a stored procedure for the complex many-to-many update.  The application then gets the data from the database and clears/fills the necessary table.  The design uses a datastore which provides the dataset as a return value after filling.  There was a defect existing in the database that caused most of my problems, at least that's what I'm assuming.  After clearing it up my one-to-manys started working fine but I have gone back to redo the many-to-many relationship add because the current one works so well.  I'll admit I resisted it because I wanted a homogenous design but it's working well.  Thanks again.