Display Hierarchy in a DropDown List

Question

I have a data hierarchy that I currently display in a treeview. I was wondering what would be the easiest way to convert this hierarchy into a dropdown list as well. In a treeview I can find a specific node and add an item under that node. I'm not sure how to do that with a drop down list. Below is the code I have for the dropdown so far:

DropDown Hierarchy
**Solved

    public void DropDownTree(DropDownList ddl)
    {
        ddl.Items.Clear(); 
        using (SqlConnection connection = new SqlConnection())
        {
            // Data Connection
            connection.ConnectionString = (ConfigurationManager.ConnectionStrings["AssetWhereConnectionString"].ConnectionString);
            connection.Open();
            string getLocations = @"
                With hierarchy (id, [location id], name, depth, [path])
                As (

                    Select ID, [LocationID], Name, 1 As depth,
                        Cast(Null as varChar(max)) As [path]
                    From dbo.Locations
                    Where ID = [LocationID]

                    Union All

                    Select child.id, child.[LocationID], child.name,
                        parent.depth + 1 As depth,
                        IsNull(
                            Cast(parent.id As varChar(max)),
                            Cast(parent.id As varChar(max))
                        ) As [path]
                    From dbo.Locations As child
                    Inner Join hierarchy As parent
                        On child.[LocationID] = parent.ID
                    Where child.ID != parent.[Location ID])

                Select *
                From hierarchy
                Order By [depth] Asc";

            using (SqlCommand cmd = new SqlCommand(getLocations, connection))
            {
                cmd.CommandType = CommandType.Text;
                using (SqlDataReader rs = cmd.ExecuteReader())
                {
                    while (rs.Read())
                    {
                        string id = rs.GetGuid(0).ToString();
                        int depth = rs.GetInt32(3);
                        string text = rs.GetString(2);
                        string locationID = rs.GetGuid(1).ToString();
                        string padding = String.Concat(Enumerable.Repeat("- ", 2 * depth));


                        if (id == locationID)
                        {
                            ddl.Items.Add(new ListItem(padding + text, id));
                        }
                        else
                        {
                            int index = ddl.Items.IndexOf(ddl.Items.FindByValue(rs.GetString(4).ToString().ToLower()));

                            // Fix the location where the item is inserted. 
                            index = index + 1;

                            ddl.Items.Insert(index, new ListItem(padding + text, id));

                        }
                    }
                }
            }
        }
    }

I'm trying to build the hierarchy, but I'm getting a negative index. I'm trying to do something similar to what I did when I built the treeview (code for that listed above), by finding the "node" and placing the correct list item underneath it. — Will
– Will, Commented Jun 6, 2011 at 19:50
Which item is giving you the negative index? First? Last? All? — NakedBrunch
– NakedBrunch, Commented Jun 6, 2011 at 20:28

NakedBrunch · Accepted Answer · 2011-06-06 21:04:01Z

3

Your code looks fine but from your comment under your question, it sounds like you are getting a negative value for the index on this line:

int index =  ddl.Items.IndexOf(ddl.Items.FindByValue(rs.GetString(4).ToString().ToLower()));

A negative value indicates that the dropdownlist item that you were searching for was not found. This would certainly be the case if the dropdownlist is empty. You would be searching for an item that doesn't exist.

Below is an update to your code that will produce the following (which I believe is what you're looking for): enter image description here

Try changing your code to the following:

if (id == locationID)
{
    ddl.Items.Add(new ListItem(padding + text, id));
}
else
{
    int index =  ddl.Items.IndexOf(ddl.Items.FindByValue(rs.GetString(4).ToString().ToLower()));

    //Check to see if this item exists before trying to insert
    if (index == -1) 
    {
        //Add the item if it doesn't exist
        ddl.Items.Add(new ListItem(padding + text, id));
    }
    else
    {
        ddl.Items.Insert(index, new ListItem(padding + text, id));
    }
}

answered Jun 6, 2011 at 21:04

NakedBrunch

49.5k14 gold badges75 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Will Over a year ago

You are correct with my issue. I'm getting the issue because at a point my path (GetString(4)) doesn't match any values. I start getting the -1 once the path includes '/'. Is there a way to strip the last value off of the path. EX: if I have abc/def/ghi, how can I just get the last value after the '/' ("ghi")?

Will Over a year ago

Nevermind I figured it out by editing the SQL. I also had to add one to the index in order for the hierarchy to be built down instead of up. It looks like my issue has been resolved. I'll update my code above with the solution. Thank you very much for your help!

Massimiliano Peluso · Accepted Answer · 2011-06-06 12:37:17Z

1

The only way you have is contatenate a string to the Node Description: it might me a white space just to indent the child node against its parent or something graphically better

answered Jun 6, 2011 at 12:37

Massimiliano Peluso

26.8k6 gold badges65 silver badges75 bronze badges

Comments

mika · Accepted Answer · 2011-06-06 15:23:07Z

1

With standard HTML select lists there are two ways to display hierarchy:

If root nodes are not selectable, you can use optgroup elements. Unfortunately, ASP.NET DropDownList does not support optgroups.
If root nodes are selectable, use whitespace to indent child nodes

I would say that the easiest way is to use the depth value in your resultset:

    DropDownList ddl = new DropDownList();
    while (rs.Read())
    {
        string id = rs.GetGuid(0).ToString();
        int depth = rs.GetInt32(3);
        string text = rs.GetString(2);
        string padding = String.Concat(Enumerable.Repeat("&#160;", 4 * depth));
        ddl.Items.Add(new ListItem(padding + text, id));
    }

answered Jun 6, 2011 at 15:23

mika

7,0324 gold badges39 silver badges38 bronze badges

2 Comments

Will Over a year ago

This does successfully space out the values and add them to a drop down, but unfortunately it doesn't preserve the hierarchy. Not sure how I'd go about preserving the hierarchy.

Will Over a year ago

I attempted to duplicate finding the child node for the dropdown list by finding the index instead. It almost works but I'm getting a negative index. I've edited my code above with what I currently have.

Collectives™ on Stack Overflow

Display Hierarchy in a DropDown List

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related