Special Edition Using
Microsoft Visual InterDev

Chapter 15
Data-Aware Pages with Design-Time Controls

In this chapter, you will learn:

If you have ever needed to display the contents of a database in an application or Web page, you know how much work and code can be involved. ActiveX design-time controls are designed to take care of the implementation details, freeing you to concentrate on your design objectives. Design-time controls are embedded onto Web pages (.HTML or .ASP), and create much of the code for you. Because they are ActiveX, they handle their own drawing, and accept mouse and keyboard input. The developer interacts with them through the context menus and property pages provided by the control. Unlike run-time controls, such as a data-bound grid control or a calendar control, design-time controls are available only at design time, and only in the development environment.

Inserting a Design-Time ActiveX Control

To use design-time controls, they must first be installed on the computer that is being used to run them. Visual InterDev comes bundled with several design-time controls, and automatically installs them for you during setup. These controls include the following:

NOTE: As with run-time controls, you can create your own design-time controls or use ActiveX controls developed by third-party vendors. See Chapter 14, "Visual Basic 5.0, Control Creation Edition," for more information about building and installing ActiveX controls.

The Data Command Control

The Data Command design-time ActiveX control allows you to create a connection to an external data source and create the command to browse or modify data from that source. As a prerequisite to using the Data Command Control (and the Data Range Control), you must have established a project data connection. For more information, see the sidebar "Creating a Project Data Connection."

To insert a Data Command Control, take the following steps:

  1. Open the .ASP file in which you wish to create the control.

  2. Place your cursor at the location where you wish to insert the control, and right-click. Select Insert ActiveX Control... from the menu. (You can also select Insert, Into HTML, ActiveX Control... from the main menu.)

  3. You will see a tabbed dialog with both standard and design-time ActiveX controls. The dialog will look like Figure 15.1. Click the Design-time tab.

    Figure15.1.
    Inserting a design-time ActiveX control on your Web page.

  4. Select the Data Command Control, and click OK.

  5. The Data Command Control and its Property Page will appear. (See Figure 15.2.) In the drop-down list labeled Data Connection, select the data connection you wish to use.

    WARNING:
    If you have not created a Project Data Connection, this list will be empty.


    Figure 15.2.
    The Data Command Object and its Properties sheet.
  6. To build your SQL Query, click the SQL Builder button.

  7. When the Query Designer appears, expand the Tables folder in the Project Workspace.

  8. Drag the Customers table from the data view, and drop it into the Design Pane of the Query Designer.

  9. Select *All Columns by checking it in the Customers table window. The SQL query will automatically appear in the SQL Pane.

  10. Test the query by clicking the SQL Run icon (shown in Figure 15.4) on the Database toolbar. The results will be displayed in the Results Pane. Your screen should look like Figure 15.3.

    Figure 15.3.
    The Query Designer after running the SQL query entered in Step 9.

    Figure 15.4.
    The Query Run Icon.

  11. When you are satisfied with your query, close the Query Designer by selecting File, Close.

  12. Choose Yes when prompted to update DataCommand1.

  13. Close the Data Command Control by selecting File, Close, or by clicking the Close Window button in the top right corner of the data control window.

The control will insert the code shown in Listing 15.1 into your .ASP file.

Listilng 15.115LIST01.TXT: Script inserted by the Data Command control

*** End Listing ***

When this script is run on the server, the results of the query will be stored in the DataCommand1 recordset variable. A recordset is a representation of the data that is returned by the query. For example, if you insert the code in Listing 15.2 below the data command control, it will loop through and display all of the customers.

Listilng 15.215LIST02.TXT: Script to list all of the customers in the customer table.

*** End Listing ***

TIP: If at any time you need to edit your design-time control, right-click anywhere within the code generated by the control, and select Edit Design-time Control... from the menu. Script generated by design-time controls begins with:

<!--METADATA TYPE="DesignerControl" startspan

and ends with:

<!--METADATA TYPE="DesignerControl" endspan-->

If you wish to change the script generated by the control, make sure you remove the METATDATA comments, otherwise your changes will be overwritten the next time you open the control.

The Data Range Control

