by Chris H. Striker
One of the more tedious tasks you'll undertake when developing dynamic, database-driven web pages is writing the code that connects to a database, generates HTML from retrieved data, and provides interactivity with the database. Fortunately, Visual InterDev contains a handful of design-time ActiveX controls to make this task considerably easier; in fact, the combination of some of these controls takes nearly all the work out of the task. With the Data Command control or the Data Range Header and Footer controls (together making up the Data Range controls), most of the grunt work of incorporating database access in web pages is done for you.
You use the design-time controls in very much the same way you use other ActiveX controls--they're inserted in pages as objects, have parameters and properties you can set, and are accessible via scripting. As opposed to other ActiveX controls, which run on the client, design-time controls reside and function on the server; also, they're available only in your development environment (Visual InterDev). For example, the Include control, the other design-time control included in Visual InterDev, inserts the contents of another file into your web page. This happens when the page is requested--the server looks through the contents of the page, sees the Include control object, and replaces the object with the contents of the file specified by the control. The page with the inserted contents is then sent down to users.
NOTE: This chapter assumes that you're comfortable with Active Server Pages (ASP), server-side scripting, and ActiveX controls.
See Chapter 8, "Using ActiveX Client Components in an I-net Environment," for information on ActiveX controls.For information on Active Server Pages, see Chapter 13, "An Inside Look at Active Server Pages and Internet Information Server," and Chapter 14, "Developing Active Content with Visual InterDev."
See Chapter 17, "Server-Side Programming in an I-net Environment," for information on server-side scripting.
Before you incorporate the Data Command, Data Range Header, or Data Range Footer controls in your pages, you must first define a data connection. Visual InterDev makes this easy. Open your web project in Visual InterDev and look at the global.asa file, which contains the code for the data connection.
NOTE: The database used for the following example, as well as successive examples throughout the chapter, is the Northwind database that ships with Microsoft Access. (This database is included with Microsoft Office 97 Professional Edition.)
Follow these steps to establish a data connection for your web project:
FIG. 16.1
Throughout Visual InterDev, context-sensitive menus appear with a right-click.
Here, you're presented with the option to add a data connection because Visual InterDev
recognizes that you've right-clicked the global.asa file.
TIP: Step 5 saves the data source file in the default location for your machine. If you want to specify another location, you can browse to the location and select it.
FIG. 16.2
After you select your data source here, Visual InterDev inserts the code necessary
to build the data connection in your global.asa file.
This procedure specifies a data connection for your web project. Notice that the global.asa file now features an expansion plus sign next to it in the File View in Visual InterDev. If you expand the global.asa file, you can see the data connection. When you open the global.asa file, you should see code similar to the following:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
Visual InterDev Generated - DataConnection startspan==
Project Data Connection
Session("DataConn_ConnectionString") = "DBQ=D:\VISUAL STUDIO 97\DESIGN TIME CONTROLS\Northwind.mdb;DefaultDir=D:\VISUAL STUDIO 97\DESIGN TIME CONTROLS;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;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==
End Sub
</SCRIPT>
Notice that Visual InterDev has set several session properties:
Now that the data connection is defined, you can use the database tools included with Visual InterDev. Switch to the data view of the workspace window or double-click the DataConn object below the global.asa file in the File View of the workspace window. Notice that the objects in your database are exposed. Expand the tables section and double-click one of the tables. The table opens and the Query toolbar appears (compare your screen to Figure 16.3). With these tools, you can write SQL by hand or build statements with the visual tool (by dragging and marking fields), and you can test the statements. You'll use these tools to build the SQL used by the Data Range control to return recordsets.
FIG. 16.3
Visual InterDev provides a fairly comprehensive set of tools with which to work with
data. Here, the Customers table of the database is opened, displaying its contents.
The Data Command control provides an easy way to generate the server-side code necessary to connect to a database and to define your data objects. If you're developing a web page that accesses a database and want to maintain control over the interactivity with the database (filtering, sorting, and so on) and the generation of HTML to display content, you'll probably want to use the Data Command control. This control generates the code necessary to establish the connection, passes the SQL needed to generate a recordset for your use, and defines the returned recordset object.
The properties listed in the following sections determine the functionality of the Data Command control. As with most properties, they're displayed on your Active Server Page as parameters (specified by the <PARAM> tag) within the <OBJECT> element for your control. Keep in mind that many of the properties for the Data Command control are also used by the Data Range Header control.
The CommandText Property This property is the command/SQL to be issued against the database specified in your data connection. You can set this property by any of the following three methods:
The CommandText property works with the CommandType property described later. If you set the CommandType property to SQL, the SQL Builder button is enabled, and you can enter your own SQL into the CommandType property text box or use the SQL Builder to generate the SQL. If the CommandType property is set to Table, View, or Stored Procedure, the drop-down list is populated with these objects from your database.
The CommandTimeout Property The CommandTimeout property indicates how many seconds ADO should wait for a command to execute against the database before terminating the command and generating an error. The default for this value is 30 seconds; ADO waits indefinitely if you set this property to 0.
NOTE: ADO, or ActiveX Data Objects, is the newest of the data-access methods Microsoft offers. It's slated to succeed DAO (Data Access Objects) and RDO (Remote Data Objects).
The CommandType Property This property indicates what type of command is being passed by the CommandText property: SQL, Stored Procedure, Table, or View. See the discussion on the CommandText property for more information.
The CursorType Property The CursorType property indicates the type of cursor used with the recordset returned by the control. A Forward Only cursor allows only forward movement through the recordset (via the MoveNext method); this is the default CursorType. The Keyset cursor allows forward and backward movement, fixes membership, and reflects the current state of the database. A Dynamic cursor allows forward and backward movement and dynamic membership; it reflects the current state of the database, and new rows inserted by other users appear automatically. The Static cursor type allows forward and backward movement, but the recordset is a snapshot. It doesn't necessarily reflect the current state of the database and isn't automatically refreshed to indicate changes by other users.
NOTE: Fixed membership means that the data in the returned recordset is valid for the moment at which the recordset is built; changes to the database won't be reflected in the recordset. Dynamic membership means that the recordset reflects changes to the database since the recordset was created.
The DataConnection Property This property indicates which data connection the control should use when building recordsets. The list of data connections is built from the connections that already have been added to your web project (typically in the global.asa file).
The ID Property The ID property is the standard identification property used for all ActiveX controls; it's the handle by which controls are referenced via scripting. You can change this property although it's assigned a unique value derivative of the type of control when you insert the control. A valid ID has the following characteristics:
Examples of valid IDs are DataCommand1, dcOne, dc_Two, and txtBox.
The LockType Property The LockType property indicates the type of locking to be used when updating or inserting. The default value depends on the provider but is typically Read-Only, which allows no modifications. Pessimistic tells the provider to do as much as necessary to ensure successful updates, Optimistic doesn't lock records until they're actually updated, and BatchOptimistic is the required LockType for batch update mode.
The MaxRecords Property With this property, you can limit the number of records returned in your recordset. This property is set to 0 (all records) by default.
The Prepared Property This property indicates whether you want a prepared version of your command before execution. Although this doesn't make the speed of the first issuance of the command faster, it does speed successive executions. Keep in mind that not all providers support prepared statements.
In this example, you add the Data Command control to a new Active Server Page. You also build a short VBScript routine to display the contents of the recordset returned by the Data Command control. This is the most flexible means of accessing data via Visual InterDev's design-time controls. As is usually the case, however, flexibility comes with a price; it's up to you to provide a means of displaying and interacting with the data.
FIG. 16.4
Properties dialog boxes are available for nearly every object you'll use in Visual
InterDev. Typically, they provide an easy means of access to all properties exposed
by the object.
FIG. 16.5
When possible, Visual InterDev attempts to make things easier on you. In this case,
Visual InterDev knows to populate the Data Connection drop-down list with the connections
specified in your global.asa file.
TIP: If you click the All tab of the Properties dialog box, you get a list of all properties for the control in one place, presented in the list format common to all controls. If you feel more comfortable setting properties in this format, you can do so from the All tab.
Try previewing your page by first saving it (choose File, Save) and then right-clicking anywhere on the page. Select to Preview <Page>. Notice that nothing shows up. Go back to the code for the page and look--nothing shows up because there's no code to access the contents of the recordset you directed the Data Command control to provide. The Data Command control establishes the connection to the data source and provides a recordset for your use, but that's all it does. Accessing the recordset and presenting its data on your page is up to you.
Clearly, this provides the most flexibility but requires the most work. A quick and dirty way to get the content on the page is to follow the closing <METADATA> tag that Visual InterDev inserts with this server-side script:
<%
Dim holdField
Do Until DataCommand1.EOF
For Each holdField In DataCommand1.Fields
Response.Write holdField.Value & "<BR>"
Next
DataCommand1.MoveNext
Loop
%>
This code loops through the recordset and writes the contents of each field in the fields collection to the page, using the Write method on the Response object. Try this and then preview your page again. This time you should see all your data appear on the page (but in a format you probably wouldn't want to present to a user).
ON THE WEB:You can check your code against the file datacommand.asp, which you can find at http://www.gasullivan.com/vs97book.
The Data Range Header control is similar to the Data Range Command control in that it also establishes connection and recordset objects for your use. The similarity ends there, however. The Data Range Header control, with the Data Range Footer control, provides you with an easy way to use the returned recordset to generate HTML that displays the data from a database, as well as the navigation controls necessary to move through pages of the data (if configured to do so). This process isn't fully automatic--you still need to weave the contents of the returned recordset object into your HTML--but the process is greatly simplified.
You need to be familiar with a few concepts in using the Header control (and the Footer control). First, keep in mind what you're building. Typically, you'll set up an HTML table structure to hold data contained in a recordset object. You'll want to set up the table first, preceding the data row of the table with the Data Range Header control and following with the Data Range Footer control. The code generated by these two controls includes a Do...Loop structure, so the row is repeated for each record in the recordset, thus generating the HTML for repeated rows.
Another key concept is paging. Without the use of paging, every record returned in a recordset is displayed on the web page. This could be a very large number of records, causing performance to suffer considerably. The page is built on the server in its entirety before it's sent to the client. If the page contains a table with hundreds or thousands of rows, users might have to wait for an unacceptable length of time.
By using paging, you can set up a page that returns a fixed number of records from the recordset at a time and provides controls that allow access to other pages. This increases performance considerably, especially for recordsets with large numbers of records.
The CommandText, CommandTimeout, CommandType, CursorType, DataConnection, ID, LockType, and MaxRecords properties for the Data Range Header control are the same as the ones for the Data Command control. The Data Header control also exposes the BarAlignment, CacheSize, PageSize, and RangeType properties.
The BarAlignment Property This property lets you specify whether you want the navigation bar to be left-justified, centered, or right-justified on your page. Keep in mind this applies only if you're using paging; otherwise, all records display on your page and a navigation bar isn't necessary.
The CacheSize Property This property allows you to set the size of the cache in which records from a recordset are stored locally. For Forward Only cursors, the default value is 1; for other types, the default is 10. No error is generated if the returned number of records is less than the cache size. A cache size of 0 isn't allowed.
The PageSize Property With the PageSize property, you can specify how many records you want to appear on a page when using paging. If you set this property to 0, paging is disabled, all records in the recordset appear on one page, and the navigation bar doesn't appear.
The RangeType Property With this property, you can control the placement of the navigation bar in the generated server-side script, as well as the output of the Copy Fields dialog box. If the RangeType property is set to Table, the navigation bar is placed outside the table element. If the property is set to Text or Form, the navigation bar is placed inside the table. If the property is set to Text or Table, the output of the Copy Fields dialog box is of the form
<%=DataRangeHdr("FieldName")%>
The server replaces this value with the contents of this field. However, if you set the RangeType property to Form, the Copy Fields dialog box provides code like this:
<INPUT TYPE="Text" SIZE=25 MAXLENGTH=60 NAME="FieldName" VALUE="<%=
DataRangeHdr("FieldName") %>"><BR>
In this case, the output surrounds the contents of the field with the HTML necessary to define the element as an HTML-intrinsic input control for a form.
Follow the same procedure you used earlier to create another Active Server Page in your web project. This time, rather than insert the Data Command control in your page, use the Data Range Header and Data Range Footer controls. Before inserting them, however, you need to create a table structure in which to place the controls.
Find the place on the page where this line appears:
<!--Insert HTML here -->
Highlight the line and replace it with this rudimentary table structure:
<TABLE>
<TR>
<TD>Company Name</TD>
<TD>Contact Name</TD>
<TD>Order Date</TD>
</TR>
In this table, the row displays the names of the fields. The second row (to be added shortly) displays the contents of the actual fields themselves. You'll need a server-side looping structure wrapped around the second row so that the server will send HTML for repeated rows of the table to the client, one row for each record in the recordset. Perform the following steps to create this structure:
Your screen should now look similar to the one in Figure 16.6. (You might want to expand the Query Builder window so that you'll have more space within which to work.) If your Query Builder window isn't showing all the panes visible in Figure 16.6, enable them from the Query toolbar. The first four buttons on the toolbar are for the Diagram Pane, the Grid Pane, the SQL Pane, and the Results Pane. The Diagram Pane is the space into which you drag tables and columns; the Grid Pane gives you another graphical view of the SQL you're building; the SQL Pane is where you can view and alter the SQL you're building; and the Results Pane is where you can see what your SQL returns.
Notice that the Workspace view has changed from File View to Data View as a result of your having opened the SQL Builder. Here you should see your tables, views, and other objects for the database specified in your data connection (in the global.asa file). As you continue, this chapter assumes that you're working with the Northwind database. Following these steps, finish building your SQL statement:
FIG. 16.6
One of the most useful database-oriented features of Visual InterDev is the Query
Builder, which provides a visual environment in which you can build and test SQL
statements.
FIG. 16.7
The panes in the Query Builder communicate with each other--a change made in one
pane is immediately reflected in all other affected panes.
FIG. 16.8
Be sure to fully explore the Query Builder. For instance, the Query toolbar has buttons
that provide the framework for insert, update, and delete queries automatically.
TIP: You can change the nature of the join by right-clicking the join indicator and selecting properties.
This is just the beginning of what you can do with your SQL. To sort on the Order Date, for example, click the Sort Type drop-down list in the Grid Pane for the Order Date field and select Ascending. Run the Query again and notice that the results are now sorted in ascending order by the Order Date.
FIG. 16.9
Remember to test your SQL before closing the Query Builder. It's much easier to ensure
that your SQL is valid and that you're retrieving the data you want here than in
your web page.
Notice that the Query Builder indicates it's building the SQL for the Data Range Header control. Everything you've been doing in this window is just to prepare the SQL for the control. You can close the Query Builder window at this point. When Visual InterDev asks whether you want to update the Data Range Header control, respond yes. If the Properties dialog box for the control isn't open, right-click the control itself and choose Properties. Notice that the SQL you built in the SQL Builder now appears in the Command Text box. Compare your dialog box to Figure 16.10.
FIG. 16.10
Properties for the Data Range Header control, after building the SQL for the CommandText
property in the SQL Builder.
In the Control tab of the Properties dialog box, check the box for Record Paging. Notice that Visual InterDev won't allow this yet because the default CursorType is Forward Only. Record Paging requires a cursor to be selected that enables forward and backward movement through the recordset. Select the Advanced tab of the Properties dialog box and change the Cursor Type to 1 - Keyset. Go back to the Control tab and try turning on Record Paging again. This time, the setting is accepted. You can now change the Page Size setting if you want (for this sample, leave it at 10).
You need to set two other properties at this point. On the Control tab, change Bar Alignment to 2 - Centered and RangeType to 2 - Table. The Data Range Footer control responds to these settings to determine how to position the buttons that navigate through pages in your recordset and to close off your table correctly after the rows for the table are generated.
NOTE: If you want, you can adjust other properties, such as CacheSize; refer to the earlier property information for the Data Range Header control for more details.
Before you close the Data Range Header control, click the Copy Fields button on the Control tab to copy to the Clipboard the code you need to specify the fields in your table cells. In the Copy Fields dialog box, move all three fields to the right-hand list. Click OK and close the control.
Place your cursor after the closing <METADATA> tag and choose Edit, Paste to insert the server-side code for the fields you copied from the Data Range Header Properties dialog box into your page. The code should look similar to this:
<%= DataRangeHdr1("CompanyName") %><br>
<%= DataRangeHdr1("ContactName") %><br>
<%= DataRangeHdr1("OrderDate") %><br>
Position your cursor at the beginning of the first of these lines and enter <TR>. Then wrap each line with <TD> and </TD> tags to place each in its own cell. At this point, your code should look like this:
<TR>
<TD><%= DataRangeHdr1("CompanyName") %><br></TD>
<TD><%= DataRangeHdr1("ContactName") %><br></TD>
<TD><%= DataRangeHdr1("OrderDate") %><br></TD>
You've completed the first part of the Data Range control structure. Next, you need to add the Data Range Footer control.
The Data Range Footer control is used only with the Data Range Header control; it provides the server-side code necessary to complete the looping structure that pulls data from your recordset, as well as the navigation controls required when using paging. If you set the properties for the Data Range Header control so that it's not using paging, you don't need the footer control--you simply need to write the server-side code that completes the loop. (Keep in mind that performance will deteriorate considerably as the number of records returned in the recordset grows.)
No properties are associated with the Data Range Footer control because this control works only with the Data Range Header control. All the functionality of the Data Range Footer control is controlled by the property settings for the Data Range Header control.
Go back to the file you were creating, using the Data Range Header control. Find the following code:
<TR>
<TD><%= DataRangeHdr1("CompanyName") %><br></TD>
<TD><%= DataRangeHdr1("ContactName") %><br></TD>
<TD><%= DataRangeHdr1("OrderDate") %><br></TD>
Position your cursor at the beginning of the line following this code and then follow these steps:
TIP: You can check your code against the file datarange.asp on the web site at http://www.gasullivan.com/vs97book/.
Preview the page and experiment with the controls provided for paging through the records. Clearly, the presentation isn't pretty; you can change the HTML on your own to make the pages more attractive.
The Include control is a bit different than the other design-time controls included with Visual InterDev. Unlike the others, this control doesn't provide any sort of database functionality; it provides a means by which you can tell a web server to include the content of another file (typically, an .inc file) in a page that the users have requested.
This control is very useful. If you have a large number of pages that share common HTML elements (for example, navigation elements, header or footer elements, or style sheets), you can put together files that contain only these elements and reference the files on all pages that need them by using the Include control. Also, you can prepare include files that contain scripting functions you want available on multiple pages in your web project. This makes administering, maintaining, and modifying your site much easier; each page is less complex because it contains only code unique to that page, and any changes to common elements in your site need to be made only in the files included via the Include control.
The only property for the Include control is Source, which specifies the file to insert into the page containing the control. The syntax for the path to the control is the same as you would use in referencing all other files within your web page (virtual and relative paths, the ../ construct for referencing files in the directory above the current directory, and so on).
NOTE: A path to an include file might read /includes/sampleinclude.inc if the sampleinclude.inc file is in a subdirectory under the current page's directory; it might read sampleinclude.inc if the sampleinclude.inc file is in the same directory as the current page; and it might read ../sampleinclude.inc if the sampleinclude.inc file is in a directory immediately above the current page's directory.
Add a new HTML file called footer.htm to your web project. Replace the HTML in the file with the following:
<center> <font face = "verdana" size = 1 color = midnightblue> ________________________<BR> copyright(c) 1997 My Company all rights reserved </font></center>
Close the file. Generally, include files end with an .inc extension so that tracking which files are include files and which are files that reference (include) include files is easier. Rename your footer.htm file to footer.inc. To do so, right-click the file in the File View of the Workspace window. Select Release Working Copy. Right-click the file again and select Rename. Change the name of the file from footer.htm to footer.inc.
NOTE: The .inc extension isn't required for the Include control to function. However, it's generally a good idea--and standard practice--to use the .inc extension to differentiate these files from the others in your web project. It takes a little extra work, but as your project grows, you'll find it's worth the extra steps.
Now add a new Active Server Page called home.asp to your web project. It's to this file that you'll add the Include control with a reference to the footer.inc file. Follow these steps:
When you preview the page, notice that the contents of the footer.inc file appear in the Active Server Page. You can put whatever you want in a file to be included via the Include control, and you can include any number of files anywhere you want within an Active Server Page. The only caveat here is that you can't dynamically provide the value of the source property through scripting. The following HTML code specifies the Include control; notice that the method Visual InterDev uses to indicate the footer.inc file doesn't allow for dynamic inclusion.
<!--METADATA TYPE="DesignerControl" startspan
<OBJECT ID="Include1" WIDTH=151 HEIGHT=24
CLASSID="CLSID:F602E725-A281-11CF-A5B7-0080C73AAC7E">
<PARAM NAME="_Version" VALUE="65536">
<PARAM NAME="_ExtentX" VALUE="3969">
<PARAM NAME="_ExtentY" VALUE="635">
<PARAM NAME="_StockProps" VALUE="0">
<PARAM NAME="Source" VALUE="footer.inc">
</OBJECT>
-->
<!--#INCLUDE FILE="footer.inc"-->
<!--METADATA TYPE="DesignerControl" endspan-->
In this chapter, you worked a bit with the design-time controls included with Visual InterDev. The Include control is a solid utilitarian control that you'll find extremely useful. The data-related controls can be considered decent starting points; they don't generate fully developed code, nor do they construct attractive interfaces, but they do take a good deal of the grunt work out of building data-driven web pages. By using them, you can focus your energies on fleshing out your application and applying finishing touches.
© Copyright, Macmillan Computer Publishing. All rights reserved.