What Is the Grid Design-Time Control?

Article
07/12/2006

Microsoft Corporation

October 1998

Summary: Discusses how to create HTML tables using the Microsoft Visual InterDev version 6.0 Grid Design-Time Control (DTC). (8 printed pages) Covers:

Creating links
Calling functions
Sorting data by columns
Changing tables

Introduction

The Grid Design-Time Control (DTC) is a Microsoft® ActiveX® control that creates HTML tables. The Grid DTC is a data-bound control that uses the Recordset DTC to retrieve data from a data source and displays the information in an HTML table.

You can use the Properties dialog box at design time to control the appearance of the HTML table that is generated. In the General tab of the Properties dialog box, you can set a predefined style for the grid. The Data tab allows you to control what is displayed in the different columns. The Navigation tab lets you enable and disable page and row navigation. The Borders and Format tabs allow you to customize beyond the setting of the style applied in the General tab. The Advanced tab allows you to add additional information into the TABLE, TR, TH, and TD tags generated by this control.

How Do You Use the Grid DTC?

The Grid DTC cannot be used without a Recordset DTC on the page. The Recordset DTC provides the data for the grid to display. Once the Recordset control is added to the page and the properties have been set appropriately, you can point the Grid control to the recordset, using the Data tab of the Properties dialog box. You can also choose which fields to output.

Calling Methods

There are few methods exposed by the Grid control. These methods can be called by functions or other controls, such as the FormManager DTC. With the FormManager control, you can call the Hide and Show methods to control the visibility of the grid. Before calling these methods, you would want to know whether the grid is already visible. Using Microsoft Visual Basic®, Scripting Edition (VBScript) or MicrosoftJScript®, you can call the isVisible method of the Grid control to see if it is visible or not. Here is a small JScript function that can be called to toggle the visibility of the Grid control:

function Button1_onclick() 
{
   if (Grid1.isVisible() )      
      Grid1.hide();
   else
      Grid1.show(); 
}

Two other methods that are available for the Grid control are getPagingNavBar and getRecordsetNavBar. These methods create references to the navigation controls so their methods can be called, and their properties can be set. Here is an example of a call to the getRecorsetNavBar:

objRecordsetNavbar = Grid1.getRecordsetNavbar(); 
objRecordsetNavbar.updateOnMove = false;

The last method available for the Grid control is the bindAllColumns method. This method is used to tell the Grid control to display all columns for the associated recordset. See Example 4 later in this document for an example of using this method.

Using Expressions

The Field/Expression property, on the Data tab of the Grid Properties dialog box, supports expressions. A string that begins with an equal sign ( = ) indicates it is an expression. An expression can be built from a combination of values—strings, fields, numbers, and functions—in order to produce a string that will be written to the page.

An equal sign ( = ) at the beginning of the Field/Expression indicates it is an expression.
Square brackets [ ] are placed around a field name to return the field's value (for example, [FirstName]). The value is returned as a string.
Square brackets that are placed inside quotation marks are treated as text and not evaluated. For example, "[First Name]" produces [First Name].
Expression syntax is consistent with JScript syntax, except expression syntax also supports field names surrounded by square brackets.
HTML must be placed inside quotation marks so it is treated as text.
The end result of an expression must evaluate to a string.

See the following examples.

Selecting the Current Record

The Grid allows you to specify "record navigation," which means that users can select a specific record in the Grid to work with. This is useful if you have additional controls on the page that are bound to the record set; users can then use the Grid to select a record, and additional controls to edit it.

You can specify record navigation in the Navigation pane of the Grid's Properties window. Just select Enable row navigation. This adds a "next" and "previous" button to the Grid's navigation toolbar. You can also specify details such as what color to use to highlight the selected row and what captions to put on the row navigation buttons.

Buttons are the default way to navigate between rows in the Grid. Of course, a more convenient way is to simply click the row you want to select. If you're using the Grid in a client page (an .htm file), you get this feature for free because it's built into the DHTML version of the Grid. However, if you're using the Grid on a server page (an .asp file), you are by default limited to using only the row navigation buttons.

Fortunately, an undocumented feature of the .asp version of the Grid, referred to as an "anchor," allows you to add this click navigation feature. Here's what you do. First, decide what column in the Grid users can click on to move between records. (That's often the leftmost column.) Then in the Data tab of the Grid's Properties window, select that column. In the Field/expression box, instead of binding directly to a data field or to an expression, wrap the field or expression into a call to the anchor method. For example, if the leftmost Grid column is currently defined to display the emp_id field, it will look like this:

emp_id

To make it a click-select column, use this expression instead:

=Grid1.anchor([emp_id])

