Downloads
5224.zip

The quick and easy way to display database information on the Web

If you need to extract data from SQL Server and display it on a Web page, you'll appreciate the Microsoft Visual InterDev 6.0 Grid Design-Time Control (DTC). In the context of SQL Server, DTC usually stands for Distributed Transaction Coordinator. However, for Web developers, DTC means Design-Time Control. A DTC is an ActiveX control that emits htm. The Grid DTC creates an htm table and populates it with data you specify. Screen 1 shows how a Grid DTC looks rendered in a user's browser.

You can use the Grid DTC to display data from a SQL Server table or view on a Web page in an attractive htm table in less than five minutes. You can control the number of records displayed at a time and provide navigational controls simply by setting properties. Also, you can calculate and display returned data values. With some more work, you can let users sort columns and perform other similar tasks.

DTCs make developing Web-based applications easier. The Grid DTC encapsulates common functionality so that you can easily reuse it. For example, you often need to display information from a table or view in a grid format. To generalize this code, you might first try to create a function and then pass in the column names and some parameters to affect the look of the rendered htm table. That approach is what a DTC does, too. The difference is that a DTC lets you set those parameters on a Properties page. A user's browser never downloads the DTC control. Instead, when you save your work during the design phase, the DTC emits Active Server Pages (ASP) code. Listing 1, page 58, shows the code behind the DTC I used to generate the grid in Screen 1. Visual InterDev generated this code when I added the DTC object to the Visual InterDev form.

You can see the code underlying any DTC by right-clicking on the DTC in design mode and selecting Show Run-time Text. The Visual InterDev editor won't let you change this code because if you do, the DTC can't interpret the changes. If you need to alter the underlying code, you can right-click on the DTC in design mode and select Convert To Run-time Text. Because you can't undo the conversion, you'll see a dialog box requesting confirmation.

The Grid DTC is data-bound, which means it's linked to another DTC, the Recordset DTC, which provides data for the Grid DTC to display. The data source can be a table, a view, a stored procedure, or Data Environment commands. In this example, I use a view.

To add a Grid DTC to an Active Server Page, you:

  1. Add a data connection to your project by right-clicking the project name in Project Explorer and then selecting Add Data Connection from the menu.
  2. Rename the data connection. The data connection will first appear as a sub-item to DataEnvironment under Global.ASA in the Project Explorer. But future actions depend on the data connection's name, so rename it now to something appropriate.
  3. Place a Recordset DTC on the page. The Recordset DTC and other DTCs are in the Toolbox under the Design-Time Controls tab. At this point, you see a prompt to install the scripting object model, which is script code in the project folder_Script Library. The scripting object model provides a library of objects with methods and properties that script code can act on. If you receive this prompt, install the script library.
  4. Set the Recordset's connection to the Data Connection you renamed in step 2, select the type of database object you want to display (table, view, etc.), and select the object. Change the Recordset's name to something meaningful for later use.
  5. Place a Grid DTC on the page.
  6. On the Data tab of the Grid's Properties page, set the Recordset property to the Recordset you specified in step 4. You set the DTC properties by right-clicking the object, then selecting properties from the pop-up menu. Then select the fields you want to display.
  7. Right-click the page you're working on, and select View In Browser. You need to have the local Web server running to view the page. Note that, because the page might be cached, the second and subsequent times you view the page, you might need to click refresh in the browser to see your changes.

But I Want More


The Grid DTC can do much more. For example, in addition to displaying columns that are bound to a Recordset DTC, you can also display unbound columns. In these unbound columns, you can call functions to perform almost any operation. The last column in Screen 1 is an unbound column I placed an expression in. Listing 2 shows the function that's being implemented. The listing converts the title to upper case.

To use an expression within the Grid DTC, I first wrote the function Listing 2 shows. Then, in the Field/Expression text box on the Data tab of the Grid Properties dialog box, I placed this text:

=ConvUpper(\[title\])

When the page is requested, the Grid DTC's underlying code calls the function and places the calculated values into the grid.

Here are a few tips for using expressions: Precede your function call with an equal sign; place any data column names in square brackets; and place any literal values within quote marks. If the expression for the unbound column is based only on the recordset data, you might want to alter the underlying query to make the calculation. However, this feature is handy if the expression is based on a value that's unique to a particular user's system, such as the local time.

The Grid DTC provides a high level of commonly used functionality. Because it reduces the time you spend displaying information from a database in a grid format, the Grid DTC is a control that's well worth getting to know.