Self Referencing Result Not Clean

21 Feb 2006 8:19 PM

Marty Cruise

I am trying to load a table with a self relation, but can't seem to figure
out how to load only top-level parent rows at the top of the data hierarchy.
Instead, all rows are coming across to the top level of the hierarchy, as
well as their children (per the relation--this part is good). I found an
example elsewhere, but it wasn't exactly what I was hoping for. Can anyone
recommend how to accomplish getting a clean result?

        Dim myDS As New Data.DataSet
        Dim mySelect As String = Nothing

        mySelect = "SELECT * FROM vwWOBuilds WHERE ID = " & ID & " ORDER BY
ID"

        Dim myDA As New System.Data.SqlClient.SqlDataAdapter(mySelect,
myApp.Server.Provider2)
        myDA.Fill(myDS)

        Dim myR As New DataRelation("SelfReferencing",
myDS.Tables(0).Columns("ID"), myDS.Tables(0).Columns("ParentBuildID"), False)

        myDS.Relations.Add(myR)

        Return myDS.Tables(0)

27 Feb 2006 3:03 AM

Robbe Morris [C# MVP]

Wouldn't your query be by ParentID = null or ParentID = RecordID
depending on how you have your hierarchy set up?

--
Robbe Morris - 2004-2006 Microsoft MVP C#
http://www.eggheadcafe.com/forums/merit.asp

Show quote

"Marty Cruise" <MartyCru***@discussions.microsoft.com> wrote in message
news:CD9A3D95-5F83-4ED5-B825-1EAE2A136501@microsoft.com...
>I am trying to load a table with a self relation, but can't seem to figure
> out how to load only top-level parent rows at the top of the data
> hierarchy.
> Instead, all rows are coming across to the top level of the hierarchy, as
> well as their children (per the relation--this part is good). I found an
> example elsewhere, but it wasn't exactly what I was hoping for. Can
> anyone
> recommend how to accomplish getting a clean result?
>
>        Dim myDS As New Data.DataSet
>        Dim mySelect As String = Nothing
>
>        mySelect = "SELECT * FROM vwWOBuilds WHERE ID = " & ID & " ORDER BY
> ID"
>
>        Dim myDA As New System.Data.SqlClient.SqlDataAdapter(mySelect,
> myApp.Server.Provider2)
>        myDA.Fill(myDS)
>
>        Dim myR As New DataRelation("SelfReferencing",
> myDS.Tables(0).Columns("ID"), myDS.Tables(0).Columns("ParentBuildID"),
> False)
>
>        myDS.Relations.Add(myR)
>
>        Return myDS.Tables(0)