Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
Hello
I get this Exception when in try to convert data relation from Data set tables to xml to make an XmlDataSource supply it to Dynamic Menu Control.

This is the Code behind

DataSet ds = new DataSet();
 
 SqlCommand cmdmenu = new SqlCommand("select CategoryID,CategoryName from Category ", ConManager.Con());
 
 SqlCommand com2 = new SqlCommand("select CategoryIDToSub,subcategoryid ,SubCategoryName from CategorySub", ConManager.Con());
SqlCommand cmd3 = new SqlCommand("select subcategoryid 'SubCategoryID2',Sub2CategoryName from CategorySub2", ConManager.Con());
 
SqlDataAdapter menadapter = new SqlDataAdapter(cmdmenu.CommandText, ConManager.Con());
SqlDataAdapter menadapter2 = new SqlDataAdapter(com2.CommandText, ConManager.Con());
SqlDataAdapter menadapter3 = new SqlDataAdapter(cmd3.CommandText, ConManager.Con());
                  menadapter.Fill(ds, "Menu");
 
                  menadapter.SelectCommand = com2;
                  menadapter2.Fill(ds, "SubMenu");
                  menadapter3.SelectCommand = cmd3;
                  menadapter3.Fill(ds, "SubSubMenu");
 
                  DataColumn colParent =
                  ds.Tables["Menu"].Columns["CategoryID"];
                  DataColumn colChild =
                  ds.Tables["SubMenu"].Columns["CategoryIDToSub"];
 
                  DataRelation relation = new DataRelation("relationName",
                  colParent,
                  colChild, true);
                  DataRelation Relation2 = new DataRelation("relationName2",
                  ds.Tables["SubMenu"].Columns["subcategoryid"],
                  ds.Tables["SubSubMenu"].Columns["SubCategoryID2"], true);
 
                  relation.Nested = true;
                  Relation2.Nested = true;
 
                  ds.Relations.Add(relation);
                  ds.Relations.Add(Relation2);
 
                  XmlDataSource.Data = ds.GetXml();
              
                  if (Request.Params["Sel"] != null)
                        Page.Controls.Add(new System.Web.UI.LiteralControl("You selected " + Request.Params["Sel"]));
and this is the Xslt File Code
XML
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes" encoding="utf-8"/>

  <!-- Replace root node name Menus with MenuItems
       and call MenuListing for its children-->
  <xsl:template match="/Menus">
    <MenuItems>
      <xsl:call-template name="MenuListing" />
    </MenuItems>
  </xsl:template>
  <!-- Allow for recursive child nodeprocessing -->
  <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="CategoryName"/>
      </xsl:attribute>
      <xsl:attribute name="ToolTip">
        <xsl:value-of select="Remarks"/>
      </xsl:attribute>
      <xsl:attribute name="NavigateUrl">
        <xsl:text>?Sel=</xsl:text>
        <xsl:value-of select="Text"/>
      </xsl:attribute>
      <xsl:attribute name="Text2">
        <xsl:value-of select="SubCategoryName"/>
      </xsl:attribute>
      <xsl:attribute name="Test3">
        <xsl:value-of select="Sub2CategoryName3"/>
      </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>

and this is the Menu Control and the XmlDataSource Code
XML
<asp:Menu ID="Menu" runat="server" BackColor="#E3EAEB"  DynamicHorizontalOffset="2" Font-Names="Verdana" Font-Size="0.8em" ForeColor="#666666" StaticSubMenuIndent="10px" DataSourceID="XmlDataSource" Width="225px" onmenuitemclick="Menu_MenuItemClick">
        
<databindings>
 <asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl" TextField="Text" />
 <asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl" TextField="Text2" />
 <asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl" TextField="Text3" />
</databindings>

        <staticselectedstyle backcolor="#1C5E55" />
        <staticmenuitemstyle horizontalpadding="5px" verticalpadding="2px" />
        <dynamichoverstyle backcolor="#666666" forecolor="White" />
        <dynamicmenustyle backcolor="#E3EAEB" />
        <dynamicselectedstyle backcolor="#1C5E55" />
        <dynamicmenuitemstyle horizontalpadding="5px" verticalpadding="2px" />
        <statichoverstyle backcolor="#666666" forecolor="White" />
                     
    <asp:XmlDataSource ID="XmlDataSource" runat="server" 
        TransformFile="~/TransformXSLT.xsl" XPath="MenuItems/MenuItem">


