ADO.NET DataRelation

[Tim]

I am trying to learn the proper use of the DataRelation class in
ADO.net. When I try to create a DataRelation and add it to my
dataset, I get an error stating that says "This constraint cannot be
enabled as not all values have corresponding parent values."

After initially receiving this error, I successfully create a
constraint between the two tables in question without error. Upon
creating the constraint, I left "check existing data on creation"
checked. I also ran numerous queries against the table in an attempt
to troubleshoot the problem. Here is the query I used to check the
integrity of my data:

/*********************************/
select * from ann_announcement
where not exists
(select * from dpt_department
where ann_dpt_id = dpt_id)
/*********************************/

where dpt_id is the primary key, and ann_dpt_id is the foreign key.
Below is the code I am using:

'*********************************
SqlDataAdapter1.Fill(DataSet11, "ann_announcement")
SqlDataAdapter2.Fill(DataSet11, "dpt_department")
Try
' Get the DataColumn objects from two DataTable objects in
a DataSet.
Dim parentCol As DataColumn
Dim childCol As DataColumn
' Code to get the DataSet not shown here.
parentCol =
DataSet11.Tables("dpt_department").Columns("dpt_id")
childCol =
DataSet11.Tables("ann_announcement").Columns("ann_dpt_id")
' Create DataRelation.
Dim relDeptAnnouncements As DataRelation
relDeptAnnouncements = New DataRelation("ann_dpt",
parentCol, childCol)
' Add the relation to the DataSet.
DataSet11.Relations.Add(relDeptAnnouncements)
Catch e2 As SqlClient.SqlException
System.Diagnostics.Debug.Write(e2.Message)
Catch e3 As Exception
System.Diagnostics.Debug.Write(e3.Message)
End Try
'*********************************

Any clues that you can offer me would be appreciated!

Thanks -

TJR

 

[Bender]

change this

DataSet11.Relations.Add(relDeptAnnouncements)

to this

DataSet11.Relations.Add(relDeptAnnouncements, False)

by default when you create a relationship in this way it enforces fk
constraints, by adding the False you are telling it you dont wish to
do this.

ps youd be better off doing a outer join on the table and using Is
Null then the nested select statement that you have used