categories and subcategories display in the dropdown/gridview (part 2)

bsarahimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Meir RivkinFull stack Software EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Meir RivkinFull stack Software EngineerCommented:
when the SelectedIndexChanged is triggered, what are the values in the list?

>>Input string was not in a correct format. -> this exception occurs because the item cannot be converted to integer.
so what is the value of the item when the exception rises?
0
bsarahimAuthor Commented:
any of the item it is giving a error..
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

bsarahimAuthor Commented:
waiting for you reply.. thanks
0
Meir RivkinFull stack Software EngineerCommented:
when the SelectedIndexChanged is triggered, what are the values in the Listbox1?
0
Meir RivkinFull stack Software EngineerCommented:
the listbox1 should contain items like the following:
1->2->6
2->5->8
3->6

in the values in the listbox are not in this format, none of the code will work.
0
bsarahimAuthor Commented:
thanks..

Pls see the image, which has the details.. thanks
picture.png
0
Meir RivkinFull stack Software EngineerCommented:
so instead of list we need dictionary to map category id to name.
can u post the full code?, i'll make the change on that
0
bsarahimAuthor Commented:
Here is the full code.. thanks

 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

-------------------------------------
page_onload


        If Not (Page.IsPostBack) Then



            Dim sqlConn As New SqlConnection
            Dim strConn As String

            Dim cmd As New SqlClient.SqlCommand

            strConn = System.Configuration.ConfigurationManager.ConnectionStrings("JC").ConnectionString
            sqlConn = New SqlConnection(strConn)
            'Dim reader1, reader As SqlDataReader

            cmd.Connection = sqlConn

            sqlConn.Open()
            Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT [Id] ,[Name] ,[ParentCategoryId]  FROM [nopCommerce].[dbo].[Category]", sqlConn)


            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()

                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
                Listbox1.Items.Add(name)

           
            Next





        End If
0
Meir RivkinFull stack Software EngineerCommented:
create a member in your page class:
Dim MapCategory As New Dictionary(Of String, Integer)

--page_onload


        If Not (Page.IsPostBack) Then



            Dim sqlConn As New SqlConnection
            Dim strConn As String

            Dim cmd As New SqlClient.SqlCommand

            strConn = System.Configuration.ConfigurationManager.ConnectionStrings("JC").ConnectionString
            sqlConn = New SqlConnection(strConn)
            'Dim reader1, reader As SqlDataReader

            cmd.Connection = sqlConn

            sqlConn.Open()
            Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT [Id] ,[Name] ,[ParentCategoryId]  FROM [nopCommerce].[dbo].[Category]", sqlConn)


            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()

                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
MapCategory.Add(name, parentId)
                Listbox1.Items.Add(name)

           
            Next

        End If

Open in new window


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

        Dim curItem As String = Listbox1.SelectedItem.Value

--get parentid of selected item
            Dim parentCatId As Integer = MapCategory(curItem))
            Response.Write(parentCatId)

        End If

    End Sub

Open in new window

0
bsarahimAuthor Commented:
thanks

im getting the following error in any of the selection of the dropdownlist


The given key was not present in the dictionary.
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.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.

Source Error:


Line 154:
Line 155:        '--get parentid of selected item
Line 156:        Dim parentCatId As Integer = MapCategory(curItem)
Line 157:        Response.Write(parentCatId)
Line 158:
0
Meir RivkinFull stack Software EngineerCommented:
on line 156, what is the value of curItem when the exception occurs>?
0
Meir RivkinFull stack Software EngineerCommented:
found a bug in the for each (line 34).
replace it with:

            For Each item In rows
dim orgParentId as Integer
                orgParentId =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
MapCategory.Add(name, orgParentId )
                Listbox1.Items.Add(name)

           
            Next

Open in new window


now, the map contains the following:
key:                         value:
A->B->C                  2

while 2 is the parentid of A which is the id of B.

so each the selectedIndex change event will take the current select item from the list and using the map get its parent id.
0
bsarahimAuthor Commented:
Apparel & Shoes->Apparel accessories
testing11

etc.,

  Dim curItem As String = Listbox1.SelectedItem.Value
        Response.Write(curItem)
        Response.End()


        '--get parentid of selected item
        Dim parentCatId As Integer = MapCategory(curItem)
        Response.Write(parentCatId)
0
bsarahimAuthor Commented:
i have noticed all the values are 00000000000000000000
when i write   Response.Write(parentId)


 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
                MapCategory.Add(name, parentId)
                Response.Write(parentId)
                Listbox1.Items.Add(name)


                'List.Items(parentId).Text = name
            Next
0
Meir RivkinFull stack Software EngineerCommented:
did u update the code according to my last comment?
0
bsarahimAuthor Commented:
yes.. the whole code is again..

 If Not (Page.IsPostBack) Then



            Dim sqlConn As New SqlConnection
            Dim strConn As String

            Dim cmd As New SqlClient.SqlCommand

            strConn = System.Configuration.ConfigurationManager.ConnectionStrings("JC").ConnectionString
            sqlConn = New SqlConnection(strConn)
            'Dim reader1, reader As SqlDataReader

            cmd.Connection = sqlConn

            sqlConn.Open()
            Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT [Id] ,[Name] ,[ParentCategoryId]  FROM [nopCommerce].[dbo].[Category]", sqlConn)


            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()
                Response.Write(parentId)
                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
                MapCategory.Add(name, parentId)
                Response.Write("<BR>" & parentId)
                Listbox1.Items.Add(name)
             
            Next

        End If


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





        Dim curItem As String = Listbox1.SelectedItem.Value


        Response.Write(curItem)

        Response.End()


        '--get parentid of selected item
        Dim parentCatId As Integer = MapCategory(curItem)
        Response.Write(parentCatId)
0
Meir RivkinFull stack Software EngineerCommented:
u didn't replace the for each loop with the code i've posted:

If Not (Page.IsPostBack) Then



            Dim sqlConn As New SqlConnection
            Dim strConn As String

            Dim cmd As New SqlClient.SqlCommand

            strConn = System.Configuration.ConfigurationManager.ConnectionStrings("JC").ConnectionString
            sqlConn = New SqlConnection(strConn)
            'Dim reader1, reader As SqlDataReader

            cmd.Connection = sqlConn

            sqlConn.Open()
            Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT [Id] ,[Name] ,[ParentCategoryId]  FROM [nopCommerce].[dbo].[Category]", sqlConn)


            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

Dim orgParentId as Integer
                orgParentId =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
MapCategory.Add(name, orgParentId )
Response.Write("<BR>" & parentId)
                Listbox1.Items.Add(name)

           
            Next
                                            

        End If

Open in new window

0
bsarahimAuthor Commented:
Based on your code, the dropdown will display like this

pls see the image
Untitled-picture1.png
0
Meir RivkinFull stack Software EngineerCommented:
change:
Response.Write("<BR>" & parentId)

with:
Response.Write("<BR>" & parentId)
Response.Write("<BR>" & name)

what is printed?
0
bsarahimAuthor Commented:
thanks.. it is printing like this below

0
Books
0
Computers
0
Desktops
0
Notebooks
0
Accessories
0
Software
0
Games
0
Electronics
0
Camera, photo
0
Cell phones
0
Apparel & Shoes
0
Shirts
0
Jeans
0
Shoes
0
Apparel accessories
0
Digital downloads
0
Jewelry
0
Gift Cards
0
Battery
0
testing11
0
tsting12
0
tsting12ww
0
tsting12wwee
0
tsting12wwees
0
battery23232
0
3rd
0
testing2323
0
Hello Group
0
Meir RivkinFull stack Software EngineerCommented:
i reviewd the code few times i dont understand whats wrong.

under this line:
 While parentId > 0

add:
Response.Write("<BR>" & name)

and remove the other Response.Write() lines.
what is it printing?
0
bsarahimAuthor Commented:
I have added the below  after While parentId > 0, but I feel. nothing is getting display..

 While parentId > 0

                    Response.Write("<BR>jjjj" & name)

thanks
0
Meir RivkinFull stack Software EngineerCommented:
that means parentId is always 0 for all rows.
check  line 34:
orgParentId =parentId = CInt(item("parentcategoryid"))

parentId is always 0 which means the datatable has wrong values for parentcategoryid.
i'd check  in DB that parentcategoryid is not 0 for all rows.
then run the query in your DB and see that the results are correct.

add those lines:
Dim ids = String.Join(",", dt.Rows.Cast(Of DataRow)().[Select](Function(n) n("ParentCategoryId")).ToArray())
Response.Write("<BR>ids: " & ids )

Open in new window


under
 
Dim dt As DataTable = dataSet.Tables(0)

Open in new window

0
bsarahimAuthor Commented:
Im getting the values

ids: 0,0,2,2,2,2,2,0,8,8,0,11,11,11,11,0,0,0,0,0,0,0,0,0,20,25,19,27



----------------------------------------------
The full code is

If Not (Page.IsPostBack) Then



            Dim sqlConn As New SqlConnection
            Dim strConn As String

            Dim cmd As New SqlClient.SqlCommand

            strConn = System.Configuration.ConfigurationManager.ConnectionStrings("JC").ConnectionString
            sqlConn = New SqlConnection(strConn)
            'Dim reader1, reader As SqlDataReader

            cmd.Connection = sqlConn

            sqlConn.Open()
            Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT [Id] ,[Name] ,[ParentCategoryId]  FROM [nopCommerce].[dbo].[Category]", sqlConn)


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


            Dim dt As DataTable = dataSet.Tables(0)

            Dim ids = String.Join(",", dt.Rows.Cast(Of DataRow)().[Select](Function(n) n("ParentCategoryId")).ToArray())
            Response.Write("<BR>ids: " & ids)


            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

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

                While parentId > 0

                    Response.Write("<BR>jjjj" & name)


                    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
                MapCategory.Add(name, orgParentId)
                'Response.Write("<BR>" & parentId)
                'Response.Write("<BR>" & name)


                Listbox1.Items.Add(name)


            Next


        End If
0
Meir RivkinFull stack Software EngineerCommented:
ok so datatable is fine.

under             For Each item In rows
put  Response.Write("<BR>" & CInt(item("ParentCategoryId")))
0
bsarahimAuthor Commented:
im getting this values.. thanks

0
0
0
0
0
0
0
0
20
25
19
27
0
Meir RivkinFull stack Software EngineerCommented:
so maybe it's case sensitive, change
orgParentId = parentId = CInt(item("parentcategoryid"))

to
orgParentId = parentId = CInt(item("ParentCategoryId"))
0
bsarahimAuthor Commented:
after copy paste, here is the full code..

but still the drop down values are plainvalues



 If Not (Page.IsPostBack) Then



            Dim sqlConn As New SqlConnection
            Dim strConn As String

            Dim cmd As New SqlClient.SqlCommand

            strConn = System.Configuration.ConfigurationManager.ConnectionStrings("JC").ConnectionString
            sqlConn = New SqlConnection(strConn)
            'Dim reader1, reader As SqlDataReader

            cmd.Connection = sqlConn

            sqlConn.Open()
            Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT [Id] ,[Name] ,[ParentCategoryId]  FROM [nopCommerce].[dbo].[Category]", sqlConn)


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


            Dim dt As DataTable = dataSet.Tables(0)

            Dim ids = String.Join(",", dt.Rows.Cast(Of DataRow)().[Select](Function(n) n("ParentCategoryId")).ToArray())
            'Response.Write("<BR>ids: " & ids)


            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
                ' Response.Write("<BR>" & CInt(item("ParentCategoryId")))

                Dim orgParentId As Integer
                orgParentId = parentId = CInt(item("ParentCategoryId"))
                Dim name As String = item("Name").ToString()

                While parentId > 0

                    '   Response.Write("<BR>jjjj" & name)


                    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
                MapCategory.Add(name, orgParentId)
                'Response.Write("<BR>" & parentId)
                'Response.Write("<BR>" & name)


                Listbox1.Items.Add(name)


            Next


        End If
Untitled-picture1.png
0
Meir RivkinFull stack Software EngineerCommented:
change:
    If row IsNot Nothing Then
                        parentId = CInt(row("parentcategoryid"))
                        name = String.Format("{0}->{1}", row("Name"), name)
                    End If

to

    If row IsNot Nothing Then
                        parentId = CInt(row("ParentCategoryId"))
                        name = String.Format("{0}->{1}", row("Name"), name)
                    End If
0
Meir RivkinFull stack Software EngineerCommented:
    For Each item In rows

                Dim orgParentId As Integer
                orgParentId = 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)
                        Response.Write("<BR>" &  name & parentId)

                    End If
                End While
                MapCategory.Add(name, orgParentId)

                Listbox1.Items.Add(name)

            Next

Open in new window

0
bsarahimAuthor Commented:
still the same plain dropdown values instead of ->

and when i click the selected index event

Server Error in '/' Application.
--------------------------------------------------------------------------------

The given key was not present in the dictionary.
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.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.

Source Error:


Line 221:
Line 222:        '--get parentid of selected item
Line 223:        Dim parentCatId As Integer = MapCategory(curItem)
Line 224:        Response.Write(parentCatId)
Line 225:
0
Meir RivkinFull stack Software EngineerCommented:
this is .net so u should be able to debug your code.
i run this on my app few times and i couldn't figure out what's the problem.
in my last post i cleaned up all the response.write() besides the on on line 14.

 Response.Write("<BR>" &  name & parentId)

put breakpoint there and check if the name is being concatenated with the parent names.
the logic is that for each row we take the id and we look for the row of its parent using the ParentCategoryId (line 9), untill parentid equals 0.
then we move to the next row.
while doing so we concatenate the name and the parent name separated by -> (line 13)
0
bsarahimAuthor Commented:
ok .. no issues.. I will debug and come back to you on this..

1. totally differnt query: I want to display categroy, subcategories,.. in the treeview..
2.
. 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)
0
Meir RivkinFull stack Software EngineerCommented:
asp treeview?
0
bsarahimAuthor Commented:
asp.net 2/3.5 treeview.. yes..
0
bsarahimAuthor Commented:
still waiting for your solution.. thanks
0
Meir RivkinFull stack Software EngineerCommented:
Please close this thread and open new question about the treeview
10x
0
bsarahimAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for bsarahim's comment #37812042
Assisted answer: 500 points for sedgwick's comment #37925687

for the following reason:

very helpful resource! Keep it up!
0
Meir RivkinFull stack Software EngineerCommented:
u forgot to reward the points, 10x
0
Meir RivkinFull stack Software EngineerCommented:
10x again for the points.
0
bsarahimAuthor Commented:
good support
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.