Dependent list boxes or grid views from access database

[danieljpayne]

Hi, could anyone please help me with some dependent list boxes or grid views. I have a table in an access database which contains the names of categories and their subcategories and am trying to fill a list box or grid view with the list of categories and then have a second list box or grid view contain all the subcategories filtered by which ever category the user has selected.

I'm currently trying to do this with grid views and have the two sets of data filling the grid views but it takes forever to fill when there are only 262 subcategories in total. My method of populating them is probably the culprit.

For count = 0 To Me.TblCategoriesTableAdapter.GetUniqueGroupList.Rows.Count - 1
            dvGroups.Rows.Add(Me.TblCategoriesTableAdapter.GetUniqueGroupList.Item(count).Group)
        Next count

        For count = 0 To Me.TblCategoriesTableAdapter.GetUniqueCategoryList.Rows.Count - 1
            dvCategory.Rows.Add(Me.TblCategoriesTableAdapter.GetUniqueCategoryList.Item(count).Group, Me.TblCategoriesTableAdapter.GetUniqueCategoryList.Item(count).Category)
        Next count

. Btw I tried setting the data sources of the grid views but none of the data would show up.

Can someone please explain the most efficient way to do this?

Many Thanks

Daniel

 

[Arg81]

I assume there is some relation between the two tables? I.E I assume CategoryID in your Category table is related to a CategoryID in your SubCategory table??

If it is, then this is how I would do it;

(a) Create your Category and SubCategory dataTables in the GUI and create the queries:
- Category tableAdapter: standard .Fill (so all categories are loaded)
- SubCategory tableAdapter: standard .Fill (so all subCategories are loaded) AND a .FillByCategoryID (so you provide the CategoryID, and then all subcategories that match this category are the only ones loaded)

(b) Make sure there is a dataRelation between the two dataTables. (Many Categories can have One SubCategory)

