• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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)
        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
1 Solution
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


Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now