The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Technique for specific data layout


I'm trying to achieve the following data layout in

The data is from a self referencing table:

id name parent
1 Spain -1
2 Costa Blanca 1
3 Costa Brava 1

I need to display a count next to each parent/child showing the number of child items.

I was considering a repeater but would appreciate any suggestions on how best to retrieve the data and display it like shown.

Many Thanks
Wednesday, June 14, 2006
Do you only need the number of _direct_ children or do you have a multi-level hierarchy where you want to count the grandchildren as well?


1 xxx -1
2 yyy 1
3 zzz 1
4 aaa 2
5 bbb 2
6 jjj 5

What do you expect?
a) xxx(5) yyy(3) aaa(0) bbb(1) zzz(0) jjj(0)
b) xxx(2) yyy(2) zzz(0) aaa(0) bbb(1) jjj(0)

Case a) should be solvable with a subquery where you count the references (parent) to your actual node (id).

Case b) is a bit more difficult ... atm i can only think of something recursive here ...
Wednesday, June 14, 2006
Oops. Case a) with the grandchildren is the recursive and case b) the simpler one.
Wednesday, June 14, 2006
Christopher Wells Send private email
Wednesday, June 14, 2006

Thanks for this. I have a self referencing table like:

id    name      parentid
1    Cyprus      -1
2    Paphos      1
3    Limassol    1
4    USA        -1
5    Florida    4
6    New York    4

And I need to achieve the following layout:

Cyprus (2)          USA (2)
  Limassol (5)      New York(18)
  Paphos (8)        Florida (22)

(The Child items under Limassol, Paphos etc are in a seperate 'properties' table.)

I need it to display 3 columns by however many rows are required. I've been looking at the datalist as that will allow me to display the 3 across, though getting the data from the sql db in the correct format is proving tricky.

Any suggestions greatly appreciated.

Thursday, June 15, 2006
Ok...this is prolly chock full of errors and bugs...but I will leave you to work it out.  I started out trying to explain it..ended up coding it...shoot me an email with questions

        'I did not add any try/catch/finally/ block to the db calls
        'You WILL want to add that stuff just doing a quick and dirty
        'mostly functional example of the datareleation functionality between
        'datasets in .NET
        'create your connection
        Dim cString As SqlConnection = New SqlConnection("YourConnectionDataHere")
        'dataset to hold all your data
        Dim ds As New DataSet()
        'command text strings for queries
        Dim ctParent, ctChild As String

        ' your query will need to pull in the data in parens...since I don't
        ' have the full I can't help you there.
        'Parent data query...your example showed parents as having parentID of -1
        ctParent = "Select id, name countforparens from yourtablename where parentid <= 0"
        'child data query...your example showed children as having parentid of > 0
        ctChild = "Select id, name, parentid, countforparens from yourtablename where parentid > 0"

        'instantiate data adapters
        Dim daParentData As SqlDataAdapter = New SqlDataAdapter(ctParent, cString)
        Dim daChildData As SqlDataAdapter = New SqlDataAdapter(ctChild, cString)

        'open connection

        ' Fill the DataSet with schema information
        daParentData.MissingSchemaAction = MissingSchemaAction.AddWithKey
        daChildData.MissingSchemaAction = MissingSchemaAction.AddWithKey

        'fill the tables
        daParentData.Fill(ds, "ParentData")
        daChildData.Fill(ds, "ChildData")

        'dispose dataadapters
        'close connection /  /dispose connection
        'drHappyFamily defines the relationship between your child data
        'and your parent data...allowing you to iterate through the child data
        'related to the parent data at will
        Dim drHappyFamily As DataRelation
        drHappyFamily = ds.Relations.Add("WhosYourDaddy", _
                        ds.Tables("ParentData").Columns("ID"), _

        Dim dvmManager As DataViewManager = New DataViewManager(ds)

        'add in the parent row you wish to sort by...maybe a good idea
        'to add a sort column
        dvmManager.DataViewSettings("ParentData").Sort = "Your Sort Coloumn Here"

        Dim drParent As DataRow
        Dim drChild As DataRow
        'holds the value to tell you the current column #
        Dim intColumnCount As Integer = 1
        'holds the value for the maximum column width...can change
        'this arbitrarely
        Dim intMaxColumnCountWidth As Integer = 3
        Dim strTableDataRender As String = ""
        'the vbcrlf and vbtab are to format the html if you were to view source
        'from the rendered page for readability
        'add the table opening tag
        strTableDataRender = "<table>" & vbCrLf
        'you will start out iterating through the rows of the parent data
        'then within each iterate row of the parent data you will
        'iterate through each child row that is related as a result
        'of the relationship you established in drHappyFamily
        For Each drParent In ds.Tables("ParentData").Rows

            'add opening row tag
            If intColumnCount = 1 Then
                'adding one tab to indent past table
                strTableDataRender += vbTab & "<tr>" & vbCrLf
            End If
            'render column open tag - adding two tabs to indent past tr
            strTableDataRender += vbTab & vbTab & "<td>" & vbCrLf
            'adding three tabs to indent past td
            strTableDataRender += vbTab & vbTab & vbTab
            'rendering the parent data stuff
            strTableDataRender += drParent("Name") & " (" & drParent("NameCount") & ")<br>" & vbCrLf
            'iterate through the child rows related to the parent iteration
            ' you are currently in
            For Each drChild In drParent.GetChildRows(drHappyFamily)
                'adding three tabs to indent past td
                strTableDataRender += vbTab & vbTab & vbTab
                'rendering the child data stuff
                strTableDataRender += vbTab & vbTab & vbTab
                strTableDataRender += drChild("Name") & " (" & drChild("NameCount") & ")<br>" & vbCrLf

            'render column closing tag
            'space tag is to make the last <br> in the list create an empty line for
            'the line space in your example
            strTableDataRender += "&nbsp;" & vbCrLf & vbTab & vbTab & "</td>" & vbCrLf
            'for closing row tag and intColumnCount management
            If intColumnCount = intMaxColumnCountWidth Then
                strTableDataRender += vbTab & "</tr>" & vbCrLf
                intColumnCount = 1
                intColumnCount += 1
            End If
        'add the table closing tag
        strTableDataRender += "</table>"
        'you can either assign this strTableDataReader to a control or render
        'on the page with a <%=strTableData%>
Dan Hirsch Send private email
Sunday, June 18, 2006

delete one of these in the child control iteration ..added one too many

"strTableDataRender += vbTab & vbTab & vbTab"


"'you can either assign this strTableDataReader"

Should be

"'you can either assign this strTableDataRender"
Dan Hirsch Send private email
Sunday, June 18, 2006
Wow...I go through all that work..and nobody responds?
Dan Hirsch Send private email
Thursday, June 22, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz