• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2982
  • Last Modified:

Loading TreeView Recursively from Sql Database Tbl Vb.net

 I am having problems loading a TreeView from an SQL DataTable. I managed to get the Save TreeView working.
      
         I found some code in "C" that is supposed to work. You will notice it is included with comments.
         I have convert the C code to Vb.   I have checked the LoadTree subroutine and it seems to be working     correctly.
        
         I am having problems with the LoadRecursive Subroutine.  I am relatively new to ADO.net so it may be
         a small problem.  Below is the structure of the database table I am loading the TreeView from.

                   Even though the code below is an eyefull the actual subroutine I am having trouble with is quite small.
        
        
             I  have been trying to get this to work for quite a while and cannot get it working correctly so far.
                 This may be easy for an expert... The points are high because I urgently need to get this resolved.
        
        I would greatly appreciate help.
        
        Thanks,
        
        brad
        
        '===================================================================      
      SQL Datatable TblTreeView1
      fields    
      -------
                TvNodeId     Int
      TvParentId  Int
      TvNodeName  string
   ===================================================================
    '  Sub LoadTree
    ==================================================================
    '  Following is the C Language version that I converted to Visual Basic
   ==================================================================
    '
    '  private void LoadTree(TreeView tree)
    '  {
    ' // First of all, clear the tree to be sure, you might pass a TreeNode here to load your
    '// tree into an existing TreeNode
    'tree.Nodes.Clear();
    ' string sql = "select * from TREETABLE order by parent_id, node_id";
    '  //
    '  // Use a DataAdapter and DataSet here, I assume you have a DataSet named ds
    '  //
    '  foreach(DataRow row in ds.Tables["TREETABLE"].Rows)
    '   {
    '       int myId = Convert.ToInt32(row["node_id"]);
    '       int parentId = Convert.ToInt32(row["parent_id"]);
    '      string name = Convert.ToString(row["name"]);
    '     int permission = Convert.ToInt32(row["permission"]);
    '     if( !CheckPermission(permission) )
    '     {
    '         // Not valid, current user has no permission to see this node, skip it...
    '         continue;
    '     }
    '     DataRow[] rootNodes = ds.Tables[0].Select("parent_id = 0");
    '     foreach(DataRow row in rootNodes)
    '     {
    '         TreeNode node = new TreeNode(name);
    '         node.Tag = myId;
    '        tree.Nodes.Add(node);
    '        LoadRecursive(node, ds.Tables[0]);
    '      }
    '     }
    '  }
   ==================================================================
    '  Following is the Visual Basic Version of  the code for LoadTree
  ==================================================================
    Private Sub LoadTree()
        '   First of all, clear the tree to be sure, you might pass a TreeNode here to load your
        '   tree into an existing TreeNode

        TreeView1.Nodes.Clear()
        Dim ConnStr As String

        ConnStr = ModMain.GpDcConnStr
        Dim cn As New SqlClient.SqlConnection(ConnStr)

        Dim strSQL As String
        strSQL = " SELECT * FROM tblTreeView1 "
        strSQL += " ORDER BY TvParentId, TvNodeId;"

        Dim ds As New DataSet
        Dim da As New SqlClient.SqlDataAdapter(strSQL, cn)
        Dim tbl As New DataTable
        da.Fill(tbl)

        Dim dRow As DataRow
        tbl.TableName = "Tv1"


        Dim xMyId As Integer
        Dim xParentId As Integer
        Dim xNodeName As String
        Dim xPermission As Integer
        Dim xNode As New TreeNode

        For Each dRow In tbl.Rows
            xMyId = dRow("TvNodeId")
            xParentId = dRow("TvParentId")
            xNodeName = dRow("TvNodeName")
            xPermission = dRow("TvPermission")
            Call LoadRecursive(xParentId, tbl)
        Next
    End Sub

 ' ==================================================================== LoadRecursive
    ' ===================================================================
    ' Following is the C Language source version of the code I converted to Vb.net
    '
    '    private void LoadRecursive(TreeNode parentNode, DataTable table)
    '    {
    '    DataRow[] rows = table.Select("parent_id = " + ((int)parentNode.Tag));
    '    foreach(DataRow row in rows)
    '     {
    '        TreeNode node = new TreeNode((string)row["name"]);
    '        node.Tag = row["id"];
    '        parentNode.Nodes.Add(node);
    '        LoadRecursive(node, table);
    '      }
    '    }
 ========================================================
    '  Following is the Visual Basic Version
 ========================================================
    Private Sub LoadRecursive(ByVal xParentNode As Integer, ByVal dt As DataTable)
        Dim ConnStr As String
        ConnStr = ModMain.GpDcConnStr

        Dim cn As New SqlClient.SqlConnection(ConnStr)
        Dim strSQL As String

        strSQL = " SELECT * FROM tblTreeView1 "
        strSQL += " WHERE TvParentId = " & Str(xParentNode) & ";"

        Dim da As New SqlClient.SqlDataAdapter(strSQL, cn)
        Dim ds As New DataSet()
        da.Fill(Ds, "MyTreeView1")
        cn.Close()

        Dim xNodeId As TreeNode
        Dim dRow As DataRow
        For Each dRow In Ds.Tables("MyTreeView1").Rows
            Dim xNodeName As New TreeNode(dRow("TvNodeName"))
            'xNode.Tag = dRow("TvNodeId")
            xNodeId = dRow("TvNodeId")
            TreeView1.SelectedNode.Nodes.Add(xNodeId)
            xNodeId.Tag = xNodeId
            LoadRecursive(xNodeId.Tag, dt)
        Next
    End Sub