You won't find any explicit documentation on the anchor method in the Microsoft Visual InterDev® 6.0 documentation. You also won't find it exposed in statement completion if you script against the Grid, because this method was created for this single purpose, namely to add click navigation available in the .asp version of the Grid.

Example 1: Creating Links

You can use the data from a table to create links in your grid. The field can contain a URL or an e-mail address. The following example will show how to create MailTo links using a field in a table.

To create links in your grid

Open a project in MicrosoftVisual InterDev 6.0.
If the project does not already have a data connection, add one for your database.
Add a new ASP page to the project.
Add a Recordset control to the page. (When prompted, say yes to enable the Scripting Object Model.)
Set the properties of the Recordset control to point to the data connection, database object, and table that you want to work with.
Add a Grid control to the page.
Right-click the Grid control, select Properties from the pop-up menu to open the properties window, and set the following properties:
- In the Data tab, set Recordset to the name of the Recordset control created in step 4.
- Select the fields you want to display from the Available fields list.
- For the field you want to contain the link, type the following in the Field/Expression text box:
  
  ="<a href=mailto:" + [FieldName] + ">" + [FieldName] + "</a>"
  
  FieldName is a field containing an e-mail address.
Save the ASP page, and view it in your browser. One column of the grid should have a MailTo link in every row.

Example 2: Calling Functions

By calling functions within the Grid control, you can change the output of the grid based on logical conditions. The following example uses the Authors table from the Pubs database in Microsoft SQL Server™, and the State field was selected for output. It will output the state column as red text for all the rows that are not in the state of California.

To call functions within the Grid control

Open a project in Visual InterDev 6.0.
If the project does not already have a data connection, add one for your database.
Add a new ASP page to the project.
Add a Recordset control to the page. (When prompted, say yes to enable the Scripting Object Model.)
Set the properties of the Recordset control to point to the data connection, database object, and table that you want to work with.
Add a Grid control to the page.
Right-click the Grid control, select Properties from the pop-up menu to open the properties window, and set the following properties:
- In the Data tab, set Recordset to the name of the Recordset control created in step 4.
- Select the fields you want to display from the Available fields list.
- For the field you want to conditionally format in the table, type the following in the Field/Expression text box:
  
  =formatField([FieldName])
  
  FieldName is the name of the field you are conditionally formatting

Add the following script block to the page:

<Script Language=JavaScript Runat=Server>
function formatField(strText)
{
   // change color of state to red, unless state is California
   if (strText != "CA"){
      return "<FONT color=Red>" + strText + '</FONT>';
   }
   return strText;
}
</Script>

Change the conditional statement to match one that will work with your data.

Save the ASP page, and view it in your browser. All the fields that meet the condition should be red, and the rest should be black.

Example 3: Sorting Data by Columns

Once you have your Web page displaying your data the way you want, you may choose to give the person viewing the page options for further refining the data. This example shows how you can allow the viewer to sort the columns of the table generated by the Grid control.

To sort data by columns

Open a project in Visual InterDev 6.0.
If the project does not already have a data connection, add one for your database.
Add a new ASP page to the project.
Add a Recordset control to the page. (When prompted, say yes to enable the Scripting Object Model.)
Set the properties of the Recordset control to point to the data connection, database object, and table that you want to work with.
In the Implementation tab of the Recordset Properties dialog box, clear Automatically open the Recordset.
Add a Grid control to the page.
Right-click the Grid control, select Properties from the pop-up menu to open the properties window, and set the following properties:
- In the Data tab, set Recordset to the name of the Recordset control created in step 4.
- Select the fields you want to display from the Available fields list.
- For each of the fields you have selected, type the following in the Header text box:
  
  =sortAnchor('field_name','display_text')
  
  Field_nameis the name of the field you are editing the header for, andDisplay_text is the text you want displayed in the header.

Add the following script block to the page:

<SCRIPT LANGUAGE=javascript RUNAT=Server>
   function sortAnchor(sortField,strText){
      return "<a href=javascript:thisPage.navigate.sortTable('" + 
               sortField + "')>" + strText + "</a>";
   }

   function thisPage_onenter(){
      if (thisPage.firstEntered) {
         // change au_id to your default sort field
         sortTable("au_id")
      }
   }

   function sortTable(sortField){
      // change Authors ot your table name here:
      newSQL = "Select * from Authors ORDER BY " + sortField;
      Recordset1.close();
      Recordset1.setSQLText(newSQL);
      Recordset1.open();
   }
</SCRIPT>

Add a Page Object control to the page.
Open the properties window for the Page Object control, and add sortTableto theNavigate methods list.
Edit the first line of the sortTable function to point to the table you are displaying.

