Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-04
5
Medium Priority
?
513 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:vandy02
  • 3
5 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12501528
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
0
 
LVL 2

Expert Comment

by:ahmadfuwad
ID: 12502948
'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
0
 

Author Comment

by:vandy02
ID: 12503993
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...
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12520748
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
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 750 total points
ID: 12520870
The last three lines are junk ignore them

ie. these lines at very end

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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (http://msdn.microsoft.com/en-us/library/system.string.aspx) 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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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