asked on

Using to datagrids with two tables and a relation between the two tables..Click on Dept row...and get children in EMP?

I have a dataset with two tables..."DEPT", "EMP"
They have column "DEPTNO" in common...
I have made a relation and tied the dataset to a datagrid "dgDEPT" and the relationship works.

What I want to do is have a grid as the DEPT table and when I select on a record...I want a separate grid to show the child records...any idea?

At present dgDEPT shows the DEPT records and has "+" so I can look at the child records
=====================================================
Dim strEMP, strDEPT As String
strEMP = "Select * from EMP"
strDEPT = "Select * from DEPT"

Dim dsEMP = New DataSet

Dim daEMP = New OleDb.OleDbDataAdapter(strEMP, conn)
Dim daDEPT = New OleDb.OleDbDataAdapter(strDEPT, conn)

daEMP.Fill(dsEMP, "EMP")
daDEPT.Fill(dsEMP, "DEPT")
conn.Close()

'EXAMPLE OF MANUAL BINDING

dsEMP.Relations.Add("MINE", dsEMP.Tables("DEPT").Columns("DEPTNO"), dsEMP.Tables("EMP").Columns("DEPTNO"))

dgDEPT.SetDataBinding(dsEMP, "DEPT")

==============================================

I got this from a website....and i treid MINE, but it still did not work...

