Displaying Empty Grids When No Search Criteria Is Specified

Areas: ASP, Perl 

When you use the builders to create search and grid forms whereby the grid form is based on a table or a number of joined tables, the grid will initially display all the records in the table(s) when the page is first viewed. This is the default behavior which occurs since the search form has not been used to specify a search criteria yet. While this behavior is acceptable in most situations, in some cases you might want to suppress the display of any records until an search criteria is specified explicitly using the search form. In other words, if no search criteria is received, the grid form will not display any records.

In the case whereby the search form has only one search field, you can easily accomplish this goal be setting a default value for the corresponding input parameter in the datasource property of the grid form. For instance, if the grid form has an input parameter called group_id which receives the value to be used to search the group_id database field, you can set the Default Value property to a value which will result in no records being retrieved. This is because the Default Value is only used when no value is received from the search form.

In the example above, the group_id field is of type Integer so a value of -1 will suffice since the group_id field in the database contains values greater than 0. If the field in question is of another type such as text, you can set the Default Value property to a text string that will not match any database values. At all times, the value used in the Default Value property must be of the same data type as the corresponding database field value.

Note however, that the above method will only work properly when there is only one search field. In the case where you have multiple search fields, the default values would interfere with the search when just some of the fields receive a search value while others don't. The fields that don't receive a value from the search form would revert to their default values and thereby cause no records to be retrieved. As such, a different method has to be used which involves placing code in the Before Execute Select event of the grid. Basically, a condition is used to check whether the where clause is empty as a result of having received no search criteria and if it is, the code sets a where clause which will not return any records.

if (FormName.command.Where = "") then
FormName.command.Where = "FieldName = impossible_value"
end if

global $FormName;
if ($FormName->ds->Where == "")
$FormName->ds->Where = "FieldName = impossible_value";

In the above code, FormName and FieldName should be substituted with the actual names of the grid form and database field respectively while impossible value should be replaced with a value of the same data type as the field but for which there will be no matching database value.

Viewed 22840 times.   Last updated: 12/18/2002 1:27:48 PM