plz help
Please, help.
Posted
Updated 22-May-11 14:44pm
v2
Comments
Hemant__Sharma 23-May-11 1:39am    
Can you add the XML you are getting from "ds.GetXml();"? a little sample having couple of enteries from all table will do.

--Hemant
ehab_developer 23-May-11 8:38am    
How can i Add Xml code it's dosen't added well
Hemant__Sharma 23-May-11 9:45am    
try this
http://www.codeproject.com/Tips/87155/Formatting-code-in-Tips-and-Tricks.aspx

I would suggest your data does not match as you hope, and thus is trying to create two root elements. Why not put your SQL in a data layer, and write your own code to generate your XML ?
 
Share this answer
 
Comments
Yvan Rodrigues 22-May-11 21:25pm    
Definitely a sign of more than one root element.
hello ehab,

The solution is to
replace
DataSet ds = new DataSet();


with
DataSet ds = new DataSet("Menus");


The problem is:
if you don't supply dataset a name by default it's name property will be NewDataSet and this is where your XSL is not able to find Menus root node hence below result:

XML
<?xml version="1.0" encoding="utf-8"?>
  <MenuItem Text="Category Name 1" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
  <MenuItem Text="Category Name 2" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
  <MenuItem Text="Category Name 3" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
  <MenuItem Text="Category Name 4" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
  <MenuItem Text="Category Name 5" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />


you see the all MenuItem are found but because the root node is a mismatch this results in invalid XML i.e. multiple root elements.

hope this will help you.

Thanks,
Hemant
 
Share this answer
 
Comments
ehab_developer 24-May-11 11:21am    
Grrrrrrrrrrrrreeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaat,,,,, It's work , Thanks alot HEMANT realy thank u , but now i have problem the Menu show the first level only (The Menu items only no sub or sub sub)
i think the problem in the menu Html Bind code .
Hemant__Sharma 25-May-11 4:23am    
i've added a seperate solution because you have a new problem now. see additional solution i've added.
1 more thing always mark a solution as an answer if it solves your problem you have asked.

Thanks,
Hemant
This is a seperate solution for seperate problem you've asked in my first solution. A request please add seperate question for seperate problem it helps other to easily locate answer for similar problem.

Now the new problem you are facing:
if you apply XSL to the XML you are getting from ds.GetXml(); you will get below output:
XML
<?xml version="1.0" encoding="utf-8"?>
<MenuItems>
  <MenuItem Text="Category Name 1" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
  <MenuItem Text="Category Name 2" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
  <MenuItem Text="Category Name 3" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
  <MenuItem Text="Category Name 4" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
  <MenuItem Text="Category Name 5" ToolTip="" NavigateUrl="?Sel=" Text2="" Test3="" />
</MenuItems>


you do not have any tree-like XML for a tree-like menu, you should update your XSL to generate appropriate XML for XMLDatasource. I have updated the XSL a bit and you can modify it according to your app requirement.

XSL:
XML
<?xml version="1.0" encoding="utf-8"?>
<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"/>

  <!-- Replace root node name Menus with MenuItems
       and call MenuListing for its children-->
  <xsl:template match="/Menus">
    <MenuItems>
      <xsl:call-template name="MenuListing" />
    </MenuItems>
  </xsl:template>
  <!-- Allow for recursive child nodeprocessing -->
  <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="CategoryName"/>
      </xsl:attribute>
      <xsl:attribute name="ToolTip">
        <xsl:value-of select="Remarks"/>
      </xsl:attribute>
      <xsl:attribute name="NavigateUrl">
        <xsl:text>?Sel=</xsl:text>
        <xsl:value-of select="Text"/>
      </xsl:attribute>
      <xsl:call-template name="Menu"/>
      <!-- Recursively call MenuListing forchild menu nodes -->
      <xsl:if test="count(Menu) >0">
        <xsl:call-template name="MenuListing" />
      </xsl:if>
    </MenuItem>
  </xsl:template>
  <!-- Template for sub menu item -->
  <xsl:template name="Menu">
    <xsl:apply-templates select="SubMenu" />
  </xsl:template>
  <xsl:template match="SubMenu">
    <SubMenuItem>
      <!-- Convert Menu child elements to MenuItem attributes -->
      <xsl:attribute name="Text">
        <xsl:value-of select="SubCategoryName"/>
      </xsl:attribute>
      <xsl:attribute name="NavigateUrl">
        <xsl:text>?Sel=</xsl:text>
        <xsl:value-of select="Text"/>
      </xsl:attribute>
      <xsl:call-template name="SubMenu"/>
      <!-- Recursively call Menu forchild submenu nodes -->
      <xsl:if test="count(SubMenu) >0">
        <xsl:call-template name="Menu" />
      </xsl:if>
    </SubMenuItem>
  </xsl:template>
  <!-- Template for subsub menu item -->
  <xsl:template name="SubMenu">
    <xsl:apply-templates select="SubSubMenu" />
  </xsl:template>
  <xsl:template match="SubSubMenu">
    <SubSubMenuItem>
      <!-- Convert Menu child elements to MenuItem attributes -->
      <xsl:attribute name="Text">
        <xsl:value-of select="Sub2CategoryName"/>
      </xsl:attribute>
      <xsl:attribute name="NavigateUrl">
        <xsl:text>?Sel=</xsl:text>
        <xsl:value-of select="Text"/>
      </xsl:attribute>
      <!-- Recursively call Menu forchild submenu nodes -->
      <xsl:if test="count(SubSubMenu) >0">
        <xsl:call-template name="SubMenu" />
      </xsl:if>
    </SubSubMenuItem>
  </xsl:template>
