Click here to Skip to main content
14,329,627 members
Rate this:
Please Sign up or sign in to vote.
See more:
I need to allow users to search on any criteria in a multiple table (with multiple relations) dataset. Once the criteria is entered and the user presses the search button, I'd like to allow the user to scroll through a list box containing a key field of each of the found records. As each of the key fields is selected in the list box, all records tied to that key will show up on the form. There are many tables, and I haven’t gotten binding filtering or dataset selects or DataViewManagers to work. So far, creating a view of all the tables and running the following code comes closest to working. This is a very simplistic version (using only one search criteria) of what I’ve currently got:
Private Sub SearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchButton.Click
        Me.tblChildForSearchTableAdapter.FillByUser(Me.DsSearch. tblChildForSearch, SearchTextBox.Text)
        'Create a table of distinct DocIDs
        Dim Parentdt As DataTable
        'Parentdt = DsSearch.VwAllDocSearch.DefaultView.ToTable(True, "DocID")
        Parentdt = DsSearch. tblChildForSearch.DefaultView.ToTable(True, New String() {"DocID", "IndexNo", "FileNo"})
        'Get the number of rows in the new table
        Dim parentdtCount As Integer
        parentdtCount = Parentdt.Rows.Count
        'Copy the rows in parentdt to tblParentForSearch
        For i = 0 To parentdtCount - 1
            DsSearch.tblParentForSearch.ImportRow(Parentdt.Rows(i))
        Next
        'Create a parent,child relation between tblParentForSearch and tblChildForSearch
        Dim colChildID As DataColumn = DsSearch. tblChildForSearch.Columns("DocID")
        Dim colParentID As DataColumn = DsSearch.tblParentForSearch.Columns("DocID")
    
        Dim dr As DataRelation
        dr = New DataRelation("Parent2Child", colParentID, colChildID)

        'Set up binding sources
        Dim ParentBindingSource = New BindingSource
        With ParentBindingSource
            .DataMember = "tblParentForSearch"
            .DataSource = DsSearch
        End With
        
        Dim ChildBindingSource = New BindingSource
        With ChildBindingSource
            .DataMember = "Parent2Child"
            .DataSource = ParentBindingSource
        End With
    End Sub

I then bind a listbix to the ParentBindingSource and a datagridview to the ChildBindingSource. Although both objects fill with the correct data, there’s no binding or connection between the two. I'm not sure where I'm going wrong.
I have about 10 child tables and 1 parent table. This is the only thing I’ve come up with that’s come close to working. Any insight on my code or other ways to accomplish this would be very appreciated!
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

When working with complex data it is best practice to do most of your work on the database side.

First of all create primary-foreign key relationships on your database(assuming that it is normalised)
Create a stored procedure to give you the first result set to populate your list.

Create a second stored procedure to give the result set required after selection from the list.

Once you are happy that both procedures give the correct results start to work on you front end.

The first part of your user interface should provide the parameters for the first SP that needs to run on your search button. The second procedure would take the selection from the list and return the required data.
   
Comments
WooHoo 9 5-Mar-11 18:40pm
   
I may have been looking at this all wrong. I'm going to give that a shot now.
Thank-you so much!
milenalukic 5-Mar-11 19:01pm
   
You're welcome. Let me know how you get along and if you need any help.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100