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:
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.
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.
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 Microsoft JScript®, 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.
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.
[ ] are placed around a field name to return the field's value (for example, [FirstName]). The value is returned as a string.See the following examples.
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 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.
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
="<a href=mailto:" + [FieldName] + ">" + [FieldName] + "</a>"
FieldName is a field containing an e-mail address.
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
=formatField([FieldName])
FieldName is the name of the field you are conditionally formatting
<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>
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.
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
=sortAnchor('field_name','display_text')
Field_name is the name of the field you are editing the header for, and Display_text is the text you want displayed in the header.
<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>
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.
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>
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
<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>
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.
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.