Customizing the Data View Web Part in FrontPage 2003

Article
07/11/2006

John Jansen
Lisa Wollin
Microsoft Corporation

March 2005

Applies to:
Microsoft Office FrontPage 2003
Microsoft Windows SharePoint Services

Summary: Learn how to customize lists in Microsoft Windows SharePoint Services by using the Data View Web Part in Microsoft Office FrontPage 2003. (11 printed pages)

Introduction to Customizing the Data View Web Part
First Things First
Automatically Filtering Data Based on User Input
Returning Filtered and Unfiltered Datasets
Showing the Total Number of Items When a View Contains Multiple Pages
Conclusion
Additional Resources

Introduction to Customizing the Data View Web Part

If you use Microsoft Windows SharePoint Services, you might know that you can customize your Web sites in Microsoft Office FrontPage 2003. However, you might not know that you can use the Data View Web Part to create custom views of data contained in lists and libraries within your Web site or in custom data sources. Inserting the Data View Web Part can be as simple as dragging it to a new or existing page in your Web site and making a few modifications, such as filtering.

The three tips contained in this article explain how to use the Data View Web Part in FrontPage 2003 to create custom views of data, filter data, and provide information that is not available by default in Windows SharePoint Services. These three examples were originally published in the FrontPoint blog. The examples answered frequently asked questions about working with the Data View Web Part in FrontPage.

Explaining every possible task that you can do with the Data View Web Part is beyond the scope of this article. For more information, see the following articles on MSDN.

Note The Data View Web Part is available only when working with a Web site based on Windows SharePoint Services within FrontPage 2003.

First Things First

The examples in this article assume that you have access to a Web server that has the Northwind database. You need to use the Microsoft SQL Server version of the database, either on the same Web server as your SharePoint site or on another Web server.

Unless otherwise specified, the examples in this article use the Customers and Orders tables from the Northwind database. If you do not have access to a Web server with the Northwind database, you can substitute any database to which you have access.

To add a connection to the database connections list

On the Data Source Catalog task pane under Database Connections, click Add to Catalog.
In the Data Source Properties dialog box, click the General tab.
Type the name of the data source. FrontPage allows you to add connections to a single table or to a query that spans multiple tables. The name that you assign should describe the data contained within the returned dataset. For example, if you are adding a connection to the Customers table in the Northwind database, type Customers on Northwind.
Click the Source tab, and then click Configure Database Connection.
On the first page of the Configure Database Connection Wizard, type the name of the server on which the database is located and select the type of authentication required.

Note You can bypass typing the server name and authentication by selecting Use custom connection string, and then pasting a connection string into the Edit Connection String dialog box.
Click Next.
If you selected Use this username and password in the data connection and typed an authorized user name and password, FrontPage displays a message informing you that the user name and password are stored as clear text in the connection string. Click OK.

Important Storing a user name and password as clear text poses some obvious security concerns when connecting to a database in a live Web site. We highly recommend that you use a more-secure method, such as Windows Authentication, when connecting to a database located on an Internet or extranet Web site.
On the second page of the Configure Database Connection Wizard, select the database from the Databases drop-down list; then select a table or query from the list of available tables, views, and stored procedures, or create a custom query string by selecting Use custom query and clicking Edit.
Click Finish.

On the Source tab of the Data Source Properties dialog box, you can also specify which fields to include, set up a filter, and specify whether and how to sort the returned data.

Automatically Filtering Data Based on User Input

Filtering data using the Data View Web Part is relatively easy, but what if you want to filter data based on a value that a user specifies? For example, say you have a list of products and you want visitors to your site to be able to filter according to the category of the product.

FrontPage 2003 includes a data view style, the drop-down list style, that allows users to select a value from a combo box and display data that is filtered based on the value selected. However, when you use the drop-down style, the default behavior requires the user to click a button to execute the query. Many customers wanted the ability to run the resulting query automatically when a visitor made a selection. The following steps explain how to do this.

To execute a query automatically when a visitor makes a selection in a drop-down list

