not all values have corresponding parent values

Jim in Arizona · Aug 23, 2005

This doesn't make any sense to me. I'm hoping some SQL guru out there knows
the answer.

I'm making a help desk type database system. There's only two tables, one
called TTickets and the other called TNotes. The Primay Key of TTickets is a
one to Many to the Foreign Key of the TNotes table.

In my VB code, I create a relation between the two tables so I can display
several notes (from TNotes) with a single entry in the TTickets table. So
far, all was going well until I changed my SQL string. Originally, the SQL
string for the TTickets table was "SELECT * FROM TTickets". I got to a point
to where I was going to begin designing my queries so I changed the SQL
string to "SELECT * FROM TTickets WHERE resolve = 1". The resolve column is
a bit field (on or off). This worked just fine too. It's when I changed it
to "SELECT * FROM TTickets WHERE resolve = 0" that I got an exception. I
don't know what corresponding parent values it is talking about because
there aren't any (that I know of). This the error:

================================================

This constraint cannot be enabled as not all values have corresponding
parent values.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.ArgumentException: This constraint cannot be
enabled as not all values have corresponding parent values.

Source Error:

Line 28:
Line 29: relation1 = New DataRelation("RelationA", parentCol,
childCol)
Line 30: objDataSet.Relations.Add(relation1)
Line 31:
Line 32: Dim strResults As String = ""

Source File: E:\hhsinternal\tickets\reports.aspx.vb Line: 30

Stack Trace:

[ArgumentException: This constraint cannot be enabled as not all values have
corresponding parent values.]
System.Data.ConstraintCollection.Add(Constraint constraint, Boolean
addUniqueWhenAddingForeign) +475
System.Data.DataSetRelationCollection.AddCore(DataRelation relation)
+1034
System.Data.DataRelationCollection.Add(DataRelation relation) +142
reports.Page_Load(Object sender, EventArgs e) in
E:\hhsinternal\tickets\reports.aspx.vb:30
System.Web.UI.Control.OnLoad(EventArgs e) +102
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1064

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50215.44; ASP.NET
Version:2.0.50215.44

================================================

This doesn't make any sense to me. I double checked the two tables and I
just don't see any reason why this would be. Here is my code thus far
(reports.aspx.vb); watch for word wrap:

================================================

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
If Not Page.IsPostBack Then
Dim strConnection As String =
ConfigurationManager.AppSettings("tickets")
Dim objConnection As New SqlConnection(strConnection)

Dim parentCol As DataColumn
Dim childCol As DataColumn
Dim relation1 As DataRelation

Dim strSQL As String = "SELECT * FROM TTickets WHERE resolve <>
1"
Dim strSQL1 As String = "SELECT * FROM TNotes"

Dim objAdapter As New SqlDataAdapter(strSQL, objConnection)
Dim objAdapter1 As New SqlDataAdapter(strSQL1, objConnection)
Dim objDataSet As New DataSet()

objAdapter.Fill(objDataSet, "ticketstable")
objAdapter1.Fill(objDataSet, "notestable")

parentCol = objDataSet.Tables("ticketstable").Columns("PK")
childCol = objDataSet.Tables("notestable").Columns("FK")

relation1 = New DataRelation("RelationA", parentCol, childCol)
objDataSet.Relations.Add(relation1)

Dim strResults As String = ""
Dim r As DataRow

For Each r In objDataSet.Tables("ticketstable").Rows
strResults += "<table border=""3"" align=""center""
cellspacing=""0"" cellpadding=""2"" width=""90%"" bordercolor=""black""
bgcolor=""#e7e7e7"">"
strResults += "<tr bgcolor=" &
r.Item("prioritycolor").ToString & "><td>Priority: " & r.Item("prioritynumber").ToString & "</td>" & _
"<td bgcolor=""black"">ID #: "
& r.Item("PK").ToString & "</td><td>Subject: " & r.Item("subject").ToString & "</td>" & _
"<td>Date &
Time: " & r.Item("dtemp").ToString & "</td><td>Emp Name: " & r.Item("name").ToString & "</td>" & _
"<td>AD
Username: " & r.Item("unemp").ToString & "</td><td>IP
Address: " & r.Item("ip").ToString & "</td></tr><tr
bgcolor=" & r.Item("prioritycolor").ToString & ">" & _
"<td colspan=""7"">Description of
Problem: " & r.Item("descript").ToString & "</td></tr><tr><td>Assign To: " & r.Item("assign").ToString & "</td>" & _
"<td>Assign By: " & r.Item("unass").ToString & "</td><td>Assign
Date/Time: " & r.Item("dtass").ToString & "</td>" & _
"<td>Resolved
By: " & r.Item("unresolve").ToString & "</td><td>Resolve
Date/Time: " & r.Item("dtresolve").ToString & "</td>" & _
"<td>Approved
By: " & r.Item("unapp").ToString & "</td><td>Approve
Date/Time: " & r.Item("dtapp").ToString & "</td></tr>" & _
"<tr height=""1""><td colspan=""7""
bgcolor=""silver""><span
style=""font-family:arial;font-size:10pt;color

