Click here to Skip to main content
14,297,637 members

Searching related tables with multiple criteria

WooHoo 9 asked:

Open original thread
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
        '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!
Tags: C#, VB, VB.NET


When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).

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