Open your SharePoint site in FrontPage 2003 and create a blank page. Switch to Split view and select the design window.
On the Data menu, click Insert Data View.
From the Data Source Catalog task pane, drag the Customers table in the Northwind database from the list under Database Connections onto the page.

Note If you do not have the Customers table in the Northwind database listed under Database Connections, add a database connection to it or another database table, view, or stored procedure as described in the previous section.
Click Style on the Data View Details task pane.
Scroll through the list of HTML View Styles to select the drop-down list view style.

The view styles are not labeled, but you can find a description of each style under Description. The drop-down list view style may be the fourth from the bottom and appears circled in red in Figure 1.

Figure 1. Locating the drop-down list view style
Click the Options tab and, in the Record sets section, select Display all items.
Click OK.
On the Data menu, click Insert Data View.
From the Data Source Catalog task pane, drag the Orders table in the Northwind database from the list under Database Connections onto the page so that it appears beneath the Web part with the drop-down list box.

Note If you do not have the Orders table in the Northwind database listed under Database Connections, add a database connection to it or another database table, view, or stored procedure as described in the previous section.
From the Data menu, select Web Part Connections. This action starts the Web Part Connection Wizard.
On the first page of the Web Part Connection Wizard, in the drop-down list, select "Filter View Using Data Values From," if not already selected.
Click Next.
On the second page of the Web Part Connection Wizard, select Connect to a Web Part on this page from the list of option buttons, if not already selected.
Click Next.
On the third page of the Web Part Connection Wizard, in the Target Web Part list, select "Customers on Northwind"; in the Target Action list, select "Provide Data Values To".
Click Next.
On the fourth page of the Web Part Connection Wizard, in both list boxes, select the "CustomerID" field.
Click Next.
On the fifth page of the Web Part Connection Wizard, do not make any changes; click Next.
Click Finish.
Switch to Code view and locate the code <a target="_self">. This should be the first <a> tag in the page.

Select and copy the entire block for the first <xsl:attribute> tag within the <a> tag, as shown in the following code sample.

<a target="_self"> 

<!-- NOTE: start copying here --> 
<xsl:attribute xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
name="href"><xsl:variable name="cursel"> 
dvt_curselkey={<xsl:value-of select="$CurrentRowKey"/>}</xsl:variable> 
<xsl:variable xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
name="fields">@ID=<xsl:value-of select="ddwrt:ConnEncode(string(@ID))"/> 
</xsl:variable>javascript:<xsl:value-of select=
"ddwrt:GenFireConnection(concat('99d2bb39-f973-482c-a0ca-e49b56eae72d*',
$fields),string($cursel))"></xsl:value-of></xsl:attribute> 
<!-- NOTE: stop copying here--> 

<xsl:attribute name="style"> 
<xsl:if test="$CurrentRowKey = $dvt_curselkey"> 
font-weight: bold;</xsl:if></xsl:attribute> 
<xsl:value-of select="@Title"/> 
</a>

Locate the code for the <option> tag (it should be directly above the <a> tag), and paste the section of code you copied in the previous step.

If you accidentally make one or more changes that adversely affect the display of the Web Part, FrontPage displays an error message (similar to the one in Figure 2) within the Data View Web Part in Design view. You can either fix the problem defined in the error message or undo the changes by choosing Undo from the Edit menu.

Figure 2. Sample message from FrontPage for error in Web Part

In the code that you just pasted, locate the code name="href" and change it to name="value", as shown in bold in the following code sample. Your code should now look similar to the following code sample. This code change moves the connection from the HREF attribute to the Value attribute, which means the value selected in the drop-down box can now be used for firing the connection.

<option style="display:{$GroupStyle}"> 

<!-- NOTE: paste code here --> 
<xsl:attribute xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
name="value"><xsl:variable name="cursel"> 
dvt_curselkey={<xsl:value-of select="$CurrentRowKey"/>}</xsl:variable> 
<xsl:variable xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
name="fields">@ID=<xsl:value-of select="ddwrt:ConnEncode(string(@ID))"/> 
</xsl:variable>javascript:<xsl:value-of select=
"ddwrt:GenFireConnection(concat('99d2bb39-f973-482c-a0ca-e49b56eae72d*',
$fields),string($cursel))"></xsl:value-of></xsl:attribute> 
<!-- NOTE: paste code here --> 

  <a target="_self"> 
    <xsl:attribute xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
name="href"><xsl:variable name="cursel"> 
dvt_curselkey={<xsl:value-of select="$CurrentRowKey"/>}</xsl:variable> 
<xsl:variable xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
name="fields">@ID=<xsl:value-of select="ddwrt:ConnEncode(string(@ID))"/> 
</xsl:variable>javascript:<xsl:value-of select=
"ddwrt:GenFireConnection(concat('99d2bb39-f973-482c-a0ca-e49b56eae72d*',
$fields),string($cursel))"></xsl:value-of></xsl:attribute> 
    <xsl:attribute name="style"> 
      <xsl:if test="$CurrentRowKey = $dvt_curselkey">font-weight: bold;
      </xsl:if> 
    </xsl:attribute> 
    <xsl:value-of select="@Title"/> 
  </a> 
</option>

Locate the opening <select> tag for the drop-down list, as shown in the following code example.
```
<select name="ID" size="1"> 
```
Add an onchange event handler to the <select> tag, as shown in the following code sample.
```
<select name="ID" size="1" onchange="eval(this.options.value)"> 
```
(Steps 27 through 32 are optional; they set the formatting for the columns in the Data View Web Part.) Select the value in the OrderID column of the second Web Part, the one that displays the orders by customer.
In the Data View Details task pane, select OrderID, as shown in red in Figure 3; right-click the name and click Insert as Text.

Figure 3. Locating the OrderID column in the Data View Details task pane
Select the value in the OrderDate column of the second Web Part.
In the Data View Details task pane, select the OrderDate column; right-click the column and click Insert as Date & Time.
In the Format Date & Time dialog box, clear the Show Time check box, and then click OK.
Format the date in the ShippedDate column of the second Web Part by repeating the previous two steps for the ShippedDate column.
Save the page as sample.aspx and preview it in the browser. Experiment with filtering the dataset by selecting items from the drop-down list. Figure 4 shows how the page should appear after you complete all the steps.

Figure 4. Completed Data View Web Part using the FrontPage drop-down list style

Returning Filtered and Unfiltered Datasets

Now that you know how to provide filtered datasets, this tip shows how to provide both filtered and unfiltered datasets. For example, perhaps you want to allow your users to display filtered data or display all records contained in a table, view, or stored procedure.

In the previous example, you created a page that filters customer orders by the value selected in a drop-down list. In this example, you change the sample.aspx page to show all orders or filter by EmployeeID. The Data View Web Part does not allow you to modify the values contained in a drop-down list view, so you need to delete the drop-down list that you created previously and insert a Form Web Part.

To modify the previous example so that it can display both unfiltered and filtered results

Open the sample.aspx page that you created in the previous section if it is not already opened.
Save the page as sample2.aspx so that you do not overwrite the changes that you made in the previous section.
Select the Data View Web Part that displays the drop-down list and press DELETE. This action deletes both the drop-down list and the Web part connection that you previously created.
Display the Web Parts task pane or, on the Data menu, click Insert Web Part.

You can switch task panes by clicking the down arrow in the task pane title bar and selecting the new task pane from the list, as shown in Figure 5.

Figure 5. Quickly switching task panes in FrontPage
Locate and select the Form Web Part from the Web Part List.
Click Insert Selected Web Part.
By default, the Form Web Part inserts a text box and button into the form. Select and delete the text box.
On the Insert menu, point to Form and click Drop-Down Box. This action inserts a drop-down box form control into the Form Web Part.
Double-click the drop-down list box to display the Drop-Down Box Properties dialog box.
Type EmployeeID in the Name text box.
Click Add, type All in the Choice text box, and click Selected under the Initial State section.
Repeat the previous step to add choices for employee numbers 1 through 9.
Click OK.
Position the insertion point in any row below the heading row in the Data View Web Part that displays the customer orders.
On the Table menu, point to Select and click Row. Figure 6 shows that the second row in the Data View Web Part table, the first row after the heading row, is selected.

