Issue in creating a data driven menu in asp.net

Question

I am creating a data-driven dynamic menu in asp.net, using sql server 2008 R2 as my database. The parent node, and child nodes at the first level works just fine. but the child node below another child node, the application generate an exception. The c# code for my problem is as follows :

public partial class LeaveManagementSystemMasterPage : System.Web.UI.MasterPage
{
SqlConnection conn;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        getMenu();
    }
}

private void getMenu()
{
    Connect();
    conn.Open();
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    string sqlQuery = "select * from menu";
    SqlDataAdapter da = new SqlDataAdapter(sqlQuery, conn);
    da.Fill(ds);
    dt = ds.Tables[0];
    DataRow[] rowParent = dt.Select("ParentID=" + 0);
    DataRow[] rowChild = dt.Select("ParentID>"+0);

    //populating the first level of the menu
    foreach (DataRow dr in rowParent)
    {
        Menu1.Items.Add(new MenuItem(dr["MenuName"].ToString(),dr["MenuID"].ToString(),"",""));
    }

    //populating the children in menu
    foreach (DataRow dr in rowChild)
    {
        MenuItem child = new MenuItem(dr["MenuName"].ToString(), dr["MenuID"].ToString(), "", "");
        Menu1.FindItem(dr["ParentID"].ToString()).ChildItems.Add(child);

    }


    conn.Close();

}

public void Connect()
{
    string conStr="Data Source=HO-0000-LAP; Initial Catalog=LeaveManagementSystem; Integrated Security=true";
     conn = new SqlConnection(conStr);
}

}

And here is my table which i have designed in sqlserver 2008 :-

Menu database

I added an answer based on a guess. It would help if you post the exception and show on which line you get the exception. You can view the variables and see which item is failing to be added, although you only have 1 3rd level child item (ID 311) it's easy to guess. — Dmitriy Khaykin, Mar 07 '13 at 11:33
Thanks David, for your info, but i have got the solution and i have posted it as my answer. Kindly refer that. — codeLearner, Mar 07 '13 at 11:38
Since your solution is the answer, you can just accept that one :) — Dmitriy Khaykin, Mar 07 '13 at 11:40
Yup i had clicked the goodie hollow tick mark, but i was unable to mark it as answer, as my reputation is quite low at SO :-( but i can do so after 2 days :-) — codeLearner, Mar 07 '13 at 11:44

score 1 · Accepted Answer · edited May 23 '17 at 12:21

The solution which i found useful for the issue was found at

Getting Menu Items from database in asp.net

And here is my source code :-

public partial class LeaveManagementSystemMasterPage : System.Web.UI.MasterPage

{ SqlConnection conn; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { getMenu(); } }

private void getMenu()
{
    Connect();
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    string sqlQuery = "select * from menu";
    SqlDataAdapter da = new SqlDataAdapter(sqlQuery, conn);
    da.Fill(ds);
    dt = ds.Tables[0];
    AddTopMenuItems(dt);


}

private void AddTopMenuItems(DataTable menuData)
{
    DataView view = new DataView(menuData);
    view.RowFilter = "ParentID = 0";
    foreach (DataRowView rowView in view)
    {
        MenuItem topItem = new MenuItem(rowView["MenuName"].ToString(),rowView["MenuID"].ToString());
        Menu1.Items.Add(topItem);
        AddChildItems(menuData, topItem);
    }

}

private void AddChildItems(DataTable menuData, MenuItem parentMenuItem)
{
    DataView view = new DataView(menuData);
    view.RowFilter = "ParentID = " + parentMenuItem.Value;

    foreach(DataRowView rowView in view)
    {
        MenuItem childItem = new MenuItem(rowView["MenuName"].ToString(), rowView["MenuID"].ToString());
        parentMenuItem.ChildItems.Add(childItem);
        AddChildItems(menuData, childItem);
    }

}

public void Connect()
{
    string conStr="Data Source=HO-0000-LAP; Initial Catalog=LeaveManagementSystem; Integrated Security=true";
     conn = new SqlConnection(conStr);
}

}

But still, there are some compatibility issues as this code doesn't work on google chrome, but is compatible in windows's IE 8. — codeLearner, Mar 08 '13 at 06:24
solved the chrome browser compatibility issue at http://forums.asp.net/p/1315088/2713325.aspx The post of "cnobles" helped me :-) — codeLearner, Mar 08 '13 at 10:06

score 0 · Answer 2 · answered Mar 07 '13 at 11:23

0

Going to venture a guess here and say it has to do with order of creation of the 3rd level child items.

You explicitly add parent items to the menu from rowParent collection, then you add "the rest". The problem is that when you start the 2nd for loop for adding children, you are relying on the order of the items to be such that higher level items are added first. This is not guaranteed.

So what may be happening (if you post the exception we can confirm) is like this

After the foreach loop (on rowParent) your menu object has all 5 parent items added

Administration (ID 1), 
Setup (ID 2)
Operations (ID 3) 
Reports (ID 4) 
Logout (ID 5)

On the 2nd foreach, for example, lets day you have 2 items, for example because this is likely where your problem is

Management ID 31, ParentID 3
Initiate Application ID 311, ParentID 31

If Management is added first, then Initiate Application will work as FindItem() will find the parent to add to.

However, if initiate Application goes first, and management has not been added yet, it will throw an error because FindItem() will fail (the item hasn't been added yet).

You can add an order by clause and order by Parent ID to ensure items get added in an order such that lower numbered parent items will be added first.

Or you can make a function and pass it the parent ID's several times over for each level, to make sure you're always adding child items to parents that have already been added. On iPad now but will add some code shortly.

answered Mar 07 '13 at 11:23

Dmitriy Khaykin

5,238
1
20
32

yes i have an error in the line where finditem() is being called, but i have changed my code, by using the link http://stackoverflow.com/questions/12349163/getting-menu-items-from-database-in-asp-net?rq=1 – codeLearner Mar 07 '13 at 11:34
Ok that answer looks more complex but if it works it works. Here is a simple one that shows how to sort using a data view, you can sort on "ParentID ASC" and then just add all the items in one loop: http://stackoverflow.com/questions/2294180/sort-items-in-datatable – Dmitriy Khaykin Mar 07 '13 at 11:36
Yeah david, by sorting in DataView , it is quite simple to sort. But dont't you think that the nature of problem occured there was to just arrange the values in ascending order ? Where as my problem was to populate a menu, which required the parent id column in my database in two portion. One for the parent elements with ParentID=0 and the other greater than 0 – codeLearner Mar 07 '13 at 11:52
Yes and no... in your example, the items you say have ParentID > 0, they still have multiple levels. Your item with ID 311 has ParentID 31 - if that item is not added to the menu first, then you get an exception on FindItem(). Sorting in your simple case works. However, the solution you found is more robust and doesn't depend on sort, but depends on the ID's of the level above it, which is the right way to do it. In the more complex cases this is a recursion issue, not a sort issue. – Dmitriy Khaykin Mar 07 '13 at 11:57
Thats what i was trying to comment, my issue is for recursion, because there are submenus in two or more than two levels. And this code, which i have posted is also valid for more than two levels, so thumbs up to SO's karthik :-) He really made my life easy :-) and thank you David for your honest support on this :-) – codeLearner Mar 08 '13 at 05:12

Issue in creating a data driven menu in asp.net

2 Answers2