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

Two Datagridviews with Master-Detail relationship


I need to create a Visual Basic windows form with two datagridviews that shows data for the following tables:

Table: Employee_Table
PrimaryKey:  Employee_ID (GUID)
Fields: Last_Name, First_Name, Address

Table: Dependents_Table
Primary Key: Dependent_ID (GUID)
Fields: Employee_ID (FK), Dependent_Last_Name, Dependent_First_Name, Relationship

The first datagridview should show the contents of the Employee_Table. When I click on a row on that datagridview, I want to related Dependents_Table record to show on the other datagridview.  

How do I do this? I am using Visual Basic 2005. And SQL Server 2005.

2 Solutions
Hi crickpaolo,

You can do it using the Microsoft example http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridview.datasource(VS.80).aspx

This will fill the first datagridview. Then after cellclick you repeat this procedure only changing the SQL statement and clear the datagridview before (datagridview.datasource = nothing).

Private WithEvents rsMaster As ADODB.Recordset
Private rsChild As ADODB.Recordset

Private Sub Form_Load()

   Set rsMaster = New ADODB.Recordset
   Set rsChild = New ADODB.Recordset

   rsMaster.Open "SELECT Employee_ID, Last_Name, First_Name, Address " & _
                           "FROM Employee_Table", YOUR_CONNECTION_OBJECT, adOpenKeyset

   Set DataGrid1.DataSource = rsMaster

End Sub

Private Sub rsMaster_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
 On Error Resume Next
 ' Display dependent info for selected employee.
 With pRecordset
   Call showDependentInfo(.Fields("Employee_ID"))
 End With
End Sub

Private Sub showDependentInfo(Byval nEmployeeID As Long)

   Set DataGrid2.DataSource = Nothing

   rsChild.Open "SELECT Dependent_Last_Name, Dependent_First_Name, Relationship " & _
                        "FROM Dependents_Table " & _
                        "WHERE Employee_ID = " & nEmployeeID,  _
                        YOUR_CONNECTION_OBJECT, adOpenKeyset

   Set DataGrid2.DataSource = rsChild

End Sub
crickpaoloAuthor Commented:
Thanks guys. Your comments pointed  me on the right direction.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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