urple;""><center>- - -
Information System Notes - - -</center></td></tr>"

Dim childr() As DataRow
childr = r.GetChildRows("RelationA")

Dim theChildRow As DataRow

For Each theChildRow In childr
strResults += "<tr height=""6""><td colspan=""7""
bgcolor=""black""></td></tr>"
strResults += "<tr><td colspan=""4"">Posted By: " & theChildRow.Item("unnotes").ToString & "</td>"
strResults += "<td colspan=""3"">Posted
Date/Time: " & theChildRow.Item("dtnotes").ToString &
"</td></tr>"
strResults += "<tr><td colspan=""7"">Comments/Notes: " & theChildRow.Item("notes").ToString & "</td></tr>"

Next
strResults += "</table> "
Next
testgrid.InnerHtml = strResults
End If

End Sub

================================================

Thanks in advance for the help,
Jim

Bruce Barker · Aug 23, 2005

if you have a where clause on TTickets, so that you do not load all, but
load all TNotes, hten you will have TNotes rows with no matching TTickets
parent row. you need to use the same where clause on the TNotes.

-- bruce (sqlwork.com)

Jim in Arizona said:
This doesn't make any sense to me. I'm hoping some SQL guru out there
knows the answer.

I'm making a help desk type database system. There's only two tables, one
called TTickets and the other called TNotes. The Primay Key of TTickets is
a one to Many to the Foreign Key of the TNotes table.

In my VB code, I create a relation between the two tables so I can display
several notes (from TNotes) with a single entry in the TTickets table. So
far, all was going well until I changed my SQL string. Originally, the SQL
string for the TTickets table was "SELECT * FROM TTickets". I got to a
point to where I was going to begin designing my queries so I changed the
SQL string to "SELECT * FROM TTickets WHERE resolve = 1". The resolve
column is a bit field (on or off). This worked just fine too. It's when I
changed it to "SELECT * FROM TTickets WHERE resolve = 0" that I got an
exception. I don't know what corresponding parent values it is talking
about because there aren't any (that I know of). This the error:

================================================

This constraint cannot be enabled as not all values have corresponding
parent values.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.ArgumentException: This constraint cannot be
enabled as not all values have corresponding parent values.

Source Error:

Line 28:
Line 29: relation1 = New DataRelation("RelationA", parentCol,
childCol)
Line 30: objDataSet.Relations.Add(relation1)
Line 31:
Line 32: Dim strResults As String = ""

Source File: E:\hhsinternal\tickets\reports.aspx.vb Line: 30

Stack Trace:

[ArgumentException: This constraint cannot be enabled as not all values
have corresponding parent values.]
System.Data.ConstraintCollection.Add(Constraint constraint, Boolean
addUniqueWhenAddingForeign) +475
System.Data.DataSetRelationCollection.AddCore(DataRelation relation)
+1034
System.Data.DataRelationCollection.Add(DataRelation relation) +142
reports.Page_Load(Object sender, EventArgs e) in
E:\hhsinternal\tickets\reports.aspx.vb:30
System.Web.UI.Control.OnLoad(EventArgs e) +102
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1064

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50215.44;
ASP.NET Version:2.0.50215.44

================================================

This doesn't make any sense to me. I double checked the two tables and I
just don't see any reason why this would be. Here is my code thus far
(reports.aspx.vb); watch for word wrap:

================================================

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim strConnection As String =
ConfigurationManager.AppSettings("tickets")
Dim objConnection As New SqlConnection(strConnection)

Dim parentCol As DataColumn
Dim childCol As DataColumn
Dim relation1 As DataRelation

Dim strSQL As String = "SELECT * FROM TTickets WHERE resolve <>
1"
Dim strSQL1 As String = "SELECT * FROM TNotes"

Dim objAdapter As New SqlDataAdapter(strSQL, objConnection)
Dim objAdapter1 As New SqlDataAdapter(strSQL1, objConnection)
Dim objDataSet As New DataSet()

objAdapter.Fill(objDataSet, "ticketstable")
objAdapter1.Fill(objDataSet, "notestable")

parentCol = objDataSet.Tables("ticketstable").Columns("PK")
childCol = objDataSet.Tables("notestable").Columns("FK")

relation1 = New DataRelation("RelationA", parentCol, childCol)
objDataSet.Relations.Add(relation1)

Dim strResults As String = ""
Dim r As DataRow

