Displaying Database values into a menu

See more:

VB

ASP.NET

Hi,

I am trying to dynamically create a menu.

I have database(MenuDb) and table(Menu) in sql server.

The menu table consists of

MenuId , MenuText , Parent ID.

1 Masters 0
2 Transactions 0
3 Reports 0
4 Help 0
5 Company 1
6 Factory 1
7 Warehouse 1
8 Users 1
9 Items 1
10 GoodsInward 2
11 GoodsOutward 2
12 Production 3
13 UserMatric 3
14 Secondly 3
15 About 4
16 SNO 9
17 Category 9
18 R1 14
19 R2 14

Here is my aspx page code where I am defining asp:menu

<asp:menu id=""menu"" datasourceid=""xmlDataSource"" runat=""server"" backcolor=""#FFFBD6"" dynamichorizontaloffset=""2"" font-names=""Verdana"" forecolor=""#990000"" staticsubmenuindent=""10px"" staticdisplaylevels=""1"" orientation=""Horizontal"><br" mode="hold" xmlns:asp="#unknown" />                <DataBindings>
                <asp:MenuItemBinding DataMember="Text" NavigateUrlField="NavigateUrl" TextField="MenuText" ToolTipField="ToolTip"/>
                </DataBindings>
                </asp:Menu>
                <asp:XmlDataSource ID="xmlDataSource" runat="server" TransformFile="~/TransformXSLT.xslt" XPath="MenuItems/MenuItem"/>

And the code behind page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       lblTime.Text = DateTime.Now.ToString()
       Dim ds As New DataSet()
       Dim connStr As String = "Data Source=LAKSHMI-E31FABD;Initial Catalog=MenuDb;User Id=sa;Password=rootanupama;Trusted_Connection=true;"
       Using con As New SqlConnection(connStr)
           Dim sql As String = "Select MenuID,MenuText,ParentID from Menu"
           Dim da As SqlDataAdapter = New SqlDataAdapter(sql, con)
           da.Fill(ds)
           da.Dispose() 'optional
       End Using
       ds.DataSetName = "Menus"
       ds.Tables(0).TableName = "Menu"
       Dim relation As DataRelation = New DataRelation("ParentChild", ds.Tables("Menu").Columns("MenuID"), ds.Tables("Menu").Columns("ParentID"), False)
       relation.Nested = True
       ds.Relations.Add(relation)
       xmlDataSource.Data = ds.GetXml()
       If Request.Params("Sel") IsNot Nothing Then
           Page.Controls.Add(New System.Web.UI.LiteralControl("You selected " + Request.Params("Sel")))
       End If

   End Sub

My xml xslt file

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
    <xsl:output method="xml" indent="yes" encoding="utf-8"/>
    <xsl:template match="/Menus">
        <MenuItems>
            <xsl:call-template name="MenuListing" />
        </MenuItems>
    </xsl:template>
    <!-- Allow for recursive child node processing -->
    <xsl:template name="MenuListing">
        <xsl:apply-templates select="Menu" />
        </xsl:template>
        <xsl:template match="Menu">
            <MenuItem>
                <!-- Convert Menu child elements to MenuItem attributes -->
                <xsl:attribute name="Text">
                    <xsl:value-of select="MenuText"/>
                    </xsl:attribute>
                    <xsl:attribute name="NavigateUrl">
                            <xsl:text>?Sel=</xsl:text>
                            <xsl:value-of select="Text"/>
                            </xsl:attribute>
                            <!-- Recursively call MenuListing forchild menu nodes -->
                            <xsl:if test="count(Menu) >0">
                                <xsl:call-template name="MenuListing" />
                            </xsl:if>
                        </MenuItem>
        </xsl:template>
    </xsl:stylesheet>

My Problem is when I am trying to debug this code I am not getting the values to the menu but it is show the relation correctly according to the database.

Eg. For the master tab I have 5 child nodes and it is showing the 5 links below the main heading but all the values are being displayed as 'Menu Item'.

Please let me know where I am going wrong or what I need to change in order to get the output.

Thanks in Advance.