</xsl:stylesheet>


The above XSL will produce below result:
XML
<?xml version="1.0" encoding="utf-8"?>
<MenuItems>
  <MenuItem Text="Menu Item1" ToolTip="" NavigateUrl="?Sel=">
    <SubMenuItem Text="Sub Menu Item1" NavigateUrl="?Sel=">
      <SubSubMenuItem Text="Sub Sub Item1 " NavigateUrl="?Sel=" />
    </SubMenuItem>
  </MenuItem>
  <MenuItem Text="Menu Item2" ToolTip="" NavigateUrl="?Sel=" />
  <MenuItem Text="Menu Item3" ToolTip="" NavigateUrl="?Sel=" />
  <MenuItem Text="Menu Item4" ToolTip="" NavigateUrl="?Sel=" />
  <MenuItem Text="Menu Item5" ToolTip="" NavigateUrl="?Sel=" />
</MenuItems>


Now you need to update your DataItemBinding for Menu control:
XML
<DataBindings>
    <asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl" TextField="Text" />
    <asp:MenuItemBinding Depth="1" DataMember="SubMenuItem" NavigateUrlField="NavigateUrl" TextField="Text" />
    <asp:MenuItemBinding Depth="2" DataMember="SubSubMenuItem" NavigateUrlField="NavigateUrl" TextField="Text" />
</DataBindings>


The Depth property extracts member from child node of datasource.

Hope it will help mark as answer if helped.

thanks,
Hemant
 
Share this answer
 
Comments
ehab_developer 25-May-11 8:18am    
It's working Great Thanks Hemant ,Thanks alot
Hemant__Sharma 25-May-11 9:06am    
Glad i could help.
But you have not accepted both solution. you can click on "Accept Solution" button and vote for the answer if you like it.

Thanks
--Hemant
ehab_developer 25-May-11 11:01am    
Sure i like it it's what i was need and it's work well now and i accect it and vote for it ,Thanks Hemant
Best Regards
Ehab
Hemant__Sharma 25-May-11 11:02am    
you are always welcome :)

Regards,
Hemant
This is the output XML


<pre Lang="XML">
<NewDataSet>
<Menu>
<CategoryID>1</CategoryID>
<CategoryName>Menu Item1</CategoryName>
<SubMenu>
<CategoryIDToSub>1</CategoryIDToSub>
<subcategoryid>1</subcategoryid>
<SubCategoryName>Sub Menu Item1</SubCategoryName>
<SubSubMenu>
<SubCategoryID2>1</SubCategoryID2>
<Sub2CategoryName>Sub Sub Item1 </Sub2CategoryName>
</SubSubMenu>
</SubMenu>
</Menu>
<Menu>
<CategoryID>2</CategoryID>
<CategoryName>Menu Item2</CategoryName>
</Menu>
<Menu>
<CategoryID>3</CategoryID>
<CategoryName>Menu Item3</CategoryName>
</Menu>
<Menu>
<CategoryID>4</CategoryID>
<CategoryName>Menu Item4</CategoryName>
</Menu>
<Menu>
<CategoryID>5</CategoryID>
<CategoryName>Menu Item5</CategoryName>
</Menu>
</NewDataSet>
</pre>
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900