The Data Range design-time ActiveX control expands on the Data Command Control by adding the code that will be repeated for each record in the recordset. The Data Range Control consists of two separate controls: a header and footer. The header control incorporates the same logic as the Data Command Control, allowing the developer easily to build an SQL query. It also creates the code that will allow the user to navigate forward and backward through the results. The footer simply denotes the end of the data range loop and displays the navigation buttons.

To make the results returned by a Data Range Control presentable, you need to do a little legwork. By default, the data range header control displays data with limited formatting. I have included a template below to get you started. You can create your own .ASP page with your favorite HTML editor, such as Front Page 97. The complete file is available on the disk

ON THE DISK:
at CHAPTER15/TEMPLATE.ASP.

Listing 15.315LIST03.TXT: A sample template for inserting a Data Range Control.

*** End Listing ***

You can now add a Data Range Control to the .ASP file by taking the following steps:

  1. Highlight the section of code that reads "<!-- INSERT HEADER HERE -->." Right-click and select Insert ActiveX control.

  2. Click the Design-time tab, and double-click Data Range Header Control.

  3. The Data Range Header Control and its Property pages will appear. (See Figure 15.5.) In the drop-down list labeled Data Connection, choose the database connection for this control.

    Figure 15.5.
    The Data Range Header Control and its Property pages.

  4. Click the Advanced tab of the Property sheet. Change the Cursor Type to 1-Keyset. In order to enable "paging" through the records, this property needs to be set to either 1-Keyset or 3-Static. (For more information about this property, review the Cursor Type Property in the online help topics.)

  5. Click the Control tab of the Property sheet. Set the following properties:

  6. Start the Query Designer by pressing the SQL Builder button.

  7. Drag and drop the customer table into the Designer Pane of the Query Designer. Select the following columns from the table: CompanyName, ContactName, Country, and Phone. The Query Designer will look like Figure 15.6.

    Figure 15.6.
    The selected fields from the Customers table.

  8. Test the query by clicking the SQL Run icon on the Database toolbar. When you are satisfied with the results, close the Query Designer.

  9. Choose Yes when prompted to update the Database Connection.

The Data Range Control creates all the code you need to read the records from the database and page through them. However, it does not automatically create the code to display the records. The Data Range Control provides a mechanism to insert the appropriate fields into your .ASP file manually. Take the following steps:

  1. With the Data Range Control opened in the Object Editor, right-click the control and select Copy Fields... from the menu.

  2. The Copy Fields dialog box will appear, with the fields you selected in the Query Designer listed under Available Fields. (See Figure 15.7.)

    Figure 15.7.
    The Copy Fields dialog box copies script to the Clipboard.

  3. Move all the fields to the Fields to Copy box by pressing the button labeled >> (second button from the top).

  4. Press OK. The code to display the fields you selected has now been copied to the Clipboard. The format for this text is determined by the Range Type property that we set when we inserted the Data Range Header (see Step 5 above).

  5. Close the Data Range Control by clicking the Close Window button in the top right corner of the Data Range Header Control window. The appropriate code will be displayed in your .ASP page.

WARNING:
If you get an error message in your page that reads:
<!--
ERROR: This file must be an 'HTML Server-Side Script' file (.ASP).
Please open properties for this file and update the current language setting.
-->

You will need to select View, Properties from the menu, and change the Language to HTML Server Side Script. Then reopen the header control, and close it again. The new code should now appear.



To display the records in your HTML table, scroll down to the line that reads <!-- INSERT CLIPBOARD HERE -->. Highlight the line, right-click, and select Paste. The code that was generated by the Copy Fields dialog box will be inserted into your .ASP file. To complete this section, you need to cut and paste the individual entries into your table. Your row definition should look like Listing 15.4 when you have finished.

Listing 15.415LIST04.TXT: The row definition code after Clipboard contents have been inserted.

*** End Listing ***

To finish your page, insert a data range footer control. The footer denotes the end of the recordset loop and inserts the navigation buttons at the bottom of your page. To insert the footer, take the following steps:

  1. Scroll down to the line that reads <!-- INSERT FOOTER HERE -->.

  2. Highlight the line, right-click, and choose Insert ActiveX Control... from the menu.

  3. Click the Design-time tab and double-click the Data Range Footer Control.

  4. There are no properties that need to be set on this control, so close it by clicking the Close Window button in the top right corner of the Data Range Control window. The appropriate code will be displayed in your .ASP page.

