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

Posted on 2004-11-06
Last Modified: 2006-11-17
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)
        If ConnectionState.Open Then
            TextBox1.Text = "Connection is made to Main"
            TextBox1.BackColor = Color.Green
            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")
        dgDEPT.SetDataBinding(ds, "DEPT") 'to make sure table is obtained
        dgEMP.SetDataBinding(ds, "EMP") 'to make sure table is obtained

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

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

        ' Add the relation to the DataSet.
        ' 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))
End If

    End Sub
Question by:vandy02
    LVL 28

    Expert Comment

    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)
                'using DataAdapter enter records from table to DataSet
                da.Fill(ds, "Employee")
            Catch objExc As System.Exception
                Exit Sub
            End Try

            'get the Orders table to dataset
            sSQL = "SELECT * FROM Orders"
            da = New OleDbDataAdapter(sSQL, fsConn)
                'using DataAdapter enter records from table to DataSet
                da.Fill(ds, "Orders")
            Catch objExc As System.Exception
                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)
            ' 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
            dgp.DataSource = ds.Tables("Employee")
        End Sub

    End Class

    LVL 19

    Accepted Solution


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now