Walkthrough: Creating Master/Detail Web Pages in Visual Studio
Many Web pages display data in more than one way, often displaying data from related tables. This walkthrough shows you various ways to work with data in multiple controls and from multiple tables, including those that have a master/detail relationship. Tasks illustrated in this walkthrough include:
Populating a drop-down list with database data.
Creating filters — SQL statements with a WHERE clause (parameterized queries).
Displaying filtered data based on the user's selection in a drop-down list.
Displaying details about a selected record using the DetailsView control.
Selecting a record in one page and displaying a related record in a second page.
Prerequisites
In order to complete this walkthrough, you will need:
Access to the SQL Server Northwind database. For information about downloading and installing the SQL Server sample Northwind database, see Installing Sample Databases on the Microsoft SQL Server Web site.
Note
If you need information about how to log on to the computer running SQL Server, contact the server administrator.
Microsoft Data Access Components (MDAC) version 2.7 or later.
If you are using Microsoft Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you are using Microsoft Windows 2000, you might to need to upgrade the MDAC already installed on your computer. For more information, see the article " Microsoft Data Access Components (MDAC) Installation" in the MSDN Library.
Creating the Web Site
If you have already created a Web site in Microsoft Visual Web Developer (for example, by following the steps in Walkthrough: Creating a Basic Web Page in Visual Web Developer or Walkthrough: Basic Data Access in Web Pages), you can use that Web site and skip to the next section, "Connecting to SQL Server." Otherwise, create a new Web site and page by following these steps.
To create a file system Web site
Open Visual Web Developer.
On the File menu, click NewWeb Site.
The New Web Site dialog box appears.
Under Visual Studio installed templates, click ASP.NET Web Site.
In the Location box, enter the name of the folder where you want to keep the pages of your Web site.
For example, type the folder name C:\WebSites.
In the Language list, click the programming language you prefer to work in.
Click OK.
Visual Web Developer creates the folder and a new page named Default.aspx.
Connecting to SQL Server
To work with data, you need to establish a connection to a database. In this walkthrough, you will create a connection independently of working with Web pages or controls.
To create a connection to SQL Server
Press CTRL+ALT+S to display the Database Explorer. In Database Explorer, right-click Data Connections and choose Add Connection.
The Change Data Source dialog box is displayed.
In the Change Data Source dialog box, select Microsoft SQL Server. and click OK.
In the Add Connection dialog box, do the following:
Enter or select the name of your SQL Server computer. For a server on your computer, enter (local).
Select Use SQL Server Authentication.
Enter a user name and password.
Select Save my password.
Select Northwind as the database.
Click Test Connection, and when you are sure that it works, click OK.
The connection is added to Server Explorer.
Using a Drop-Down List as the Master
In this part of the walkthrough, you will add a drop-down list to a page and populate it with a list of categories from the Northwind table. When users select a category, the page will display the products for that category.
To create and populate a drop-down list
Switch to or open the Default.aspx page. If you are working with a Web site you had already created, add or open a page that you can work with in this walkthrough.
Switch to Design view.
Type Select Products By Category in the page and format the text as a heading using the Block Format drop-down list above the Toolbox.
From the Standard group in the Toolbox, drag a DropDownList control onto the page.
In the DropDownList Tasks, select the Enable AutoPostBack check box.
This configures the control so that it causes the page to post back to the server whenever a selection is made from the list, rather than waiting until the user clicks a button.
In the DropDownList Tasks, select Choose Data Source.
The Choose Data Source wizard starts.
In the Select a data source list, click <New Data Source>.
The Data Source Configuration Wizard dialog box is displayed.
Select Database.
This specifies that you want to get data from a database that supports SQL statements. (This includes SQL Server and other OLE-DB-compatible databases.)
In the Specify an ID for the data source box, a default data source control name is displayed (SqlDataSource1). You can leave this name.
Click OK.
The wizard displays the Configure Data Source - SqlDataSource1 page in which you can select a data connection.
From the drop-down list, select the Northwind connection you created earlier in the walkthrough.
Click Next.
The wizard displays a page in which you can choose to store the connection string in the configuration file.
Be sure the Yes, save this connection as check box is selected, and then click Next. (You can leave the default connection string name.)
The wizard displays a page in which you can specify what data you want to retrieve from the database.
From the Name list under Table or View Options, select Categories.
In the Columns box, select CategoryID and CategoryName.
Click Next.
Click Test Query to be sure that you are getting the data you want.
Click Finish.
The Data Source Configuration Wizard is displayed, with the name of the data source control you have configured displayed.
From the Select a data field to display in the DropDownList list, select CategoryName.
Note
If no items appear in the list, click the Refresh Schema link.
This specifies that the value of the CategoryName field will be displayed as the text of the item in the drop-down list.
From the Select a data field for the value of the DropDownList list, select CategoryID.
This specifies that when an item is selected, the CategoryID field will be returned as the value of the item.
Click OK.
Before proceeding, test the drop-down list.
To test the drop-down list
Press CTRL+F5 to run the page.
When the page is displayed, examine the drop-down list.
Select a category to be sure that the list performs a postback.
You can now display products for the category selected in the drop-down list.
Using a Grid to Display Detail Information
You can now extend the page to include a grid. When users make a selection from the categories drop-down list, the grid will display the products for that category.
To use a grid to display detail information
Switch to or open the Default.aspx page (or the page you have been working with), and switch to Design view.
From the Data group of the Toolbox, drag a GridView control onto the page.
From the GridView Tasks menu, select <New Data Source> from the Choose Data Source list.
The Data Source Configuration Wizard opens.
Select Database.
In the Specify an ID for the data source box, a default data source control name is displayed (SqlDataSource2). You can leave this name.
Click OK.
The wizard displays a page where you can select a connection.
From the connections drop-down list, select the connection you created and stored earlier in the walkthrough (NorthwindConnectionString).
Click Next.
The wizard displays the Configure Data Source - SqlDataSource2 page in which you can create a SQL statement.
From the Name list, select Products.
In the Columns box, select ProductID, ProductName, and CategoryID.
Click WHERE.
The Add WHERE Clause dialog box is displayed.
From the Column list, select CategoryID.
From the Operator list, select =.
From the Source list, select Control.
Under Parameter Properties, in the Control ID list, select DropDownList1.
The last two steps specify that the query will get the search value for the category ID from the DropDownList control you added earlier.
Click Add.
Click OK to close the Add WHERE Clause dialog box.
Click Next.
In the Preview page, click Test Query.
The wizard displays a dialog box that prompts you for a value to use in the WHERE clause.
Type 4 in the box and click OK.
The products records for category 4 are displayed.
Click Finish to close the wizard.
You can now test the master/detail display.
To test the page
Press CTRL+F5 to run the page.
When the page appears, products from the first item in the drop-down list are displayed.
Select a category from the list and confirm that the corresponding products appear in the grid.
Displaying Master/Detail Data on the Same Page
In this part of the walkthrough, you will display data from related tables in one page. The master table data is displayed in a grid where users can select individual rows. When they do, one or more detail records are displayed in a scrollable control elsewhere on the page. For illustration purposes, you will use the Northwind Categories table as the master table and the Products table as the details table.
To display the master records
Add a new page to the Web site and name it MasterDetails2.aspx.
Switch to Design view.
Type Master/Detail Page in the page and format the text as a heading.
From the Data group of the Toolbox, drag a GridView control onto the page.
In the GridView Tasks menu, in the Choose Data Source list, click <New Data Source> and then use these steps to configure a data source for the GridView control:
Select Database.
Click OK.
From the connection drop-down list, select the connection you created and stored earlier in the walkthrough (NorthwindConnectionString).
Click Next.
From the Name list, select Categories.
In the Columns box, select CategoryID and CategoryName.
Click Next and then click Finish.
Select the GridView control, and in the GridView Tasks menu, choose Edit Columns.
The Fields dialog box is displayed.
Under Available Fields, open the Command Field node, choose Select, and then click Add to add it to the Selected Fields list.
In the Selected Fields list, choose Select, and then in the CommandField property grid, set its SelectText property to Details.
Click OK to close the Fields dialog box.
A new column with a Details hyperlink is added to the grid.
Select the GridView control and in the Properties window, be sure its DataKeyNames property is set to CategoryID.
This specifies that when you select a row in the grid, ASP.NET can find the key of the currently displayed Categories record in a known location.
The grid allows you to select an individual category. The next step is to add the DetailsView control that will display the detail records — the products associated with the selected category. The DetailsView control will use a different SQL query to get its data, so it requires a second data source control.
To configure a query to display related records
Press ENTER to make space underneath the SqlDataSource1 control on the MasterDetails2.aspx page in Details view.
From the Data group of the Toolbox, drag a DetailsView control onto the page.
Configure it to use a second data source control using the following steps:
From the Choose Data Source list, select <New Data Source>.
Select Database.
Click OK.
In the connection drop-down list, click the connection you created and stored earlier in the walkthrough.
Click Next.
From the Name list under Table or View Options, select Products.
In the Columns box, select ProductID, ProductName, and CategoryID.
Click WHERE.
The Add WHERE Clause dialog box is displayed.
From the Column list, select CategoryID.
From the Operator list, select =.
From the Source list, select Control.
Under Parameter Properties, in the Control ID list, select GridView1. The query for the second grid will get its parameter value from the selection in the first grid.
Click Add and then click OK to close the Add WHERE Clause dialog box.
Click Next.
In the Preview page, click Test Query.
The wizard displays a dialog box that prompts you for a value to use in the WHERE clause.
Type 4 in the box and click OK.
The products records for category 4 are displayed.
Click Finish.
In the DetailsView Tasks menu, check Enable Paging.
This will allow you to scroll through individual product records.
Optionally, in the Properties window, open the PagerSettings node and select a different Mode value.
By default, you page through records by clicking a page number, but you can select options to use next and previous links.
You can now test the combination of the master grid and the details view.
To test the page
Press CTRL+F5 to run the page.
In the grid, select a category.
The DetailsView control displays a product associated with that category.
Use the pager links in the DetailsView control to navigate to other products for the same category.
In the grid, select a different category.
Review the products for that category in the DetailsView control.
Displaying Master/Detail Data on Separate Pages
For the final part of the walkthrough, you will create another variation — displaying master and detail records on separate pages. The master records are again displayed in a grid that contains a hyperlink for each record. When users click the hyperlink, they navigate to a second page where they can view detail records in a DetailsView control that displays the complete Products record.
To display the master records
Add a new page to the Web site and name it MasterCustomers.aspx.
Switch to Design view.
Type Customers in the page and format the text as a heading.
From the Data folder of the Toolbox, drag a GridView control onto the page.
From the GridView Tasks menu on the control, choose <New Data Source> from the Choose Data Source list and then use the wizard to do the following:
Click Database.
Connect to the Northwind database (connection NorthwindConnectionString).
Retrieve the CustomerID, CompanyName, and City columns from the Customers table.
Optionally, on the GridView Tasks menu, select the Enable paging check box.
In the GridView Tasks menu, choose Edit Columns.
The Fields dialog box is displayed.
Clear the Auto-Generate Fields check box.
Under Available Fields, choose HyperLink Field, click Add, and then set the following properties:
Property
Value
Text
Details
DatahrefFields
CustomerID
This indicates that the hyperlink should get its value from the CustomerID column
DatahrefFormatString
DetailsOrders.aspx?custid={0}
This creates a link that is hard-coded to navigate to the DetailsOrders.aspx page. The link also passes a query string variable named custid whose value will be filled using the column referenced in the DatahrefFields property.
Click OK to close the Fields dialog box.
You can now create the details page that accepts a value from the master page.
To create the details page
Add a new page to the Web site and name it DetailsOrders.aspx.
Switch to Design view.
Type Orders in the page and format the text as a heading.
From the Data folder of the Toolbox, drag a GridView control onto the page.
In the GridView Tasks menu on the control, select <New Data Source> from the Choose Data Source list
In the Select a data source type list, click Database and then click OK.
From the connection list, select the connection you created and stored earlier in the walkthrough (NorthwindConnectionString).
Click Next.
The wizard displays a page where you can create a SQL statement.
From the Name list, select Orders.
In the Columns box, select OrderID, CustomerID, and OrderDate.
Click WHERE.
From the Columns list, select CustomerID.
From the Operators list, select =.
From the Source list, select QueryString.
This specifies that the query will select records based on the value passed into the page with the query string.
Under Parameter Properties, in the QueryString field box, type custid.
The query will get the customer ID value from the query string, which is created when you click a Details link in the MasterCustomers.aspx page.
Click Add.
Click OK to close the Add WHERE Clause dialog box.
Click Next, and then click Finish to close the wizard.
From the Standard node of the Toolbox, drag a Hyperlink control onto the page. Set its Text property to Return to Customers and its href property to MasterCustomers.aspx.
You can now test the related master-detail pages.
To test the pages
Switch to the MasterCustomers.aspx page.
Press CTRL+F5 to run the page.
Click the Details link for a customer.
The browser displays the DetailsOrders.aspx page with the order for the selected customer. Note that the URL in the browser's Address box is:
DetailsOrder.aspx?custid=xwhere x is the ID of the customer you selected.
Click the Return to Customers link, select a different customer, and click the Details link again to test that you can view orders for any customer.
Next Steps
This walkthrough has illustrated various ways to display data from related tables. All the scenarios have the following in common: they rely on a parameterized query, and the parameter values can be passed to the query automatically at run time. You might want to experiment with additional ways to use related data. For example, you might want to:
Experiment with parameters that get their values from other sources, such as a Session variable or a cookie. For more information, see Using Parameters with Data Source Controls.
Expand what you can do with the DetailsView control from the second part of the walkthrough. For example, you can configure the control to allow users to edit detail records. For details, see Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control.