many to many relationship databinding headache

  • Thread starter Thread starter Neil Steventon
  • Start date Start date
N

Neil Steventon

Hi ,

I have started developing a database application , I say start as at this rate I see no finish.

I have a windows form which is bound to mydataset table "Companys" and this works great binding the text boxes etc such as ;

txtCompanyTelNo.DataBindings.Add("Text", myDataSet, "Companys.CompanyTelNo")
I then want a datagrid to display a list of contacts for the company, which would be easy if it was a 1:m relationship, but a contact can belong to many company's. It would therefore make sense to have an interim table of compcontacts which has the primary keys of the the company table and contact table together.

I have then set up the relationships but now when I databound I am finid it difficult to show the contacts info in the datagrid as it shows the iterim table instead ? I want to use a datagrid as it is easy for the user and also easier for me when updating the data tables.

I have found some examples on the web where people have tried to get around this but it would not allow you to update the datagrid as the solutions seem to be calculated columns.

examples solutions ;

http://weblogs.asp.net/rosherove/articles/9088.aspx

http://msdn.microsoft.com/library/d...atingMultipleRelatedTablesInADONETDataset.asp

Please can anyone help.

Thanks

Neil
 
Neil,

I tried to find an answer to this a while back, and was told at that time that what we wanted to do can not be done with the built-in databinding of windowsforms. There is not a way to bind a Many-Many (with the inbetween table), so you need to make a View (or another join) that combines one of the table with the inbetween table, and then use the One-Many between the View (or another join) and the other main table. But, then you can't databind. (You would need to add a record to both the inbetween table and the Main table in the View for each newly added records. I am not an SQL wiz, so there may be a way to pull it off with triggers, or something.

I will keep an eye on this thread to see if anyone has an answer. I used to use MS Access for database apps with SQL Server. I often like what it could do better than VB / VB.NET, I just hate using it.

Kevin
Hi ,

I have started developing a database application , I say start as at this rate I see no finish.

I have a windows form which is bound to mydataset table "Companys" and this works great binding the text boxes etc such as ;

txtCompanyTelNo.DataBindings.Add("Text", myDataSet, "Companys.CompanyTelNo")
I then want a datagrid to display a list of contacts for the company, which would be easy if it was a 1:m relationship, but a contact can belong to many company's. It would therefore make sense to have an interim table of compcontacts which has the primary keys of the the company table and contact table together.

I have then set up the relationships but now when I databound I am finid it difficult to show the contacts info in the datagrid as it shows the iterim table instead ? I want to use a datagrid as it is easy for the user and also easier for me when updating the data tables.

I have found some examples on the web where people have tried to get around this but it would not allow you to update the datagrid as the solutions seem to be calculated columns.

examples solutions ;

http://weblogs.asp.net/rosherove/articles/9088.aspx

http://msdn.microsoft.com/library/d...atingMultipleRelatedTablesInADONETDataset.asp

Please can anyone help.

Thanks

Neil
 
Hi Neil,

When you post to a newsgroup in Rich HTML the change that you get an answer
is not very high.
For a lot of newsreaders it is almost unreadable and for the ones they can
it is often a problam, because of the fact that the rows are to long.

Search for your problem for "Master-Detail Form" on MSDN, than you
probably get your answer.

It is also in this resource kit.

http://msdn.microsoft.com/vbasic/vbrkit/default.aspx

And if you have problems installing it

http://msdn.microsoft.com/vbasic/vbrkit/faq/#installvdir

I hope this helps a little bit?

Cor
 
Back
Top