Save the ASP page, and view it in your browser. Clicking the text in the columns should refresh the page, and the data should be sorted by that column.

How the Example Works

This example uses three functions and three DTCs. The first function, sortAnchor, formats the anchor tag in the column header of the grid. By using this function, we eliminate the need to type in a long string of text into each column header. There are two parameters passed to this function. The first parameter is the name of the field that is going to be sorted. The second parameter is the text that will appear at the top of the column.

   function sortAnchor(sortField,strText){
      return "<a href=javascript:thisPage.navigate.sortTable('" + 
               sortField + "')>" + strText + "</a>";
   }

The second function checks to see if the browser has just navigated to the ASP page or if the page is being processed as a result of a round trip to the server to post a form. If this is the first time the page is processed, the sortTable function is called to set the default sort column.

   function thisPage_onenter(){
      if (thisPage.firstEntered) {
         // change au_id to your default sort field
         sortTable("au_id")
      }
   }

The third function changes the SQL statement in the Recordset control. The first line of the function takes the parameter that gets passed in and creates a new SQL statement that changes the sort order. Then, a conditional statement is run to make sure the recordset is closed so changes can be made to it. The setSQLText method is called to apply the newly created SQL statement to the recordset, and then the recordset is reopened.

   function sortTable(sortField){
      newSQL = "Select * from Authors ORDER BY " + sortField;
      if (Recordset1.isOpen()) {
         Recordset1.close();
      }
      Recordset1.setSQLText(newSQL);
      Recordset1.open();
   }

Even though this example was written using JScript, it can also be done using VBScript. The following is the script block that can be used instead of the JScript script block mentioned earlier:

<SCRIPT LANGUAGE=vbscript RUNAT=Server>
   function sortAnchor(sortField,strText)
      sortAnchor="<a href=javascript:thisPage.navigate.sortTable('" & 
                   sortField & "')>" & strText & "</a>"
   end function

   sub thisPage_onenter()
      if thisPage.firstEntered then
         sortTable("au_id")
      end if
   end sub

   sub sortTable(sortField)
      newSQL = "Select * from Authors ORDER BY " & sortField
      Recordset1.close()
      Recordset1.setSQLText(newSQL)
      Recordset1.open()
   end sub
</SCRIPT>

Example 4: Changing Tables

There may be an occasion when you want to change the data being displayed by the Grid control to a totally different table. This can be done in one of two ways. You can use two grids with two different recordsets and just hide one and show the other. Or you can use the BindAllColumns method of the Grid control to reset the columns to a new table. The following example shows how to set the output of a Grid control to one table, and then, with a click of the button, the output will refresh to a new table.

To change data output between tables

Open a project in Visual InterDev 6.0.
If the project does not already have a data connection, add one for your database.
Add a new ASP page to the project.
Add a Recordset control to the page. (When prompted, say yes to enable the Scripting Object Model.)
Set the properties of the Recordset control to point to the data connection, database object, and table that you want to work with.
Add a Grid control to the page.
Right-click the Grid control, select Properties from the pop-up menu to open the properties window, and set the following properties:
- In the Data tab, set Recordset to the name of the Recordset control created in step 4.
- Select the fields you want to display from the Available fields list.
Add a Button control to the page.

Add the following script block to the page:

<SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server>
Sub Button1_onclick()
   call ChangeRS()   
End Sub

sub changeRS()
   Recordset1.close()

   ' change to a new table
   Recordset1.setSQLText( "Select * from employee")
   Recordset1.open   
   Grid1.bindAllColumns
end sub
</SCRIPT>

Edit the setSQLText line to point to the table you want to change to. (This example was created using the Authors and Employee tables in the Pubs database on SQL Server.)

Save the ASP page, and view it in your browser. When you click the button, the grid should display all the columns of your new table.

How the Example Works

This example uses two functions and three DTCs. The first function is triggered by the onClick event of the button and calls the changeRS function. The changeRS function checks if the recordset is open and closes it if it is. The setSQLText method is called to change the SQL query—in this case changing what table the query is run against. The recordset is then reopened. The final step of this function calls the bindAllColumns method of the Grid control. This instructs the grid to bind to all the columns of the new query. Without this method, the Grid control would still have the column information from the first table.

What Is the Grid Design-Time Control?

Introduction

How Do You Use the Grid DTC?

Calling Methods

Using Expressions

Selecting the Current Record

Example 1: Creating Links

Example 2: Calling Functions

Example 3: Sorting Data by Columns

How the Example Works

Example 4: Changing Tables

How the Example Works

Additional resources