not all values have corresponding parent values

J

Jim in Arizona

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><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Priority:</b><br
/>" & r.Item("prioritynumber").ToString & "</span></td>" & _
"<td bgcolor=""black""><span
style=""font-family:arial;font-size:10pt;color:white;""><b>ID #:</b><br />"
& r.Item("PK").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Subject:</b><br
/>" & r.Item("subject").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Date &
Time:</b><br />" & r.Item("dtemp").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Emp Name:</b><br
/>" & r.Item("name").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>AD
Username:</b><br />" & r.Item("unemp").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>IP
Address:</b><br />" & r.Item("ip").ToString & "</span></td></tr><tr
bgcolor=" & r.Item("prioritycolor").ToString & ">" & _
"<td colspan=""7""><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Description of
Problem:</b><br />" & r.Item("descript").ToString & "</td></tr><tr><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Assign To:</b><br
/>" & r.Item("assign").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Assign By:</b><br
/>" & r.Item("unass").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Assign
Date/Time:</b><br />" & r.Item("dtass").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Resolved
By:</b><br />" & r.Item("unresolve").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Resolve
Date/Time:</b><br />" & r.Item("dtresolve").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Approved
By:</b><br />" & r.Item("unapp").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Approve
Date/Time:</b><br />" & r.Item("dtapp").ToString & "</span></td></tr>" & _
"<tr height=""1""><td colspan=""7""
bgcolor=""silver""><span
style=""font-family:arial;font-size:10pt;color:purple;""><center><b>- - -
Information System Notes - - -</b></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""><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Posted By:</b><br
/>" & theChildRow.Item("unnotes").ToString & "</span></td>"
strResults += "<td colspan=""3""><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Posted
Date/Time:</b><br />" & theChildRow.Item("dtnotes").ToString &
"</span></td></tr>"
strResults += "<tr><td colspan=""7""><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Comments/Notes:</b><br
/>" & theChildRow.Item("notes").ToString & "</span></td></tr>"

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

End Sub

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

Thanks in advance for the help,
Jim
 
B

Bruce Barker

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><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Priority:</b><br
/>" & r.Item("prioritynumber").ToString & "</span></td>" & _
"<td bgcolor=""black""><span
style=""font-family:arial;font-size:10pt;color:white;""><b>ID #:</b><br
/>" & r.Item("PK").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Subject:</b><br
/>" & r.Item("subject").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Date &
Time:</b><br />" & r.Item("dtemp").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Emp
Name:</b><br />" & r.Item("name").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>AD
Username:</b><br />" & r.Item("unemp").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>IP
Address:</b><br />" & r.Item("ip").ToString & "</span></td></tr><tr
bgcolor=" & r.Item("prioritycolor").ToString & ">" & _
"<td colspan=""7""><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Description of
Problem:</b><br />" & r.Item("descript").ToString &
"</td></tr><tr><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Assign
To:</b><br />" & r.Item("assign").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Assign
By:</b><br />" & r.Item("unass").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Assign
Date/Time:</b><br />" & r.Item("dtass").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Resolved
By:</b><br />" & r.Item("unresolve").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Resolve
Date/Time:</b><br />" & r.Item("dtresolve").ToString & "</span></td>" & _
"<td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Approved
By:</b><br />" & r.Item("unapp").ToString & "</span></td><td><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Approve
Date/Time:</b><br />" & r.Item("dtapp").ToString & "</span></td></tr>" & _
"<tr height=""1""><td colspan=""7""
bgcolor=""silver""><span
style=""font-family:arial;font-size:10pt;color:purple;""><center><b>- - -
Information System Notes - - -</b></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""><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Posted
By:</b><br />" & theChildRow.Item("unnotes").ToString & "</span></td>"
strResults += "<td colspan=""3""><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Posted
Date/Time:</b><br />" & theChildRow.Item("dtnotes").ToString &
"</span></td></tr>"
strResults += "<tr><td colspan=""7""><span
style=""font-family:arial;font-size:10pt;color:black;""><b>Comments/Notes:</b><br
/>" & theChildRow.Item("notes").ToString & "</span></td></tr>"

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

End Sub

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

Thanks in advance for the help,
Jim
 
J

Jim in Arizona

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
 
B

Bruce Barker

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)
 
J

Jim in Arizona

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top