0
bulrick
Asked:
bulrick
  • 2
  • 2
1 Solution
 
junglerover77Commented:
I do think that code is a little strange. In the original C code, we can see that it assumes all the root nodes have ParentID=0. If so, the code in function LoadTree() is useless. Just change sub LoadRecursive like this:

    Private Sub LoadRecursive(ByVal xParentNodeID As Integer)
        Dim ConnStr As String
        ConnStr = ModMain.GpDcConnStr

        Dim cn As New SqlClient.SqlConnection(ConnStr)
        Dim strSQL As String

        strSQL = " SELECT * FROM tblTreeView1 "
        strSQL += " WHERE TvParentId = " & Str(xParentNodeID) & ";"

        Dim da As New SqlClient.SqlDataAdapter(strSQL, cn)
        Dim ds As New DataSet()
        da.Fill(ds, "MyTreeView1")
        cn.Close()

        Dim dRow As DataRow
        Dim xNodeText As String
        Dim xNodeID As Integer

        For Each dRow In ds.Tables("MyTreeView1").Rows
            xNodeText = dRow("TvNodeName").ToString
            xNodeID = dRow("TvNodeId")

            If xParentNodeID = 0 Then
                TreeView1.Nodes.Add(xNodeID.ToString, xNodeText)
            Else
                TreeView1.Nodes(xParentNodeID.ToString).Nodes.Add(xNodeID.ToString, xNodeText)
            End If

            LoadRecursive(xNodeID)
        Next
    End Sub

Then, change LoadTree() like this:

    Private Sub LoadTree()
        Treeview1.Nodes.Clear
        LoadRecursive(0)
    End Sub
0
 
bulrickAuthor Commented:
 Thank you for the help....

    I haven't much time this morning.. meetings.  However, quickly tried your solution and got a stack overflow in the following code.
  The root node is zero... so I tried your code as is.  

     I got a stack overflow... it looks like its stuck in the loop below.  
'------------------------------------------------------------------------------------------
      For Each dRow In ds.Tables("MyTreeView1").Rows
            xNodeText = dRow("TvNodeName").ToString
            xNodeID = dRow("TvNodeId")

            If xParentNodeID = 0 Then
                TreeView1.Nodes.Add(xNodeID.ToString, xNodeText)
            Else
                TreeView1.Nodes(xParentNodeID.ToString).Nodes.Add(xNodeID.ToString, xNodeText)
            End If

            LoadRecursive(xNodeID)
        Next

           If xParentNodeID = 0 Then
                TreeView1.Nodes.Add(xNodeID.ToString, xNodeText)
            Else
                TreeView1.Nodes(xParentNodeID.ToString).Nodes.Add(xNodeID.ToString, xNodeText)
            End If
   '---------------------------------------------------------------------------------------------
         
             I will debug this more carefully and get back to you.  
             Do you see the problem?

     My tree is     Root
                           sub1
                                sub1a
                           sub 2
                                sub2A
--------------------------------------------------------------------------
  Data in tblTreeView1
    TvNodeId        TvParentId      TvNodeName    TvPermission    (Permission is not used at this time)
       0                       0                 Root                    0
       1                       0                 sub1                    0
       4                       1                 sub1a                  0
       2                       0                 sub2                    0
       3                       2                 sub2a                  0
0
 
junglerover77Commented:
oh no, there is no error in my code. The error is  that your Root Node is WRONG, it's parent node is just itself ! So that you will get the stack error.

Just Change Your data like
    TvNodeId        TvParentId      TvNodeName    TvPermission    (Permission is not used at this time)
       0                       -1                Root                    0
       1                       0                 sub1                    0
       4                       1                 sub1a                  0
       2                       0                 sub2                    0
       3                       2                 sub2a                  0

Then do the following:
    Private Sub LoadTree()
        Treeview1.Nodes.Clear
        LoadRecursive(-1)
    End Sub

Regards,
Jungle
0
 
bulrickAuthor Commented:
   Brilliant!   I really appreciate this help.  Have a great day!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now