For Each r In objDataSet.Tables("ticketstable").Rows
strResults += "<table border=""3"" align=""center""
cellspacing=""0"" cellpadding=""2"" width=""90%"" bordercolor=""black""
bgcolor=""#e7e7e7"">"
strResults += "<tr bgcolor=" &
r.Item("prioritycolor").ToString & "><td>Priority: " & r.Item("prioritynumber").ToString & "</td>" & _
"<td bgcolor=""black"">ID #: " & r.Item("PK").ToString & "</td><td>Subject: " & r.Item("subject").ToString & "</td>" & _
"<td>Date &
Time: " & r.Item("dtemp").ToString & "</td><td>Emp
Name: " & r.Item("name").ToString & "</td>" & _
"<td>AD
Username: " & r.Item("unemp").ToString & "</td><td>IP
Address: " & r.Item("ip").ToString & "</td></tr><tr
bgcolor=" & r.Item("prioritycolor").ToString & ">" & _
"<td colspan=""7"">Description of
Problem: " & r.Item("descript").ToString &
"</td></tr><tr><td>Assign
To: " & r.Item("assign").ToString & "</td>" & _
"<td>Assign
By: " & r.Item("unass").ToString & "</td><td>Assign
Date/Time: " & r.Item("dtass").ToString & "</td>" & _
"<td>Resolved
By: " & r.Item("unresolve").ToString & "</td><td>Resolve
Date/Time: " & r.Item("dtresolve").ToString & "</td>" & _
"<td>Approved
By: " & r.Item("unapp").ToString & "</td><td>Approve
Date/Time: " & r.Item("dtapp").ToString & "</td></tr>" & _
"<tr height=""1""><td colspan=""7""
bgcolor=""silver""><center>- - -
Information System Notes - - -</center></td></tr>"

Dim childr() As DataRow
childr = r.GetChildRows("RelationA")

Dim theChildRow As DataRow

For Each theChildRow In childr
strResults += "<tr height=""6""><td colspan=""7""
bgcolor=""black""></td></tr>"
strResults += "<tr><td colspan=""4"">Posted
By: " & theChildRow.Item("unnotes").ToString & "</td>"
strResults += "<td colspan=""3"">Posted
Date/Time: " & theChildRow.Item("dtnotes").ToString &
"</td></tr>"
strResults += "<tr><td colspan=""7"">Comments/Notes: " & theChildRow.Item("notes").ToString & "</td></tr>"

Next
strResults += "</table> "
Next
testgrid.InnerHtml = strResults
End If

End Sub

================================================

Thanks in advance for the help,
Jim

Jim in Arizona · Aug 23, 2005

Bruce Barker said:
if you have a where clause on TTickets, so that you do not load all, but
load all TNotes, hten you will have TNotes rows with no matching TTickets
parent row. you need to use the same where clause on the TNotes.

-- bruce (sqlwork.com)

If I use the same WHERE clause on the TNotes statement, it will not work
becuase there is no resolve field in the TNotes table. So, how can I get
around this problem?

I thought about using a Left Outer Join for the TNotes SQL statement but
that doesn't help me either; not that I could tell anyway:

SELECT dbo.TNotes.notes AS Expr1, dbo.TNotes.unnotes AS Expr2,
dbo.TNotes.dtnotes AS Expr3, dbo.TNotes.FK AS Expr4, dbo.TTickets.*
FROM dbo.TTickets LEFT OUTER JOIN
dbo.TNotes ON dbo.TTickets.PK = dbo.TNotes.FK

Although this still displays the records ok, when I add a WHERE resolve = 0
to the end of the statement, I get the same error.

Since this has to do with the relationship type, I was thinking that it has
to do with the relationship coding itself, ie:

parentCol = objDataSet.Tables("ticketstable").Columns("PK")
childCol = objDataSet.Tables("notestable").Columns("FK")

relation1 = New DataRelation("RelationA", parentCol, childCol)
objDataSet.Relations.Add(relation1)

I'm thinking that this creates a standard one to many relationship instead
of a relationship that will allow us to select the records from our Parent
table (TTickets) where there are no corrosponding records in the Child Table
(TNotes), which is what we need.

Help!

Thanks,
Jim

Bruce Barker · Aug 24, 2005

you just select the notes that match the tickets. you don't wnat an outer
join becuase thats what you have now (notes without tickets).

select * from TTickets where Resolve =1

select * from TNotes
where FK in (select PK from TTickets where Resolve =1)

-- bruce (sqlwork.com)

Jim in Arizona · Aug 24, 2005

Bruce Barker said:
you just select the notes that match the tickets. you don't wnat an outer
join becuase thats what you have now (notes without tickets).

select * from TTickets where Resolve =1

select * from TNotes
where FK in (select PK from TTickets where Resolve =1)

-- bruce (sqlwork.com)

Wow! That easy huh. I can imagine that I'll have to apply this same concept
to many of the queries I'll be running.
I need to keep reading my SQL books I see. Now, if only I had time to keep
reading them!

SELECT Time.* from MyLife WHERE OnlyFun = 1

Thanks Bruce.

Jim

ASPNET 2.0: dynamic buttons	0	Jan 31, 2006
Angularjs newbie - second JSON datasource does not display	0	May 18, 2022
Filter table rows based on multiple checkboxes value	2	Jan 13, 2023
I need help fixing my website	2	Oct 15, 2023
Button to call another Aspx page	2	Oct 16, 2008
object of a from are not recognized in the code behind	0	Dec 13, 2007
Problems with Drop Down List Control	0	Nov 20, 2006
action_page.php form	2	Oct 25, 2020

not all values have corresponding parent values

Jim in Arizona

Bruce Barker

Jim in Arizona

Bruce Barker

Jim in Arizona

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads