Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment
Problem with listing Department and subDepartment Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Go Back  Xtreme Visual Basic Talk > > > Problem with listing Department and subDepartment


Reply
 
Thread Tools Display Modes
  #1  
Old 06-05-2010, 09:02 AM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default Problem with listing Department and subDepartment


Hello
I am trying to read infofmation from the database where i list each department and thier subdepartments in the database

so i have all departments and subdepartments in one database Called Departments there is also a field called SubDeptID

the SubDeptID will have the value 0 if the department is a parent or a top level other wise it will have it's parent DeptID


now i used 2 function to try to create this - it seems that the function add only the parents to the listbox but not the childrens
can some one please help me to correct this code

here are my functions

thanks


Code:
    Public Shared Sub FillDepartment(ByVal DropName As Telerik.WinControls.UI.RadListBox)

        Dim displaymember As String = "DptName"
        Dim valuemember As String = "Deptid"
        Dim i As Integer = 0
        Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT " & displaymember & ", " & valuemember & "  FROM Department WHERE SubDeptID = 0 ", Common.GetConnection)
        Dim ds As New DataSet
        Dim ID As Integer

        da.Fill(ds, "Departments")

        DropName.DataSource = ds.Tables(0).DefaultView

        For Each DataRow In ds.Tables(0).Rows

            DropName.DisplayMember = displaymember
            DropName.ValueMember = valuemember

            ID = ds.Tables(0).Rows(i)("Deptid")
            FillSubDepartment(DropName, ID)
            i += 1
        Next

        da.Dispose()
        ds.Dispose()

    End Sub

Code:
    Public Shared Sub FillSubDepartment(ByRef DropName As Telerik.WinControls.UI.RadListBox, ByVal ID As Integer)

        Dim displaymember1 As String = "DptName"
        Dim valuemember1 As String = "Deptid"

        Dim da2 As SqlDataAdapter = New SqlDataAdapter("SELECT " & displaymember1 & ", " & valuemember1 & "  FROM Department WHERE SubDeptID = '" & ID.ToString & "' ", Common.GetConnection)
        Dim ds2 As New DataSet
        Dim i As Integer = 0

        da2.Fill(ds2, "Departments")

        'dropname.DataSource = ds.Tables("Departments").DefaultView

        For Each DataRow In ds2.Tables(0).Rows
            DropName.DisplayMember = displaymember1
            DropName.ValueMember = valuemember1

            ID = ds2.Tables(0).Rows(i)("Deptid")
            i += 1
            FillSubDepartment(DropName, ID)

        Next
        da2.Dispose()
        ds2.Dispose()

    End Sub
Reply With Quote
  #2  
Old 06-05-2010, 09:34 AM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

I forgot to mention
here how i call this function from a different file

Code:
Functions.FillDepartment(Departments_ListBox)
Reply With Quote
  #3  
Old 06-05-2010, 10:59 AM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

There are a few things that could probably be tidied up first to help make identifying the problem easier...

Firstly in both functions you are executing code like
Code:
DropName.DisplayMember = displaymember1
DropName.ValueMember = valuemember1
in a loop - this will be setting the properties to the same values repeatedly and should be moved outside of the loop.

I also noticed the code
Code:
         FillSubDepartment(DropName, ID)
at the end of the FillSubDepartment method - do you really need to call this method recursively to populate the control?

I am still at a bit of a loss as to exactly what you are trying to achieve however. Do you want to fill a single control with a list of all departments and subdepartments? If so could you not just select all the departments and bind to that? Or am I missing the point?
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #4  
Old 06-05-2010, 11:26 AM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

well
the first 2 lines you mentions should if i am not mistaking add valuemember and display member from the dataset to the listbox. Now this may be my error but i am not sure how to set it.

On the other line here what happens

I call the first function when it get all departments where SubDptID = 0 (This mean all the main departments) now with in that loop because i have to check every single row to see if there are any children department so this will call that second function.

Now after that, we go and check only for sub departments. so we pass the Parent ID then we get all the departments where SubDeptID = ID and so on we call it recursively so if there are sub department of the first sub department

so we get some like this
>Main Department 1
>>Sub Dept 1
>>>Sub Dept of (Sub Dept 1)
>>>> Keep going with sub departments until there is not more
>>Sub Dept 2
>Main Department 2
>Main Department 3
>So on

Thanks for your time
Reply With Quote
  #5  
Old 06-05-2010, 01:04 PM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

Quote:
Originally Posted by malhayek View Post
well
the first 2 lines you mentions should if i am not mistaking add valuemember and display member from the dataset to the listbox. Now this may be my error but i am not sure how to set it.
You don't need to set them to the same value repeatedly though, currently you are assigning exactly the same value to these two properties on every iteration of the loop.

In the SELECT statement you are building you don't need to put the single quotes around the ID if it is defined as a numerical type in your database, you may want to look at using parametrised queries instead of string concatenation at this point.

You also seem to be making the loop harder than it needs to be when you have
Code:
        For Each DataRow In ds.Tables(0).Rows

            DropName.DisplayMember = displaymember
            DropName.ValueMember = valuemember

            ID = ds.Tables(0).Rows(i)("Deptid")
            FillSubDepartment(DropName, ID)
            i += 1
        Next
you seem to be using a For Each loop but treating it like a For Next loop
Code:
        For Each row As DataRow In ds.Tables(0).Rows

            DropName.DisplayMember = displaymember
            DropName.ValueMember = valuemember

            ID = row("Deptid")
            FillSubDepartment(DropName, ID)
        Next
Is probably what you are after. The point of a For Each is you don't need to specify indexes into the collection you are working with.

The method FillSubDepartment doesn't appear to be adding anything to the listbox either, that is probably why you are only seeing the Top Level Departments being added.

If you want to data bind you will need to build the list of all departments and sub departments in the correct order and then bind to that, otherwise you will need to manually add the items to the listbox as you work through the loops.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #6  
Old 06-05-2010, 02:05 PM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

hello
thank for the help again

okay i fixed the loops but it still is not working - only the parents are being displayed


now you said

Quote:
If you want to data bind you will need to build the list of all departments and sub departments in the correct order and then bind to that, otherwise you will need to manually add the items to the listbox as you work through the loops.
how do i do this - i can list all of them but how can i list the sub departments under thier parents like i should u earlier???


thanks
Reply With Quote
  #7  
Old 06-05-2010, 02:22 PM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

You could declare a List(Of ...) variable and have the FillDepartment and FillSubDepartment methods append their results to this list.

Then once the list has been built from all records data bind to it.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #8  
Old 06-05-2010, 03:58 PM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

Oh I think I may know what u mean but that way I will not have unlimited sub parents. What u mean is to create a list for every parent's children. If I don't do this in a systimatic way it is not going to give me unlimited sub departments



Is it possible for my code to be fixed. I did some like the above code using php but I am new to vb.net. I think my only problem is adding the data to list box
Reply With Quote
  #9  
Old 06-13-2010, 01:30 PM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

i am unable to reach to a conclusion - can someone help me please
Reply With Quote
  #10  
Old 06-13-2010, 06:54 PM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

Create a single List object and when you call FillDepartment it will add the first top level department to this list, when you call the FillSubDepartments method it will then append to this list. Once the FilSubDepartments completes FillDepartment can add the next top level department to the list and so on.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #11  
Old 06-14-2010, 03:03 PM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

Hello
thanks for your help

i am still having issue with this i coded based on your instructions but i am still getting a problem i am not sure what am i doing wrong here.

here is my code
if a file "module" called GlobalModule i have the following code
Code:
Public Departments_List As List(Of DataRow)
also in another file "Class" called NewDepartment i have the following code

Code:
    Public Shared Sub FillDepartment(ByRef DropName As Telerik.WinControls.UI.RadListBox)

        Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT DptName, Deptid FROM Department WHERE SubDeptID = 0 ", Common.GetConnection)
        Dim ds As New DataSet
        Dim ID As Integer

        da.Fill(ds, "Departments")

        For Each row As DataRow In ds.Tables(0).Rows

            GlobalModule.Departments_list.Add(row)
            ID = row("Deptid")
            FillSubDepartment(ID)

        Next

        da.Dispose()
        ds.Dispose()


        DropName.DataSource = GlobalModule.Departments_list

        For Each item As DataRow In GlobalModule.Departments_list
            DropName.DisplayMember = item("DptName").ToString
            DropName.ValueMember = item("Deptid")
        Next

    End Sub




    Public Shared Sub FillSubDepartment(ByVal ID As Integer, Optional ByVal Empty As Boolean = True)

        Dim da2 As SqlDataAdapter = New SqlDataAdapter("SELECT  DptName, Deptid  FROM Department WHERE SubDeptID = " & ID & " ", Common.GetConnection)
        Dim ds2 As New DataSet



        da2.Fill(ds2, "Departments")

        For Each row As DataRow In ds2.Tables(0).Rows

            GlobalModule.Departments_list.Add(row)

            ID = row("Deptid")
            FillSubDepartment(ID, False)

        Next
        da2.Dispose()
        ds2.Dispose()

    End Sub
finally from a file called AddNewDepartment I call the function by executing the following code

Code:
NewDepartment.FillDepartment(Me.DepartmentsList)

what do you think is the problem here?

thanks so much for the help again
Reply With Quote
  #12  
Old 06-14-2010, 03:58 PM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

Quote:
i am still getting a problem i am not sure what am i doing wrong here.
without knowing what the problem is it is fairly tricky to figure out what is wrong....

However you are still doing
Code:
  For Each item As DataRow In GlobalModule.Departments_list
            DropName.DisplayMember = item("DptName").ToString
            DropName.ValueMember = item("Deptid")
        Next
You should just be setting these to the name of the appropriate fields and not the contents of the last row in the list.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #13  
Old 06-15-2010, 10:51 AM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

Hello
Sorry the problem is that the listbox stays empty

Quote:
You should just be setting these to the name of the appropriate fields and not the contents of the last row in the list.
I did not quite get what you meant here
Reply With Quote
  #14  
Old 06-15-2010, 12:45 PM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

http://msdn.microsoft.com/en-us/libr...laymember.aspx

When databinding a combo box you specify the underlying data source for the .DataSource property, the name of the property or field to use for the display as the DisplayMember property and the name of the property or field to use as the value for the ValueMember.

You are assigning the actual values of the List to the DisplayMember and ValueMember properties when you should be assigning the names.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #15  
Old 06-15-2010, 01:11 PM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

okay I think i am getting closer to solve this problem

Now i get the following displayed in the listbox

System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow



Here is what i have changed


Code:
        For Each item As DataRow In GlobalModule.Departments_list
            DropName.DisplayMember = item("DptName").ToString
            DropName.ValueMember = item("Deptid")
        Next

to

Code:
        ListBox.DataSource = GlobalModule.Departments_list
        ListBox.DisplayMember = "DptName"
        ListBox.ValueMember = "Deptid"
        GlobalModule.Departments_list.Clear()



Also I changed

Code:
Public Departments_list As List(Of DataRow)

to

Code:
Public Departments_list As New List(Of DataRow)

Now what do i do??

thanks

Last edited by malhayek; 06-15-2010 at 01:35 PM.
Reply With Quote
  #16  
Old 06-15-2010, 03:39 PM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

You are databinding to a list and then clearing the list, the listbox is correctly showing you the contents of an empty list.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #17  
Old 06-15-2010, 04:16 PM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

hello
Actually i clear the temporary list after i finish data binding the list. Anyhow, i removed the line that clear the list and i still get the same thing

Departments_list is a temporary list

System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow

what could i do?

thanks

Last edited by malhayek; 06-15-2010 at 04:27 PM.
Reply With Quote
  #18  
Old 06-16-2010, 08:28 PM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

After spending a bit of time looking at this it would appear that a collection of DataRow objects can't be used as a data source in this fashion, which is annoying but such is life.

One option would be to create a simple class to wrap the Department / DepartmentID and populate a list of these in your code. The class could be as simple as
Code:
Public Class Department

public property DepartmentId as integer
public property DepartmentName as string

public Sub New(id as integer, name as string)
departmentid = id
departmentname = name
end sub
end class
The code is using the VS 2010 syntax for properties, excuse any small errors as I currently have no access to visual studio....

The List(Of ...) declaration could now be changed to
Code:
Public Departments_List As List(Of Department)
You could then amend your existing code to look like
Code:
 For Each row As DataRow In ds2.Tables(0).Rows

            GlobalModule.Departments_list.Add(new Department(row(0), row(1))

            ID = row("Deptid")
            FillSubDepartment(ID, False)

        Next
and similar for the FillSubDepartments method

You should then be able to data bind to the list object with code similar to
Code:
ListBox1.DataSource  = GlobalModule.Departments_list
ListBox1.DisplayMember = "DepartmentName"
ListBox1.ValueMember = "DepartmentID"
If you are using a version of Visual Studio more recent than 2005 (i.e. 2008 or later) you might want to investigate Linq To Sql or the Entity Framework as this would avoid the need to use DataTables etc to get at the data.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #19  
Old 06-17-2010, 01:24 PM
malhayek malhayek is offline
Regular
 
Join Date: May 2010
Posts: 68
Default

Hello

First of all, I am using VB 9 and i am using SQL to read from the databases.

now I followed your solution and here is what i have now

new class in new file called Department and here is it's code

Code:
Public Class Department

    Public DepartmentId() As Integer
    Public DepartmentName() As String

    Public Sub New(ByVal id As Integer, ByVal name As String)
        DepartmentId(0) = id
        DepartmentName(0) = name
    End Sub
End Class
then i modified the file "GlobalModules" and updated the definition Line to

Code:
Public Departments_list As List(Of Department)


and in my NewDepartment Class i have these two functions

Code:
    Public Shared Sub FillDepartment(ByRef ListBox As Telerik.WinControls.UI.RadListBox)

        Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT DptName, Deptid FROM Department WHERE SubDeptID = 0 ", Common.GetConnection)
        Dim ds As New DataSet
        Dim ID As Integer

        da.Fill(ds, "Departments")

        For Each row As DataRow In ds.Tables(0).Rows

            GlobalModule.Departments_list.Add(New Department(row(0), row(1)))

            ID = row("Deptid")
            FillSubDepartment(ID)

        Next

        da.Dispose()
        ds.Dispose()


        ListBox.DataSource = GlobalModule.Departments_list

        ListBox.DisplayMember = "DepartmentName"
        ListBox.ValueMember = "DepartmentId"

    End Sub




    Public Shared Sub FillSubDepartment(ByVal ID As Integer, Optional ByVal Empty As Boolean = True)

      
        Dim da2 As SqlDataAdapter = New SqlDataAdapter("SELECT  DptName, Deptid  FROM Department WHERE SubDeptID = " & ID & " ", Common.GetConnection)
        Dim ds2 As New DataSet



        da2.Fill(ds2, "Departments")

        For Each row As DataRow In ds2.Tables(0).Rows

            GlobalModule.Departments_list.Add(New Department(row(0), row(1)))

            ID = row("Deptid")

            FillSubDepartment(ID, False)

        Next
        da2.Dispose()
        ds2.Dispose()

    End Sub

Unfortunatly there is nothing being displaied in the listbox!!

so what is the problem now??

PS: I have modified the class you provided because it was giving me blue lines under the following

Code:
Public Class Department

public property DepartmentId as integer
public property DepartmentName as string

public Sub New(id as integer, name as string)
departmentid = id
departmentname = name
end sub
end class


thanks
Reply With Quote
  #20  
Old 06-17-2010, 02:10 PM
PlausiblyDamp's Avatar
PlausiblyDampProblem with listing Department and subDepartment PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

Try changing the class to
Code:
Public Class Department

    Public DepartmentId As Integer
    Public DepartmentName As String

    Public Sub New(ByVal id As Integer, ByVal name As String)
        DepartmentId = id
        DepartmentName = name
    End Sub
End Class
and see if that helps, if not I will have a proper look later this evening.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
 
Problem with listing Department and subDepartment
Problem with listing Department and subDepartment
 
-->