' Visual Basic
Dim myDataRelation As DataRelation
myDataRelation = New DataRelation _
("CustOrd", ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
' Add the relation to the DataSet.
ds.Relations.Add(myDataRelation)
GridOrders.SetDataBinding(ds, "Customers")
GridDetails.SetDataBinding(ds, "Customers.CustOrd")

=====MINE
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 --good
dgEMP.SetDataBinding(ds, "EMP") 'to make sure table is obtained -good

'EXAMPLE OF MANUAL BINDING

Dim myDataRelation As DataRelation

myDataRelation = New DataRelation("DEPTLIST", ds.Tables("DEPT").Columns("DEPTNO"), _
ds.Tables("EMP").Columns("DEPTNO"))
===
THEN I TRIED THIS...

Find A Forum Search Posts (Keyword) Thread Number Threads by Handle Search FAQs (Keyword) Search Links (Keyword) Find An Expert Advanced Search

Home > Forums > Programmers > Languages > Visual Basic(Microsoft) -VB.NET Forum
DATASET and CHildrecords
thread796-944749
Forum Search FAQs Links

Read
New Posts Reply To
This Thread
E-mail It
Print It Next
Thread

Vandy02 (Programmer) Nov 2, 2004
I have a dataset with two tables..."DEPT", "EMP"
They have column "DEPTNO" in common...
I have made a relation and tied the dataset to a datagrid "dgDEPT" and the relationship works.

What I want to do is have a grid as the DEPT table and when I select on a record...I want a separate grid to show the child records...any idea?

At present dgDEPT shows the DEPT records and has "+" so I can look at the child records
=====================================================
Dim strEMP, strDEPT As String
strEMP = "Select * from EMP"
strDEPT = "Select * from DEPT"

Dim dsEMP = New DataSet

Dim daEMP = New OleDb.OleDbDataAdapter(strEMP, conn)
Dim daDEPT = New OleDb.OleDbDataAdapter(strDEPT, conn)

daEMP.Fill(dsEMP, "EMP")
daDEPT.Fill(dsEMP, "DEPT")
conn.Close()

'EXAMPLE OF MANUAL BINDING

dsEMP.Relations.Add("MINE", dsEMP.Tables("DEPT").Columns("DEPTNO"), dsEMP.Tables("EMP").Columns("DEPTNO"))

dgDEPT.SetDataBinding(dsEMP, "DEPT")

==============================================

thanks

Inappropriate post?
If so, Red Flag it!

Check out the FAQ
area for this forum!

Start Your Own Tek-Tips Group! Click Here!
Ragnarox (Programmer) Nov 2, 2004
Vandy02,

In order to get the second datagrid to show the child records for the main grid selection you may want to try this. Instead of getting 2 tables, make 1 table as a join of the two. The set each datagrids table style and column style to show only the information that you want. Then set each grids datasource to that table. This should allow a selection in 1 to bring up the corresponding row in the other.

Hope this helps,

Brian

Thank Ragnarox
for this valuable post!

Inappropriate post?
If so, Red Flag it!

Check out the FAQ
area for this forum!

RiverGuy (MIS) Nov 2, 2004
Ragnarox,
That solution does not work, because if you do a join in SQL, you will get duplicate records that he wants to show in his parent table.

Any how, there is an article to do exactly what Vandy02 needs to do.

http://msdn.microsoft.com/library/default.asp?url=...

Thank RiverGuy
for this valuable post!

Inappropriate post?
If so, Red Flag it!

Check out the FAQ
area for this forum!

Vandy02 (Programmer) Nov 3, 2004
This is what I obtained from the link..
However, when I use mine I get an error...

"Overload resolution failed because no accessible 'NEW'
can be called without narrowing conversion"

Can someone try this...

' Visual Basic
Dim myDataRelation As DataRelation
myDataRelation = New DataRelation _
("CustOrd", ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
' Add the relation to the DataSet.
ds.Relations.Add(myDataRelation)
GridOrders.SetDataBinding(ds, "Customers")
GridDetails.SetDataBinding(ds, "Customers.CustOrd")

=====MINE
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 --good
dgEMP.SetDataBinding(ds, "EMP") 'to make sure table is obtained -good

'EXAMPLE OF MANUAL BINDING

Dim myDataRelation As DataRelation

myDataRelation = New DataRelation("DEPTLIST", ds.Tables("DEPT").Columns("DEPTNO"), _
ds.Tables("EMP").Columns("DEPTNO"))

Inappropriate post?
If so, Red Flag it!

Check out the FAQ
area for this forum!

RiverGuy (MIS) Nov 3, 2004
I'm not sure about that one. You might try referencing the Tables and Columns by index rather than string name.

Thank RiverGuy
for this valuable post!

Inappropriate post?
If so, Red Flag it!

Check out the FAQ
area for this forum!

Vandy02 (Programmer) Nov 4, 2004
well, i tried this and at least i get to the form this time...but i get another error...i changed the format so I could get to the form...any idea with the error?

myDataRelation = New DataRelation("DEPTLIST", ds.Tables(0), ds.Tables(0).Columns("DEPTNO"), ds.Tables(1), ds.Tables(1).Columns("DEPTNO"), True)

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from type 'DataTable' to type 'String' is not valid.

Note sure what to do...thanks for taking a look at this...

thanks

arif_eqbal

So you don't want to show the Parent And child in the same grid with a + sign, instead you want it in two separate grids (that's what I prefer personally)

So you'll need two grids and on the CellChanged of the first grid you'll need to filter and display records in the childgrid
Here's a sample

At form level have a variable LastRow
Dim LastRow as Integer=0

I assume that you have aleady loaded the first Grid (DeptGrid) then on CurrentCellChanged Event

Private Sub DeptGrid_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DeptGrid.CurrentCellChanged

If LastRow <> DeptGrid.CurrentRowIndex Then
LastRow = DeptGrid.CurrentRowIndex
DeptGrid_RowChanged() 'If row has changed call this function
End If

Private Sub DeptGrid_RowChanged()

'When Selection Changes in the first dataGrid change the contents in the second dataGrid
DTEmp = New DataTable
DTEmp to be declared at fiorm level
Dim DeptNo as Integer
DeptNo = DTDept.Rows(DeptGrid.CurrentRowIndex()).Item("DEPTNO")
Dim DA As OleDbDataAdapter = New OleDbDataAdapter("Select * From Emp Where DEPTNO ='" & DeptNo & "'", YourConStr)
DA.Fill(DTEmp)
EmpDataGrid.DataSource = DTEmp
End Sub

'here I am re-querying from the database for every row selection change
'You can improve the performance by getting all the Emp data once and using dataViews to populate the grid, it will avoid the round trip to the database

ahmadfuwad

'try following code it will surely help you
'Always take a new Command and DataAdapter objects for filling dataset other wise it will raise
'error

Public cn As New SqlConnection
Public cmd1 As New SqlCommand
Public cmd2 As New SqlCommand
Public oda1 As New SqlDataAdapter
Public oda2 As New SqlDataAdapter
Public ods As New DataSet

cmd1.Connection = cn
cmd1.CommandType = CommandType.Text
cmd1.CommandText = "select * from tblCustomer"
oda1.SelectCommand = cmd1
oda1.Fill(ods, "Customer")

cmd2.Connection = cn
cmd2.CommandType = CommandType.Text
cmd2.CommandText = "select * from tblOrder"
oda2.SelectCommand = cmd2
oda2.Fill(ods, "order")

ods.Relations.Add("Cstomers", ods.Tables("Customer").Columns(2), ods.Tables("order").Columns(1))

UltraGrid1.DataSource = ods

vandy02

ASKER

both comments i think are where i want to go ..at least as a mix...

the first...is what I want to do except I do not want to go back to the database again for the children...
i actually already have them as datasets like the second part...
is there a way to use the first comment where the second datagird can be populated with the already local dataset with a where clause? or something...

the second...gives all the data with the "+" in one grid...i need to have the parent in one and child in the other..much like the first comment suggests...

any help would be greatly appreciated...

arif_eqbal

Yes you can avoid a round trip to the database that's preferred in fact
Use a dataView to bind the Datagrid and the dataView will have FILTERED Records

eg.
Now declare the DataTable at Form Level and on Form Load Fill it with all data from dataBase

DTEmp = New DataTable
Dim DA As OleDbDataAdapter = New OleDbDataAdapter("Select * From Emp", YourConStr)
DA.Fill(DTEmp)

Then Change the function DeptGrid_RowChanged to :

Private Sub DeptGrid_RowChanged()

Dim DeptNo as Integer
DeptNo = DTDept.Rows(DeptGrid.CurrentRowIndex()).Item("DEPTNO")
'Create a New dataView and filter it to cntain only selected Rows
Dim DV As DataView = New DataView(DTDetails)
DV.RowFilter = "DEPTNO ='" & DeptNo & "'"
EmpDataGrid.DataSource = DV

End Sub

Dim DV As DataView = New DataView(DTDetails)
DV.RowFilter = "PID='" & Me.CmbPID.Text & "'"
Me.DetailsDataGrid.DataSource = DV

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