Dynamic Treeview Menu as per access and permissions

Question

Hi All,

I am using VWD 2010 Express with mySQL as database. I have a treeview menu which i want to populate as per the permissons/access set. I took ideas/codes from these links

http://aspalliance.com/732
http://www.codeproject.com/Articles/366450/Permissions-and-Levels-in-ASP-Menu

My treeview is as below:

        <div id="MainMenu" style="height: 600px;">
            <asp:TreeView ID="tvwMainMenu" runat="server" ExpandDepth="0" PopulateNodesFromClient="true"
                ShowLines="true" ShowExpandCollapse="true" SelectedNodeStyle-BackColor="#8E909C"
                SelectedNodeStyle-BorderColor="Black" HoverNodeStyle-BackColor="#7E909C" HoverNodeStyle-BorderColor="Black"
                ImageSet="BulletedList4">
            </asp:TreeView>
        </div>

My menu structure and user permission table and data is as below

Menu Structure

ID    nID    nName        Path
3    0    Orders
1    0    Masters
2    0    Quotes
4    1    System Master    system.aspx
5    1    Clients Master    clients.aspx
6    1    Agents Master    agents.aspx
7    2    New Orders    neworders.aspx
8    2    Pending Orders    pendingorders.aspx
9    3    Estimates    estimates.aspx
10    3    Estimate Lists    estimatelists.aspx

User Permissions
uID    Password    uLevel
aa    ad        1-2-3-4-5-6-7-8-9-10
uu    us        1-2-4-5-6-9-10

What i want is that the treeview should be populated according to the structure set in the Users Permissions table

My codebehind is as below:

Dim permissions() As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        GetConnDetails()
        If Not Page.IsPostBack Then
            If Not Session("UName") = "" Then
                LoadMenu()
            End If
            LoadRootMenu()
        End If

    Private Sub GetConnDetails()
        Dim conn As New MySql.Data.MySqlClient.MySqlConnection
        Dim myConnectionString As String
        myConnectionString = "server=127.0.0.1; uid=ua;pwd=pa;database=db;"
        Try
            conn.ConnectionString = myConnectionString
            conn.Open()
        Catch ex As MySql.Data.MySqlClient.MySqlException
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub LoadMenu()
        If Session("UName") = "" Then
            Return
        End If
        Dim strPermissionString As String
        strPermissionString = getUserPermissions(Session("UName").ToString())
        permissions = strPermissionString.Split("-")
    End Sub

    Private Function getUserPermissions(ByVal strUserName As String) As String
        Dim myDataSet As New DataSet
        Dim dt As New DataTable
        Dim objConn As New MySqlConnection("server=127.0.0.1; user id=ua; password=pa; database=db; pooling=false;")
        Dim objCommand As New MySqlCommand("SELECT * FROM users where NameOfUser='" & Session("UName") & "'", objConn)
        Dim da As New MySqlDataAdapter(objCommand)
        da.Fill(myDataSet, "UserDetails")
        If myDataSet.Tables("UserDetails").Rows.Count = 0 Then
            ScriptManager.RegisterStartupScript(Me, Me.GetType(), "not found!", "alert('User not Found!!')", True)
            Return ""
        End If
        objConn.Close()
        Return myDataSet.Tables("UserDetails").Rows(0)("uLevel").ToString()
    End Function

    Public Sub PopulateRootLevel()
        Dim tvwMain As New TreeView
        tvwMain = Me.FindControl("tvwMain")
        Dim myDataSet As New DataSet
        Dim dt As New DataTable
        Dim objConn As New MySqlConnection("server=localhost; user id=ua; password=pa; database=db; pooling=false;")
        Dim strTemp As String
        strTemp = "SELECT nID, nName, (SELECT COUNT(*) FROM Menu sc WHERE sc.nID = nID) childnodecount FROM Menu "
        For Each childID In permissions
            If IsNumeric(childID) = False Then
                Exit For
            End If
            If IndexOf(permissions, childID) = 0 Then
                strTemp += " WHERE ID = " + childID
            Else
                strTemp += " OR ID = " + childID
            End If
        Next
        strTemp += " ORDER BY NodeID"
        MsgBox(strTemp)
        Dim objCommand As New MySqlCommand(strTemp, objConn)
        Dim da As New MySqlDataAdapter(objCommand)
        da.Fill(dt)
        PopulateNodes(dt, tvwMain.Nodes)
    End Sub

    Private Sub PopulateNodes(ByVal dt As DataTable, ByVal nodes As TreeNodeCollection)
        For Each dr As DataRow In dt.Rows
            Dim tn As New TreeNode()
            tn.Text = dr("nName").ToString()
            tn.Value = dr("nID").ToString()
            nodes.Add(tn)
            tn.PopulateOnDemand = (CInt(dr("childnodecount")) > 0)
        Next
    End Sub

    Private Sub PopulateSubLevel(ByVal parentid As Integer, ByVal parentNode As TreeNode)
'            Dim objConn As New MySqlConnection("server=localhost; user id=ua; password=pa; database=db; pooling=false;")
'            Dim objCommand As New MySqlCommand("select id,nName,(select count(*) FROM Menu " _
'            & "WHERE nID=sc.id) childnodecount FROM MainMenu sc where nID=@parentID", objConn)
'            objCommand.Parameters.AddWithValue("@parentID", SqlDbType.Int).Value = parentid
'            Dim da As New MySqlDataAdapter(objCommand)
'            Dim dt As New DataTable()
'            da.Fill(dt)
'            PopulateNodes(dt, parentNode.ChildNodes)
    End Sub

    Protected Sub tvwMainMenu_TreeNodePopulate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.TreeNodeEventArgs) Handles tvwMainMenu.TreeNodePopulate
        'PopulateSubLevel(CInt(e.Node.Value), e.Node)
    End Sub

I have commented some bits in order to first populate the root nodes (Orders, Masters, Quotes etc.) and then put permissible sub nodes under each root node.

The code above is not working correctly and all nodes are being populated as root nodes. Where am i going wrong? I will try to add the navigation link later but if it can be included in the correct solution, I will appreciate.

Thanks in advance

Tom