Microsoft Visual Basic .NET Databases - Lesson 4: Master-Detail Navigation

	Master-Detail Navigation

Introduction

A relationship is referred to as master/detail when it involves at least two tables in which a certain table, the parent or master, holds the main information of the record and a second table, the child, presents some details about the current record of the master. To start, you can first create a normal table, as the parent, that holds categories of information as any data you judge necessary. After creating the parent table, you can create a second table that holds various fields with more detailed information about the various records of the parent. Very important, this new child table must have a foreign key that represents the records of the parent table.

To implement a master/detail scenario, you must filter data of the child table so that when a particular record of the parent table is accessed, only the records of the child table would display. This can easily be done using the DataView control that is equipped with a RowFilter property.

Practical Learning: Using Master-Detail Navigation

In Server Explorer, right-click the Tables node of the CIC1 database and click New Table

Create the following columns:

Column Name	Data Type	Other Properties
AssetTypeID	int	Primary Key: True Identity: Yes
AssetType	varchar	Length: 40 Allow Nulls: cleared

Save the new table as AssetCategories and close it

From the Tables node, double-click AssetCategories and complete it with the following fields:

AssetTypeID	AssetType
1	Business Management
2	Order Processing
3	Store Items Production

Close the table
In Server Explorer, under the Tables node of the CIC1 database, right-click CompanyAssets and click Design Table
Right-click AssetType and click Delete Column
Right-click Make and click Insert Column
Click the Column Name of the new empty field and change its characteristics as follows:
Column Name: AssetTypeID
Data Type: int
Save the table
On the Table toolbar, click the Relationships button
In the Properties Pages, click New
Under the Primary Key Table combo box, make sure that AssetCategories is selected. In the first combo box under it, select AssetTypeID
In the combo box under Foreign Key Table, make sure CompanyAssets is selected. In the combo box under CompanyAssets, select AssetTypeID
Click the Cascade Update Related Fields and the Cascade Delete Related Records check boxes
Click Close
Close the table. When asked to save, click Yes twice

From the Tables node, double-click CompanyAssets and complete it with the following fields:

AssetID	AssetTypeID	Make	Model
1	1	HP	Laser Jet 4200dtn
2	3	Scotcher	S-65G
3	1	IBM	NetVista M42
4	3	Bobel Systems	BBL36
5	2	DDP	AltaPOS 70DS
6	2	DDP	AltaPOS 70DS
7	3	Emery Thompson	2HSC-W
8	1	Olympus	C-50
9	1	Gateway	200XL
10	3	Technogel	BF-50

Close the table
To add a new form, on the main menu, click Project -> Add Windows Form...
Set the Name to CompanyAssets and press Enter
Change its Icon to the above App.ico and change its Text to
Clarksville Ice Cream - Company Assets
Set its StartPosition to CenterScreen

Design the form as follows:

Control	Name	Text	Other Properties
Label		Asset Category ID:
TextBox	txtAssetTypeID		ReadOnly: True TextAlign: Right
Label		Asset Category:
TextBox	txtAssetType
DataGrid	grdAssets		CaptionText: Assets of this Category AutoFormat: Colorful 2
Button	btnClose	Close
Button	btnFirst	\| <
Button	btnPrevious	<
Button	btnNext	>
Button	btnLast	> \|

From Server Explorer, under the Tables node from the CIC1 database, click AssetCategories, press and hold Ctrl. Then click CompanyAssets and release Ctrl
Drag the selected tables and drop the group on the form:
Two SqlDataAdapter (sqlDataAdapter1 and sqlDataAdapter2) and one SqlConnection (sqlConnection1) icons should have been added to your project
On the main menu, click Data -> Generate Dataset...
Accept the existing radio button and click OK
On the form, click the txtAssetTypeID text box to select it. In the Properties window, use the DataBindings property to bind its Text to
DsIceCream1 - AssetCategories. AssetTypeID
On the form, click the txtAssetCategory text box to select it. In the Properties window, use the DataBindings property to bind its Text to
DsIceCream1 - AssetCategories. AssetType

Double-click an empty area of the form to access the form's Load event and implement it as follows:

Private Sub CompanyAssets_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.SqlDataAdapter1.Fill(Me.DsIceCream1)
        Me.SqlDataAdapter2.Fill(Me.DsIceCream1)
End Sub

In the Class Name combo box, select btnClose. In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
End Sub

In the Class Name combo box, select btnFirst. In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position = 0
End Sub

In the Class Name combo box, select btnPrevious. In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position = _
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position - 1
End Sub

In the Class Name combo box, select btnNext. In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position = _
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position + 1
End Sub

In the Class Name combo box, select btnLast. In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLast.Click
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position = _
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Count - 1
End Sub

Display the first form, Form1.vb [Design]
On the Toolbox, click Button and click under the existing Employees button of the form
Change the new button's Text to Company Assets and change its Name to btnCompAssets

Double-click the new Company Assets button and implement its Click event as follows:

Private Sub btnCompAssets_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCompAssets.Click
        Dim frmAssets As CompanyAssets = New CompanyAssets
        frmAssets.ShowDialog()
End Sub

Execute the application and click the different buttons
Close the form(s)
Display the CompanyAssets form (CompAssets.vb [Design])
To display the list of assets for each category in the data grid, from the Data section of the Toolbox, click DataView and click the form
Change the DataView's Name to dvwAssets and set its Table to DsIceCream1.CompanyAssets
Set the DataSource of the DataGrid control to dvwAssets

Double-click an empty area on the form and change the file as follows:

Private Sub CompanyAssets_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.SqlDataAdapter1.Fill(Me.DsIceCream1)
        Me.SqlDataAdapter2.Fill(Me.DsIceCream1)

        Me.dvwAssets.RowFilter = "AssetTypeID = " + Me.txtAssetTypeID.Text
    End Sub

    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
    End Sub

    Private Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position = 0

        Me.dvwAssets.RowFilter = "AssetTypeID = " + Me.txtAssetTypeID.Text
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position = _
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position - 1

        Me.dvwAssets.RowFilter = "AssetTypeID = " + Me.txtAssetTypeID.Text
    End Sub

    Private Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position = _
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position + 1

        Me.dvwAssets.RowFilter = "AssetTypeID = " + Me.txtAssetTypeID.Text
    End Sub

    Private Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLast.Click
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Position = _
        Me.BindingContext(Me.DsIceCream1, "AssetCategories").Count - 1

        Me.dvwAssets.RowFilter = "AssetTypeID = " + Me.txtAssetTypeID.Text
    End Sub
End Class

Execute the application to test it
Close the form


Previous	Copyright © 2004-2010 FunctionX, Inc.	Next