Complete the page by selecting File, Save from the menu. When you view your new page in a browser, the database records will be displayed dynamically. Your results should look like Figure 15.8.

Figure 15.8.
A sample Web page created with the design-time Data Range Control.

One of the advantages of using the Data Range Control is that it makes only one call to the database to fill the recordset. So if your query returns a large number of records, the end user does not have to wait for a new query before viewing a new page. This can make a big difference if you have a slow connection or a large number of records in the recordset.

It is important to remember that the Data Range Control and the SQL Query Designer are capable of returning the results from multiple tables in the same query. I have included two sample pages that expand on our example from above. CUSTOMERS.ASP returns a table similar to Figure 15.8 above, only this time the contact names are hyperlinks. Selecting a name calls ORDERS.ASP with the CustomerID as part of the URL. ORDERS.ASP queries three tables and returns all orders for that customer. If you want to see how the queries were generated, you can right-click the Data Range header control, and press the SQL Builder button on the Property page. To use the pages in your project, you will need to create a project data connection to the Northwinds database.

NOTE: The Northwinds database is a sample database that is shipped with Microsoft Access. A copy of this database is included on the CD.

ON THE DISK:
The files are titled CHAPTER15/CUSTOMERS.ASP and CHAPTER15/ORDERS.ASP.



As an alternative to inserting the header and footer controls individually, you can insert both in the same step using the Data Control Wizard. The wizard will also set some of the properties for you. To use the wizard, take the following steps:
  1. Place your cursor at the location where you wish to insert the control, right-click, and select Insert HTML Using Wizard... from the menu. (You can also select Insert, Into HTML, Use Wizard... from the main menu.)

  2. Double-click Data Range Wizard in the Choose Builder dialog box.

  3. In Step 1 of the Data Range Wizard, select Show only 10 Records at a time. (Leave the number of records at 10.)

    TIP: You can always change these selections through the control's property page later.
  4. Click Next.

  5. In Step 2, keep the default value, and click Finish.

  6. Your Data Range Control Object and its associated Property page should now be visible in the Object Editor.

  7. Follow the steps presented above to finish inserting your control.

The Include Control

If you have common elements on your Web site, such as headers and footers, you know what a problem it is to update every page when a change is made to one of the elements. Active Server Pages allow you to create one file with the common element, and insert its contents into another file at run-time. This can also be very useful for including script functions and procedures, thus maintaining one version of the code.

Visual InterDev provides an ActiveX design-time control that makes inserting files easy. More importantly, it ensures that the path and filename you are inserting are valid. To insert an Include Control, take the following steps:

  1. Place your cursor at the location in your .ASP file where you wish to insert the external file.

  2. Right-click and select Insert ActiveX Control... from the menu.

  3. Click on the Design-time tab, and double-click the Include Control.

  4. The Include Control should now be visible in the Object Editor.

  5. Press the URL Builder button. The Edit URL dialog box will appear with the contents of your current project in the Projects Pane. (See Figure 15.9.)

    Figure 15.9.
    The URL Builder that is displayed by the Include Control.

  6. If the file you are inserting resides in a virtual directory on your Web server, select Root Relative for URL Type, otherwise select Doc Relative.

  7. Select the file you want to include by clicking it in the Contents pane.

  8. Click OK, and close the control by clicking the Close Window button in the top right corner of the Data Range Control window. The code shown in Listing 15.6 is an example of the script that will be inserted in your .ASP page.

Listing 15.515LIST06.TXT: An example of the script generated by Include Control.

Viewing and Modifying Data with the Data Form Wizard

The controls and examples I have shown you so far deal strictly with viewing data. This is fine if all you want to do is view reports. However, in the real world you often need to add or modify records in a database. And if the database is large, you may want to view only those records that meet certain criteria. In the past, building Web-based applications that are bound to one or many data sources was very complex and required a great deal of cgi programming. Today, companies live and work in "Web time," and Web-based applications always seem to be needed yesterday. Separate, stand-alone applications are being replaced by applications that "live" on the Internet or a corporate intranet.

