Parent and child data...populate datafrid with child..through for each statement??

I have to routines...
btnData_Click - I get data from a database)DEPT / EMP tables... and load data into a dataset(ds) as two tables DEPT/EMP....EMP is a child table...many emps to one DEPT....I add the data to datagrids dgDEPT and dgEMP so  can see that the data exists....

If I click on btnData_Click I want to be able to show the children of dgDEPT of the based on the CURRENTROWINDEX of which I have setup for output in dgDEPT_CurrentCellChanged..this works and I can show in a listbox or console output...

However, I am wanting to place this in a datagrid dgMAIN...
can I do this?

My goal..have a datagrid with dept listing...i select dept 10 and get all the emps that have deptno = 10...i will then attempt to edit the children...

I hope this makes since...

====
    Private Sub btnData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnData.Click
        oOleDbConnection = New OleDb.OleDbConnection(sConnString)
        oOleDbConnection.Open()
        If ConnectionState.Open Then
            TextBox1.Text = "Connection is made to Main"
            TextBox1.BackColor = Color.Green
        Else
            TextBox1.Text = "Unable to make connection"
            TextBox1.BackColor = Color.Red
        End If

        Dim strEMP, strDEPT As String
        strEMP = "Select * from EMP"
        strDEPT = "Select * from DEPT"

        Dim daEMP = New OleDb.OleDbDataAdapter(strEMP, oOleDbConnection)
        Dim daDEPT = New OleDb.OleDbDataAdapter(strDEPT, oOleDbConnection)
        daEMP.Fill(ds, "EMP")
        daDEPT.Fill(ds, "DEPT")
        oOleDbConnection.Close()
        dgDEPT.SetDataBinding(ds, "DEPT") 'to make sure table is obtained
        dgEMP.SetDataBinding(ds, "EMP") 'to make sure table is obtained
        'EXAMPLE OF MANUAL BINDING

        custOrderRel = ds.Relations.Add("CustOrders", _
                      ds.Tables("DEPT").Columns("DEPTNO"), _
                      ds.Tables("EMP").Columns("DEPTNO"))


        ' Overload resolution failed because no accessible 'New' accepts this number of arguments..

        ' Add the relation to the DataSet.
        'ds.Relations.Add(myDataRelation)
        ' GridOrders.SetDataBinding(ds, "Customers")
        ' GridDetails.SetDataBinding(ds, "Customers.CustOrd")
       

End Sub

======

Private Sub dgDEPT_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgDEPT.CurrentCellChanged

        Dim r As DataRow
        Dim intSum As Int32
        Dim mike As String
        TextBox1.BackColor = Color.Yellow

intSum = dgDEPT.CurrentRowIndex 'get Index of selected item in grid
mike = dgDEPT.Item(intSum, 0) 'get the value of the first column of the selected item

txtRowIndex1.Text = mike.ToString 'a check to make sure item is correct selection
ListBox1.Items.Clear()  'clear list box

For Each deptRow In ds.Tables("DEPT").Rows
            If deptRow(0) = mike Then 'use to get only the select item value to use as relationship
                'Console.WriteLine("Dept Table DEPTNO:" & deptRow("DEPTNO").ToString())

For Each empRow In deptRow.GetChildRows(custOrderRel)

ListBox1.Items.Add("ENAME: " + empRow("ENAME") + " MGR: " + empRow(3).ToString() + "   DATE: " + empRow(4))
Next
End If
Next

    End Sub
vandy02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iboutchkineCommented:
You have to create relationship between2 datatables and then show related records

Imports System.Data.OleDb

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
#End Region

    Dim fsConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestDB\TestDB.mdb;"
    Dim ds As DataSet
    Dim da As OleDbDataAdapter
    Dim relEmpToOrders As DataRelation

    Private Sub CreateDS()
        Dim sSQL As String

        ds = New DataSet()

        'get the Employee table to dataset
        sSQL = "SELECT * FROM Employee"
        'Connect to database and specify sSQL
        da = New OleDbDataAdapter(sSQL, fsConn)
        Try
            'using DataAdapter enter records from table to DataSet
            da.Fill(ds, "Employee")
        Catch objExc As System.Exception
            MessageBox.Show(Err.Description)
            Exit Sub
        End Try

        'get the Orders table to dataset
        sSQL = "SELECT * FROM Orders"
        da = New OleDbDataAdapter(sSQL, fsConn)
        Try
            'using DataAdapter enter records from table to DataSet
            da.Fill(ds, "Orders")
        Catch objExc As System.Exception
            MessageBox.Show(Err.Description)
            Exit Sub
        End Try

       'define relationships
        Dim t1 As DataTable
        t1 = ds.Tables("Employee")
        Dim t2 As DataTable
        t2 = ds.Tables("Orders")
        Dim dc1, dc2 As DataColumn
        dc1 = t1.Columns("EmployeeID")
        dc2 = t2.Columns("EmployeeID")

        'You define a relation between tables in the DataSet and let the parent and
        'child grids know about that
        relEmpToOrders = New DataRelation("Emp2Ord", _
        dc1, dc2)
        ds.Relations.Add(relEmpToOrders)
        ' Add an extra column
        t1.Columns.Add("OrdersPerEmp", GetType(Integer), "count(child(Emp2Ord).employeeid)")
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.CreateDS()
        dgp.DataSource = ds.Tables("Employee")
    End Sub

End Class

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.