Help with the Following Logic

[vuyiswamb]

Good Day all

it is Probably a long day, i cant think Straight now.

i have a table that looks like this

Nodeid   Parent   Description     Type      Curr
==========================================================================================
89	 NULL     Compulsory        1	     10
90	  89	  B1052	            3	     10
2820      89	  One of	    2	     10

4113	89	  B1061	            3	     10
2821	2820	  B1054             3	     10
2822	2820	  B1055             3	     10

Now the Red Record needs to be on top of "One of" because the Parent is "Compulsary" with the "Parent" = 89. Now Even "One of " has the Same Parent as the red record but if its a "One of " and they have the same parent, then "One of " must always be below the record. What i mean is that if there is a record with a same parent but different Type , the one that has type 2 should go below the one that has type 3 in my query. here is my query

select distinct nP.id, nP.NodeID, nP.parent, nP.Description, nRef.ID refParent, np.type 
 from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID	-- look up the reference id of the parent
order by  refParent,nP.id,np.type desc

Thank you

[vuyiswamb]

Thanks now i have Changed my Code to look like this and its working perfect in SQl when i test it.

select distinct nP.id, nP.NodeID, nP.parent, nP.Description, nRef.ID refParent, np.type 
from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID	-- look up the reference id of the parent
order by  np.Parent,np.Description,np.NodeID

And it returns

Nodeid   Parent   Description     Type      Curr
==========================================================================================
89	 NULL     Compulsory        1	     10
90	  89	  B1052	            3	     10

4113	89	  B1061	            3	     10
2820      89	  One of	    2	     10
2821	2820	  B1054             3	     10
2822	2820	  B1055             3	     10

and its Good thanks.

am binding the values to a Tree Control like this

public void PopulateTreeFromCurr(int currID)
    {    
        // CurrStructDataSource calls sp_Traverse_Tree which returns an inordered list of nodes for a tree
        IEnumerable result = CurrStructDataSource.Select(DataSourceSelectArguments.Empty);
        // ID, Parent, Child, Description, Level, i
        int Parent, Child;
        CurriculumTreeView.Nodes.Clear();
        
        ArrayList CurrNodes = new ArrayList();

        if (result != null)
        {
            // an ordered set of nodes is given. parent always before current node except for root            
            foreach (System.Data.DataRowView row in result)
            {
                TreeNode newnode = new TreeNode(row["Description"].ToString(), row["NodeID"].ToString());
                CurrNodes.Add(newnode); // should setup a lookup between the id given by the ArrayList and that of the node
                // as we add the nodes, we can set up the hierarchy
                if (row["refParent"].ToString() == "")
                {   // don't have to add the root node to another node-it is the root
                }
                else
                {
                    Parent = Convert.ToInt32(row["Parent"]);
                    Child = Convert.ToInt32(row["ID"]);

                    TreeNode ParentNode = new TreeNode();
                    TreeNode ChildNode = new TreeNode();
                    [B]ParentNode = (TreeNode)CurrNodes[Parent];[/B]
                    ChildNode = (TreeNode)CurrNodes[Child];
                    ParentNode.ChildNodes.Add(ChildNode);
                    CurrNodes[Parent] = ParentNode;
                }
            }
            if (CurrNodes.Count > 0)
            {
                // Add the first root compulsory which has all nodes attached to it
                CurriculumTreeView.Nodes.Add((TreeNode)CurrNodes[0]);
                CurriculumTreeView.ExpandAll();
            }
        }
    }

in the code i get an Error in the bolded red part line

ParentNode = (TreeNode)CurrNodes[Parent];

and it says

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

[sknake]

You could do something like this with your query:

Select *
From Table
Order By (Case When Parent Is Not Null Then Parent Else NodeId), NodeId

You need to handle DBNull.Value in your C# application. You cannot convert a database null value to int 32.

if (row["Parent"] == DBNull.Value)
  Parent = 0; //no parent
else
  Parent = Convert.ToInt32(row["Parent"]);

[vuyiswamb]

This Query works

select distinct nP.id, nP.NodeID, nP.parent, nP.Description, nRef.ID refParent 
from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID	-- look up the reference id of the parent
order by nP.id, refParent

but it Brings undesired results , but this one in the treeview control

And this Query from the SQL side gives me Good REsults but it give me an error of indexoutofbounds in my code

select np.id , nP.NodeID, nP.parent, nP.Description, nRef.ID refParent 
from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID	-- look up the reference id of the parent
order by refParent,np.Description

Can you help me arrange the Order by

[sknake]

Can you upload a sample project with your tree list population code and an SQL Query with the CREATE TABLE and INSERT INTO statements to get me test data for this?