Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

categories and subcategories display in the dropdown/gridview

Posted on 2012-03-10
14
Medium Priority
?
301 Views
Last Modified: 2012-06-21
Hi i want to show in the drop drown control all the categories and the sub categeories
based on the table design


SELECT  [Id],      ,[Name],       parentcategoryid    
  FROM [Category]

Id      Name      parentcategoryid
1      Books              0
2      Computers      0
3      Desktops       2
4      Notebooks      2
5      Accessories      3
6      Software        2
7      Games              2
8      Electronics      0
9      Camera, photo      8

pls see the image, wht i looking
any help will be appreciated..
dropdown.png
gridview-categories.png
0
Comment
Question by:bsarahim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 37706794
this code builds the list of your table item links.


dt -> the datatable that contains the data from DB
list -> contains the following list:
Books
Computers
Computers->Desktops
Computers->Notebooks
Computers->Desktops->Accessories
Computers->Software
Computers->Games
Electronics
Electronics->Camera, photo


all you gotta do is bind the list to your dropdown list control.

cheers


int parentId = 0;
            List<string> list = new List<string>();
            var rows = dt.Rows.Cast<DataRow>();
            foreach (var item in rows)
            {
                parentId = (int)item["parentcategoryid"];
                string name = item["Name"].ToString();

                while (parentId > 0)
                {
                    var row = rows.Where(n => (int)n["Id"] == parentId).FirstOrDefault();
                    if (row != null)
                    {
                        parentId = (int)row["parentcategoryid"];
                        name = string.Format("{0}->{1}", row["Name"], name);
                    }
                } 
                list.Add(name);
            }

Open in new window

0
 

Author Comment

by:bsarahim
ID: 37781594
any vb.net code?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37781769
here:

Dim parentId As Integer = 0
Dim list As New List(Of String)()
Dim rows = dt.Rows.Cast(Of DataRow)()
For Each item As var In rows
	parentId = CInt(item("parentcategoryid"))
	Dim name As String = item("Name").ToString()

	While parentId > 0
		Dim row = rows.Where(Function(n) CInt(n("Id")) = parentId).FirstOrDefault()
		If row IsNot Nothing Then
			parentId = CInt(row("parentcategoryid"))
			name = String.Format("{0}->{1}", row("Name"), name)
		End If
	End While
	list.Add(name)
Next

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:bsarahim
ID: 37781780
im sorry im using Ado.net, asp.net 3.5 not the linq.. thanks
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37782045
use DataRow.Select method to find the datarow with the right ID:
Dim result As DataRow() = dt.[Select]("Id = " & parentId)
if result.Length>0 Then
Dim row = result[0]
		If row IsNot Nothing Then
			parentId = CInt(row("parentcategoryid"))
			name = String.Format("{0}->{1}", row("Name"), name)
		End If		
End If

Open in new window

0
 

Author Comment

by:bsarahim
ID: 37782975
thanks..

before that, should I read the data in the dataadapater and put back in the table? or wht is the dt here?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37784137
The dt is the datatable i used it as an example
0
 

Author Comment

by:bsarahim
ID: 37785777
super.. the earlier is code working.. but i have challenges..



 For Each item In rows

            parentId = CInt(item("parentcategoryid"))
            Dim name As String = item("Name").ToString()

            While parentId > 0
                Dim row = rows.Where(Function(n) CInt(n("Id")) = parentId).FirstOrDefault()

                If row IsNot Nothing Then
                    parentId = CInt(row("parentcategoryid"))
                    name = String.Format("{0}->{1}", row("Name"), name)
                End If
            End While
            List.Items.Add(name)

        Next

I have added the listbox control in the loop

When I change the selectedindexchange..I want to get the parent category Id.
 before i insert the value s in to the table... kindly request you to help.. before i award the points
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37792919
to get the parent category id u need to parse the selected item
for instance, if user selected "1->3->8", the parent category id is 3
if user selected "1->2", the parent category id is 1
u need to add the logic where there's no parent category id, when user chooses root category id.

Private Sub listBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles listBox1.SelectedIndexChanged

   Dim curItem As String = listBox1.SelectedItem.ToString()

Dim list As New List(Of Integer)()
Dim tokens =curItem.Split(New String() {"->"}, StringSplitOptions.RemoveEmptyEntries)
For Each item As var In tokens
	list.Add(Integer.Parse(item))
Next

if list.Count > 1 then
Dim parentCatId As Integer = list(list.Count - 2)
else
End Sub

Open in new window

0
 

Author Comment

by:bsarahim
ID: 37792960
Thanks..

I have last doubt related to this topic..

1. i want to dispaly the data in the gridview based on the Id, is being fetched on the data row..event

Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT [Id] ,[Name] ,[ParentCategoryId]  FROM [nopCommerce].[dbo].[Category] where id=" & e.Row.Cells(0).Text, sqlConn)

'im fetching the data of id  e.Row.Cells(0).Text



 Dim dataSet As DataSet = New DataSet()
            adapter.Fill(dataSet, "Ordersvariant")


            Dim dt As DataTable = dataSet.Tables(0)

            Dim parentId As Integer = 0
            ' Dim list As New List(Of String)()
            Dim rows = dt.Rows.Cast(Of DataRow)()
            Dim item As DataRow

            For Each item In rows

                parentId = CInt(item("parentcategoryid"))
                Dim name As String = item("Name").ToString()
                '                where(ID = " & e.Row.Cells(0).Text")
                While parentId > 0
                    Dim row = rows.Where(Function(n) CInt(n("id")) = parentId).FirstOrDefault()

                    '  Dim row = rows.Where(Function(n) CInt(e.Row.Cells(0).Text) = parentId).FirstOrDefault()

                    If row IsNot Nothing Then
                        parentId = CInt(row("parentcategoryid"))
                        name = String.Format("{0}->{1}", row("Name"), name)
                    End If
                End While

                Dim LblShoppingitems As Label
                LblShoppingitems = CType(e.Row.FindControl("Category"), Label)
                LblShoppingitems.Text = name

                'List.Items.Add(name)


                'List.Items(parentId).Text = name
            Next



But this goes in to unended loop and this is not working


2. totally differnt query: I want to display categroy, subcategories,.. in the treeview..
your help is appreciated..
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 2000 total points
ID: 37794777
I think for the sake of fairness you should open a new question and I'll be more than happy to assist you.
open new question and post here the url for it so i'll find it easily.
cheers
0
 

Author Comment

by:bsarahim
ID: 37794880
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37794912
>>But this goes in to unended loop and this is not working

do u mean the For Each loop is infinite?
0
 

Author Comment

by:bsarahim
ID: 37794924
1. it goes in to unended loop and the webpage goes on requesting ... but never display anything.

2..for your earlier drop down solution im getting the following error..

Private Sub list_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles listBox1.SelectedIndexChanged



        Dim curItem As String = Listbox1.SelectedItem.Value





        Dim list As New List(Of Integer)()
        Dim tokens = curItem.Split(New String() {"->"}, StringSplitOptions.RemoveEmptyEntries)

        ' Response.Write(tokens.ToString)

        'Response.End()

        For Each item In tokens
            list.Add(Integer.Parse(item))
        Next

        If list.Count > 1 Then
            Dim parentCatId As Integer = list(list.Count - 2)
            Response.Write(parentCatId)
        Else

        End If



    End Sub



Error details:

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:


Line 161:
Line 162:        For Each item In tokens
Line 163:            list.Add(Integer.Parse(item))
Line 164:        Next
Line 165:
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

662 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question