How do I specify a 1 to many relationship?

[Rod]

I want to specify a relationship between two data tables in a dataset.
(Both data tables come from executing stored procedures in SQL Server.)
When I tried to add the relationship through the Relations property of the
dataset, I got the following error message:



"The columns don't currently have unique values."



I believe the problem is there is a one-to-many relationship between the
parent data table and the child data table. So my question is how do I
specify that when I create the relationship? Is there a property of the
DataColumn object that I have to specify? Right now I'm trying to link the
two by common columns (client number and case number); should I specify some
other column in the child's DataColumn which would be unique for each
client/case? (I didn't do this in this case, because the example I saw in
David Sceppa's book "Microsoft ADO.NET" didn't do this. I had thought that
I had to specify only those columns from both data tables which would be
linked between the parent and child DataColumns.)



I am using ADO.NET 1.1.





Rod

 

[W.G. Ryan - MVP]

Comments inline

I want to specify a relationship between two data tables in a dataset.
(Both data tables come from executing stored procedures in SQL Server.)
When I tried to add the relationship through the Relations property of the
dataset, I got the following error message:



"The columns don't currently have unique values."



I believe the problem is there is a one-to-many relationship between the
parent data table and the child data table. So my question is how do I
specify that when I create the relationship?

--Do you have a Key set up on the tables and/or unique constraints?
(http://www.knowdotnet.com/articles/datarelation.html)
Is there a property of the

DataColumn object that I have to specify? Right now I'm trying to link
the two by common columns (client number and case number); should I
specify some other column in the child's DataColumn which would be unique
for each client/case? (I didn't do this in this case, because the example
I saw in David Sceppa's book "Microsoft ADO.NET" didn't do this. I had
thought that I had to specify only those columns from both data tables
which would be linked between the parent and child DataColumns.)

The PrimaryKey property of the table takes an Array of DataColumns [even if
there's only one column - you have to use an Array which can be a little
confusing

 

[Cor Ligthert [MVP]]

Rod,

Especially for your question I have made what I think is the most minimized
sample to show a datarelation in a dataset.

http://www.vb-tips.com/default.aspx?ID=d667d78f-4b08-42ef-ba3b-8b8620d93761

I hope this helps,

Cor

 

[Rod]

Thank you, Cor, I'll give it a look.

Rod

 

[Rod]

Cor,

I went to your VB-Tips website and saw what you've done. I added a third
column from my child table to the relationship, which would really be
appropriate for this situation. So, now I have an array of DataColumns for
the parent with client number and case number in it, and I have an array of
DataColumns for the child with client number, case number and sequence
number. When I run my program I now get an error message which says:

"ParentColumns and ChildColumns should be the same length."

Well, they can't be, if I'm going to have a one-to-many parent-child
relationship. Even your example on the website you gave did not have the
ParentColumns and ChildColumns having the same length.

So, what's missing now?

Rod

 

[Cor Ligthert [MVP]]

Rod,

I did forgot, the messages tells in my opinion that your parent table has
columns (relation) which are not unique and therefore a one to many cannot
be made.

Sorry

Cor

 

[JAW]

I believe what the message is telling you is that the two columns must be of the same data type and size, i.e. they both
must be a number 5 or a character 10, etc.