Figure 6. Selecting a data row in the Data View Web Part
On the Data menu, click Conditional Formatting.
In the Conditional Formatting task pane, click Create, and then click Show Content.
In the Condition Criteria dialog box, click Click here to add a new clause and select the following values into the following fields.
- Field Name = EmployeeID
- Comparison = Equals
- Value = [Input Parameter]
Note If you do not see these values, and the database connection for this Web Part accesses the Orders table in the Northwind database, you may have selected the heading row. Click Cancel and select any row after the first row in the Data View Web Part that displays the customer orders. Then try this step again.
Click OK.
Select the Form Web Part that displays the drop-down list and click Web Part Connections on the Data menu.
On the first page of the Web Part Connection Wizard, in the drop-down list, select "Provide Form Values To," if not already selected.
Click Next.
On the second page of the Web Part Connection Wizard, select Connect to a Web Part on this page, if not already selected.
Click Next.
On the third page of the Web Part Connection Wizard, in the Target Web Part list, select "Orders on Northwind"; in the Target Action list, select "Modify View using Parameters from."
Click Next.
On the fourth page of the Web Part Connection Wizard, locate the [Input Parameter] item in the Inputs to Orders on Northwind column; in the Columns in Employee ID column, select "EmployeeID."
Click Next, and then click Finish.
Select the code window and locate the following code. (The reason that $filterParam appears here is because [Input Parameter] is named $filterParam in the FrontPage XSLT.)
```
<xsl:if test="@EmployeeID = $filterParam "> 
```
Add the code or $filterParam = 'All' so that the Data View Web Part tests for the 'All' filter parameter text. Your code should now resemble the following code.
```
<xsl:if test="@EmployeeID = $filterParam or $filterParam = 'All'"> 
```
Turn off paging for the Data View Web Part. To do so, select the Data View Web Part. On the Data menu, click Style. In the View Styles dialog box, click the Options tab; in the Record Sets group, select Display all items. Click OK.
Save the page as sample2.aspx, if you haven't already, and preview it in the browser.

Showing the Total Number of Items When a View Contains Multiple Pages

By default, when you add a Data View Web Part to a page, the Data View Web Part automatically paginates every 10 items when more than 25 items are returned, as shown in Figure 4 earlier in this article.

But what if you want to display the total number of items so that your user knows the size of the return set? To show the total number of items, you simply need to add a small piece of code to the Data View Web Part. The following steps show you how.

Note You cannot provide a total count for a Data View Web Part that is based on SQL data because the pagination for SQL data is done at the data source layer.

To display the total number of items returned in the Data View Web Part

Open your SharePoint site in FrontPage 2003 and create a blank page.
Switch to Code view and select all of the code (CTRL+A).
Open Microsoft Internet Explorer and go to Sample XML File (books.xml).
Copy the XML code beginning at the opening line <?xml version="1.0"?> and ending with the closing </catalog> tag.
In FrontPage, paste the XML code, replacing the existing code.
Save the page as books.xml and close the file.
Create another blank page.
Switch to Split view and select the design window.
Select the books.xml file from the Folder List and drag it to the design window of the page you just created. FrontPage automatically creates a Data View Web Part in which to display the XML data.

Note If you do not see the Folder List, on the View menu, click Folder List.
Select the Web Part that you just created, and on the Data menu, click Style.
In the View Styles dialog box, click the Options tab.
On the Options tab, click Display items in sets of this size under Record sets.
Click OK.
Switch to Code view and find the following code:
```
<xsl:value-of select="$LastRowValue"/> 
```
Add the following code immediately after the code in the previous step. (Note that a space precedes "of.")
```
 of <xsl:value-of select="$RowCount"/> 
```
Save the page as sample3.aspx and preview it in the browser.

Conclusion

As you certainly have seen from the tips in this article, the Data View Web Part is both easy to work with and very powerful. This article described just a few of the tasks that you can accomplish with the Data View Web Part for displaying data on pages in Web sites based on Windows SharePoint Services. For more tips, see the FrontPoint blog and the articles listed at the beginning of the article and in the following section.

Additional Resources

See the following resources for more information about the Data View Web Part.