(c) On your form drop 2 grids, one for your Category (to do this quickly, open the "Data Sources" panel on the right, and drag and drop Category table to the form - it'll automatically setup the grid)
For the SubCategory table, click the + next to Category in the "Data Sources" tab and then you'll see a sub-table called SubCategory - drag and drop this onto the form as well.

(d) Your form code should have added the 2 lines to fill the dataTables to the Form_Load; i.e you should see

Private Sub Form_Load (blah blah blah...... )

me.CategoryTableAdapter.Fill(me.MyDataSetName.Category)
me.SubCategoryTableAdapter.Fill(me.MyDataSetName.SubCategory)

End Sub

(e) If thats all correct, run your app. You should be able to browse the categories in the "parent" grid, and upon changing the row, the "child" grid should display the sub-categories for the selected category.


If that all works, then I'll explain recursion. It's why I said about a FillBy query, and it's a lot better for loading related data when only selected a subset of parent rows.

Have a go and get back to us

 

[danieljpayne]

Thankyou for your reply. Currently I have the category and sub category data in one table (I also have a thing called group like a super category aswell). So the content of this table looks like this:

Group | Category | Subcategory

Aquatic Products; Filters; Internal
Aquatic Products; Filters; External
Reptile Products; Decoration; Plants
Reptile Products; Food; Dry Food

etc

Should I therefore split this table into 3 and define physical relationships for them or is my method ok?

Basically, this is just a front end system to an access database that contains product information for an online shop.

Thanks

Daniel

 

[Arg81]

It depends. If you get in a situation where you have duplicate data in your table, then you should split into seperates - from your example you can see you should have a GroupID table.
good database practice is to try and "normalise" the data as best as possible.

An example using your data would be;

GroupID Table

GroupID | GroupName
1---------Aquatic Products
2---------Reptile Products

CategoryID Table

CategoryID | CategoryName
1------------Filters
2------------Decoration
3------------Food

SubCategoryID table

SubcategoryID | CategoryID | SubcategoryName
1---------------1-------------Internal
2---------------1-------------External
3---------------2-------------Plants
4---------------2-------------Statues
5---------------3-------------Dry Food
6---------------3-------------Wet Food


Main Table

GroupID | CategoryID | SubCategoryID

1----------1--------------1
1----------1--------------2
2----------2--------------3
2----------3--------------5


You would obviously need a primary key (unique ID) field in there as well, unless you set all 3 columns to be the Primary Key, in which case you will never get the same row twice in that table.

Then in your app on your form, you would have a comboBox that drops and displays ALL the groups and you select what one you want.
You will have a comboBox that drops and displays ALL Categories and you select what one you want
You will have a comboBox that drops and displays ONLY Subcategories related to the selected Category from the Cat. comboBox.

 

[danieljpayne]

Thankyou, although ideally i would like to follow the best database practices, after thinking about amount of work which would be involved by changing this, is it possible to follow your method for dependent listboxes using my current database structure if so, how do I adjust your method to work with this?

Many Thanks

Daniel

 

[Arg81]

to be honest, I'm not entirely sure. The way you have created your database is "flat file", i.e. there are no relations. Since I started DB programming I've always worked with "related" so I've never had the need to use the same table to display more than one thing.

Hopefully someone else can enlighten you on that question, although keep in mind good database practices for next time

I made a post AGES ago about how to make a "bad" database
http://www.vbdotnetforums.com/showthread.php?t=16950

 

[danieljpayne]

Ive decided to take your advice and re structure my database programmatically.

The code below transfers the groups from my old style to a new group table, and im currently working on the part that reads through all my old categories and for each category it gets the associated groupID in the new style table for that group, then saves the category into the new category table.

The problem is, the code randomly hangs at different stages in the category restructuring part, although up until this point the categories have been converted correctly.

Any ideas why this is happening? Thanks

Private Sub cmdImportCategories_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles cmdImportCategories.ItemClick

        Dim response As Boolean
        response = MsgBox("This will convert old style Group : Category : SubCategory data from tblCategories to the new style 3 separate tables data format. Are you sure you wish to continue?", MsgBoxStyle.YesNo)

        If response = True Then

            Dim OldStyleGroup As String
            Dim OldStyleCategory As String
            Dim OldStyleSubCategory As String

            Dim count As Integer
            Dim numRows As Integer
            numRows = Me.TblCategoriesTableAdapter.GetUniqueGroupList.Rows.Count
            Progress.Value = 0
            Progress.Maximum = numRows
            Progress.Visible = True

            'Put each unique old style group into the new style
            For count = 1 To numRows - 1
                OldStyleGroup = Me.TblCategoriesTableAdapter.GetUniqueGroupList.Item(count).Group
                Me.TblGroupTableAdapter.Insert(OldStyleGroup)
                Me.TblGroupTableAdapter.Update(Me.Product_DatabaseDataSet.tblGroup)

                Progress.Value = count - 1
            Next count


            'Put each old style category into the new style

            numRows = Me.TblCategoriesTableAdapter.GetUniqueCategoryList.Rows.Count
            Progress.Maximum = numRows

            For count = 1 To numRows - 1
                OldStyleCategory = Me.TblCategoriesTableAdapter.GetUniqueCategoryList.Item(count).Category
                OldStyleGroup = Me.TblCategoriesTableAdapter.GetUniqueCategoryList.Item(count).Group
                Me.TblCategoryTableAdapter.Insert(GetNewStyleGroupID(OldStyleGroup), OldStyleCategory)

                Progress.Value = count - 1
            Next count
            Me.TblCategoryTableAdapter.Update(Me.Product_DatabaseDataSet.tblCategory)
            Progress.Visible = False

        End If


    End Sub

    Private Function GetNewStyleGroupID(ByVal OldStyle As String)
        Dim countgroup As Integer
        Dim numrows As Integer

        numrows = Me.TblGroupTableAdapter.GetData.Rows.Count

        GetNewStyleGroupID = 0

        'Loop through new style group listing and get corresponding new style value for the old style
        For countgroup = 1 To numrows - 1

            If Me.TblGroupTableAdapter.GetData.Item(countgroup).GroupName = OldStyle Then
                GetNewStyleGroupID = Me.TblGroupTableAdapter.GetData.Item(countgroup).GroupID
                Exit For
            End If

        Next countgroup


    End Function