One of the most useful tools included with Visual InterDev is the Data Form Wizard. The Data Form Wizard will help you build a custom application that will allow you to view and modify records, or search for records meeting certain requirements.

To build this example, we will again be using the Northwind Trader's database that ships with Microsoft Access. Take the following steps:

  1. From the menu, choose File, New. Click the File Wizards tab.

  2. Enter Customers as the filename, then double-click Data Form Wizard. (See Figure 15.10.)

    Figure 15.10.
    Starting a new Data Form Wizard.

  3. In Step 1, select DataConn as your database connection. If you do not have a project database connection created, you can click New... to establish the connection.

  4. Enter Northwind Traders' Customers for the title, then click Next.

  5. Use the default Table option for Step 2. Click Next.

  6. In Step 3, select Customers (Table) for the Table/View. Select all the fields by pressing the >> button. Your screen should look like Figure 15.11.

    Figure 15.11.
    The Data Form Wizard after selecting all of the available fields.

  7. Click Next to move to Step 4.

  8. Keep the default options in Step 4, and click Next.

  9. Again, keep the default options, and click Next.

  10. Step 6 allows you to select how you want your pages to look, based on a theme. Select the theme you like best, and click Next.

  11. Click Finis. The wizard will now generate three new pages in your application and copy the necessary graphics to your Web server.

You should now have a fully functional Web application that will allow you to view and modify the Customer database. Click the File View Pane and you should see three new pages: CUSTOMERSACTION.ASP, CUSTOMERSFORM.ASP, and CUSTOMERSLIST.ASP. To see the pages in action, right-click one of the new .ASP pages, and select Preview in Browser from the menu.

TIP: If you view the results of these pages, you will notice that the tables and list boxes are labeled with the field names from the database. To specify alternative text for fields, click the Advanced button in Step 6 and enter the text of your choice in the Alternative Field Label box.

    1. Copy file CHAPTER15/NORTHWIND.MDB to a directory on your Web server. If Visual InterDev is running on a machine other than your Web server, make sure this is a shared directory so you can access the file.


      ON THE DISK:
      NORTHWIND.MDB
    2. Right-click the GLOBAL.ASA file in the File View Pane.

    3. Select Add Data Connection from the menu.

    4. Click New.

    5. Select Microsoft Access Driver (*.mdb) from the list. Click Next.

    6. Enter NorthWinds for the name of the data source. This is your Data Source Name, or DSN.

    7. Click Finish.

    8. A new dialog box will now appear, titled ODBC Microsoft Access Setup.

    9. Under Database, press the Select button.

    10. If Visual InterDev is running on the same machine as your Web server, enter the path that you copied the file NORTHWIND.MDB to in Step 1. If Visual InterDev is running on a machine other than your Web server, you will need to map a network drive, then select the file.

    11. Click OK.

    12. NORTHWINDS.DSN should now appear in your list of data sources. Click OK again.

If you open your GLOBAL.ASA file, the following code should be in Session_OnStart procedure:

'==Visual InterDev Generated - DataConnection startspan==
--Project Data Connection
Session("DataConn_ConnectionString") = "DBQ=W:\SAMPLES\NORTHWIND.MDB;DefaultDir=W:\SAMPLES\;Driver={Microsoft Access Driver(*.mdb)};DriverId=25;FIL=MSAccess;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
Session("DataConn_ConnectionTimeout") = 15
Session("DataConn_CommandTimeout") = 30
Session("DataConn_RuntimeUserName") = "admin"
Session("DataConn_RuntimePassword") = ""
'==Visual InterDev Generated - DataConnection endspan==

When you have completed the preceding steps, a Data View tab will appear in the project workspace, and a DataConn entry should appear under your GLOBAL.ASA file in the FileVieöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþrect for your development machine, it will probably fail for your Web server. To solve this problem, edit the connect string to specify a Universal Naming Code (UNC) path to your database, instead of the mapped drive information. Change the DataConn_ConnectionString variable to read:

"DBQ=\\MyServer\Samples\northwind.mdb;DefaultDir=W:\SAMPLES\;Driver={Microsoft Access Driver(*.mdb)};DriverId=25;FIL=MSAccess;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

where MyServer is the name of your server.

From Here...

The following chapters will provide you with more information about database access and ActiveX Controls: