DataGrid Question - Separate Dataset

[ReportsNerd]

Hi, my windows form has a tab control. The code below to search for a person by name is working great. However, Im not sure how to do the next part of my form. I need another tab that just has a data grid view. The grid will be sourced from a different table than what I have in my code below, however, the tables link on a field called File_No. The grid will just have a few columns from a table called Trans (transactions). So, after I look up a person, I will be able to see all their transactions. Help/suggestions appreciated. Thanks.

Public Class Menu
    Dim mySearchString As String
    Dim myConnectionString As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Application.StartupPath & "\TestDB1.accdb'")
    Dim daDebtors As New OleDb.OleDbDataAdapter("Select * From DEBTOR ORDER By NAME1", myConnectionString)
    Dim myDataSet As New DataSet
    Dim myBindingSource As BindingSource
Private Sub Menu_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        daDebtors.Fill(myDataSet, "Debtors")
        myBindingSource = New BindingSource(myDataSet, "Debtors")
    End Sub
Private Sub BtnSeek_Click(sender As System.Object, e As System.EventArgs) Handles BtnSeek.Click
        mySearchString = InputBox("Name:", "Search", "Anderson")
        DisplaySearchResults()
    End Sub
Private Sub DisplaySearchResults()
       
        txtName1.DataBindings.Clear()
       
        With txtName1.DataBindings.Add("Text", myBindingSource, "NAME1")
         End With
        myBindingSource.Filter = "Name1 Like '" & mySearchString & "%'"
End Sub

 

[IanRyder]

Hi,

You need to look at Parent/Child data binding principals. Have a look at this post by jmcilhinney:-

Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)-VBForums

Hope that helps.

Cheers,

Ian

 

[ReportsNerd]

Hi Ian, I’m sure I need to use the data relation method described in the post, but I'm still confused about a couple things. My transaction table has over 200k rows. As you can tell from my SQL in my earlier example, I’m doing a select * from my original "parent" table. In my forms, I need 40 columns from my parent table and 5 columns from my child (details) table. Going to display the child records in the grid. Sorry, I’m having problems connecting the dots here. I'm thinking I need to filter earlier now to prevent all 200K rows from loading into the dataset. Do I need to join the details table in my original data adapter SQL statement and also perform my filter on that line now?

Help/Suggestions appreciated. Thanks.

 

[IanRyder]

Hi,

This all depends on how your Debtors table is being returned from your Database. Either way you are going to need two tables in your DataSet, being your Debtors table and your Transactions table. There are two basic principals as to how a Debtors, and ultimately a Transactions, table could be returned from your Data source:-

1) The Debtors table contains EVERY Debtor you have ever had and therefore the selection of a Parent Debtor would need to pull in it's child transactions from every possible transaction ever entered. Therefore you would need to bring in all your 200K records to do this. This would be the wrong way to do this.

2) The Alternative, and this SHOULD be the TRUE case, is to have Query's based on the current Debtors owing money? In this case you can then use an Inner Join on your SQL strings for BOTH your Debtors and Transactions tables to limit the records that are returned to your project based on the current Debtors owing money and therefore ignoring any Debtors that do not owe money.

Both these two principals would then be subject to the Parent/Child data binding principals discussed earlier.

Hope that helps.

Cheers,

Ian