Grid DTC - conditionally setting color of data

[computergeek]

Hello,

Does anyone know if it is possible to conditionally set the color of data within a Grid DTC? If the data is a certain value I would like the color to change to 'red' for that field.

If you can't accomplish this within the Grid DTC itself, is it possible to write a javascript script to set the colors? (Not sure it this would be a great idea... might add overhead to loading of window.)

If it is possible...do you have a code sample?

Thanks!

 

[computergeek]

Hello,

I am still having trouble dynamically setting the color of a column within the Grid DTC. The following is the code I have embedded within the Field/Expression for the column. (Grid properties, Data tab) The syntax seems to be incorrect...any suggestions?

=if([below_safety] <> 0) + &quot;<font color = red>&quot; + [below_safety] + &quot;</font>&quot;;else + &quot;<font color=black>&quot; + [below_safety] + &quot;</font>&quot;;

Thanks in advance!

 

[gofast]

Computer Geek,

I know of two ways to set the font color when using Interdev. Perhaps easiest to accomplish your task is to use the below listed guidance. The following URL provides the information you are looking for and then some. If you cannot get the URL to work, the following is a copy of the article. I hope this helps you out.

http://msdn.microsoft.com/library/d...devprods/vs6/vinterdev/vidref/vistartpage.htm

What Is the Grid Design-Time Control?
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 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.

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, &quot;[First Name]&quot; 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 &quot;record navigation,&quot; 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 &quot;next&quot; and &quot;previous&quot; 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 &quot;anchor,&quot; 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.

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 Microsoft 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 contain the link, type the following in the Field/Expression text box:

=&quot;<a href=mailto:&quot; + [FieldName] + &quot;>&quot; + [FieldName] + &quot;</a>&quot;

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 != &quot;CA&quot{
return &quot;<FONT color=Red>&quot; + 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_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.

Add the following script block to the page:
<SCRIPT LANGUAGE=javascript RUNAT=Server>
function sortAnchor(sortField,strText){
return &quot;<a href=javascript:thisPage.navigate.sortTable('&quot; +
sortField + &quot;')>&quot; + strText + &quot;</a>&quot;;
}

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

function sortTable(sortField){
// change Authors ot your table name here:
newSQL = &quot;Select * from Authors ORDER BY &quot; + 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 sortTable to the Navigate 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 &quot;<a href=javascript:thisPage.navigate.sortTable('&quot; +
sortField + &quot;')>&quot; + strText + &quot;</a>&quot;;
}

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(&quot;au_id&quot
}
}

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 = &quot;Select * from Authors ORDER BY &quot; + 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=&quot;<a href=javascript:thisPage.navigate.sortTable('&quot; &
sortField & &quot;')>&quot; & strText & &quot;</a>&quot;
end function

sub thisPage_onenter()
if thisPage.firstEntered then
sortTable(&quot;au_id&quot
end if
end sub

sub sortTable(sortField)
newSQL = &quot;Select * from Authors ORDER BY &quot; & 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( &quot;Select * from employee&quot
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.

 

[gofast]

Computer Geek,

I know of two ways to set the font color when using Interdev. Perhaps easiest to accomplish your task is to use the below listed guidance. The following URL provides the information you are looking for and then some. If you cannot get the URL to work, the following is a copy of the article. I hope this helps you out.

http://msdn.microsoft.com/library/d...devprods/vs6/vinterdev/vidref/vistartpage.htm

What Is the Grid Design-Time Control?
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 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.

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, &quot;[First Name]&quot; 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 &quot;record navigation,&quot; 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 &quot;next&quot; and &quot;previous&quot; 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 &quot;anchor,&quot; 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.

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 Microsoft 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 contain the link, type the following in the Field/Expression text box:

=&quot;<a href=mailto:&quot; + [FieldName] + &quot;>&quot; + [FieldName] + &quot;</a>&quot;

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 != &quot;CA&quot{
return &quot;<FONT color=Red>&quot; + 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_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.

Add the following script block to the page:
<SCRIPT LANGUAGE=javascript RUNAT=Server>
function sortAnchor(sortField,strText){
return &quot;<a href=javascript:thisPage.navigate.sortTable('&quot; +
sortField + &quot;')>&quot; + strText + &quot;</a>&quot;;
}

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

function sortTable(sortField){
// change Authors ot your table name here:
newSQL = &quot;Select * from Authors ORDER BY &quot; + 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 sortTable to the Navigate 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 &quot;<a href=javascript:thisPage.navigate.sortTable('&quot; +
sortField + &quot;')>&quot; + strText + &quot;</a>&quot;;
}

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(&quot;au_id&quot
}
}

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 = &quot;Select * from Authors ORDER BY &quot; + 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=&quot;<a href=javascript:thisPage.navigate.sortTable('&quot; &
sortField & &quot;')>&quot; & strText & &quot;</a>&quot;
end function

sub thisPage_onenter()
if thisPage.firstEntered then
sortTable(&quot;au_id&quot
end if
end sub

sub sortTable(sortField)
newSQL = &quot;Select * from Authors ORDER BY &quot; & 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( &quot;Select * from employee&quot
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.

 

[MerlinB]

Your code snippet needs some adjustment to make it into an 'expression' - what you have is a rather mixed up multi-line string concatenating statement. Try

='<font color=' + (([below_safety] <> 0) ? 'red':'black') + '>' + [below_safety] + '</font>';

The trick here is to use the ?: Operator syntax (see the online help.)

You can easily make this a server-side function (in either JScript or VBScript).
Try the following JScript:


<script LANGUAGE=&quot;javascript&quot; RUNAT=&quot;SERVER&quot;>

function setFontOnException(i_iValue, i_iLimit) {

var sColour = 'black';

if (i_iValue > i_iLimit)
sColour = 'red';

return ('<font color=&quot;' + sColour + '&quot;>'
+ i_iValue + '</font>');
}

</script>


and in the grid type:

=setFontOnException([the_value], 1000);

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)