473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

not all values have corresponding parent values

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(Data Relation relation)
+1034
System.Data.DataRelationCollection.Add(DataRelatio n 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

Nov 19 '05 #1
4 7622
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" <ti*******@hotmail.com> wrote in message
news:Ox**************@TK2MSFTNGP09.phx.gbl...
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(Data Relation relation)
+1034
System.Data.DataRelationCollection.Add(DataRelatio n 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


Nov 19 '05 #2
"Bruce Barker" <br******************@safeco.com> wrote in message
news:ek**************@TK2MSFTNGP12.phx.gbl...
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
Nov 19 '05 #3
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" <ti*******@hotmail.com> wrote in message
news:u1*************@TK2MSFTNGP12.phx.gbl...
"Bruce Barker" <br******************@safeco.com> wrote in message
news:ek**************@TK2MSFTNGP12.phx.gbl...
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

Nov 19 '05 #4
"Bruce Barker" <br******************@safeco.com> wrote in message
news:OL*************@TK2MSFTNGP15.phx.gbl...
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
Nov 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: cirillo_curiosone | last post by:
Hi, i'm new to javascript. I started studing it on the web few weeks ago, but still haven't been able to solve one big problem: HOT TO PASS VALUES FROM A SCRIPT VARIABLE TO A CHILD HTML...
3
by: Tillman Erb | last post by:
Let's assume a database with a parent table and a child table in a one-to-many relationship, respectively. I have a DataList control on page P that displays values from the parent table. Each row...
2
by: | last post by:
I've defined an ObjectDataSource against a dataset, and I can bind the ObjectDataSource's members to data controls. I'm wondering how to take the values in an ObjectDataSource and...
1
by: KenRoy | last post by:
I have a datagrid with a child relationship. If I use the default TableStyle everything works fine and when I drill down into a child, the ParentRow shows in the header with the column names and...
1
by: | last post by:
Hi, 1st, I did a search and could not find any info on this, the Google results were good, but I'm still have issues...So any help will be great. I have a frame page, which contains 3 frames...
6
by: kath | last post by:
hi everyone......... I have a task, I have fragmented the task into subtask. I have planned to create a class to each subclass and one parent class to handle the sub tasks. Each subclass are...
5
by: Bubba | last post by:
I have a dynamic pulldown list (ASP with javascript) that when a user picks a state, the corresponding counties for that state appear in a dynamic second pulldown list. When I submit the form, the...
2
by: GTalbot | last post by:
Hello fellow comp.infosystems.www.authoring.stylesheets colleagues, Imagine this situation: #grand-parent-abs-pos { height: 400px; position: absolute; width: 600px; }
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.