asked on

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

iboutchkine

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

ASKER CERTIFIED SOLUTION

arif_eqbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial