many to many relationship databinding headache

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.A dd("Text", myDataSet, "Companys.Compa nyTelNo")
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/de...NETDataset.asp

Please can anyone help.

Thanks

Neil

Nov 20 '05 #1

Subscribe Reply

2818

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
"Neil Steventon" <ne***********@ blueyonder.co.u k> wrote in message news:hK******** *****@news-binary.blueyond er.co.uk...
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.A dd("Text", myDataSet, "Companys.Compa nyTelNo")
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/de...NETDataset.asp

Please can anyone help.

Thanks

Neil

Nov 20 '05 #2

Cor

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/vbr...q/#installvdir

I hope this helps a little bit?

Cor

Nov 20 '05 #3

Similar topics

3421

Run SQL For "many" table with relationship Established

by: Larry R Harrison Jr | last post by:

I have an Access 97 database with 2 tables that have a one-many relationship. I have a SQL statement in the "one" table which I want to execute and insert 7 records into the "many" table, and I want them to be linked to the "main" table. Problem is, it won't let me run the SQL run as long as the one-many relationship is established in the relationship window. The SQL is:

Microsoft Access / VBA

2700

Many to Many Relationships- How far is too far?

by: Megan | last post by:

hello everybody, i know this is a very long post, but i wanted to provide as much detail as possible. quick overview- i want to create a couple of many to many relationships and am wondering how many relationships to create. i am also trying to figure out what relationships to create.

Microsoft Access / VBA

2097

DataBinding (one to one relationship)

by: Keith Kroculick | last post by:

Hello, I have a small Access database with 5 tables. I have created a form in C# that I would like to bind to 2 tables (Contacts & Categories). The categories table is more or less a lookup table. Can anyone give me a small example on how to bind 2 controls (textbox and combobox) to 2 different fields with these related tables (one to one relationship). I know I need to create a datarelation object but I can't seem to find a decent C#...

C# / C Sharp

1018

DataBinding headache

by: Steve | last post by:

Hello I've got a databinding problem which occurs when I call EndCurrentEdit. For some reason the bound control (a combobox) reverts to a previous value, and not the one I have just set. The only way I have found to solve the problem is as follows: cboCustomer.SelectedValue = p_CustomerNumber; cboCustomer.Update();

C# / C Sharp

2097

Data Relationship binding

by: Leon Shaw | last post by:

I have two Dropdownlist and one which is containing States and one containing Cities. I know how to do all the little databinding task in visual studios interface as well as set the relationship (parent, child, etc.). In the code behind file in page_load event I fill the data adapter and databind the State dropdownlist. I also set the on_change event for the Cities dropdownlist after a state is selected. However, When I run this page in...

ASP.NET

1887

Many to Many cOnFuSiOn

by: the other john | last post by:

The trouble currently with 3 tables. I'm excluding non-relevant fields... tbl_users PK_user_ID tbl_developers PK_developer_ID FK_developer_user_ID FK_developer_project_ID

ASP / Active Server Pages

1358

Displaying complex one to many relationship

by: Mike Brind | last post by:

<Mental block> I want to display a kind of two-tier one-to-many relationship, for which, in the olden days, I would have used a series of nested loops and multiple calls to the db. Here's the trimmed down code so far: <% sql = "SELECT StoryType.StoryType, Articles.ArticleID, Articles.Title,

ASP / Active Server Pages

2549

Many to many relationship

by: paulcjcross | last post by:

How to set up a many to many relationship. I know you need a third table to join the other two. I need one table (table1) with stock_numbers(unique), one table (table2) with job-numbers(unique) and I want to join them with a table (table3) with job-number linked with a stock-number and the number-of-units-of-that-stock. how is it done? so that the relationship window shows up: table1 many to one table3 and table2 many to one table3

Microsoft Access / VBA

4061

Trouble with creating one to many relationship between tables

by: NicoleCartrette | last post by:

Going back to school is easier said than done.. This was posted to an older thread earlier but I don't think it got any attention. Your help is appreciated Professor requires we create a simple database and specified what are to be the primary keys and relationships etc. I have created the four tables and established the neccessary one to many and many to one relationships between primary keys in the tables with the exception of one...

Microsoft Access / VBA

9645

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9481

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10336

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10095

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9953

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6741

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5383

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4054

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2881

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General