Returning Query Results to the User

When you build search interfaces, keep in mind that there won't always be a record returned. If there is at least one record returned from a query, you will usually format that data using an HTML table. But to make sure that a search has retrieved records, you will need to test if any records have been returned using the recordcount variable in a conditional logic expression in order to display search results appropriately to users.

For example, to inform the user that no records were found if the number of records returned for the GetEmployees query is 0, insert the following code before displaying the data:

<CFIF GetEmployees.RecordCount IS "0">
    No records match your search criteria. <BR>
<CFELSE>
Note To return search results to users:
  1. Return to actionpage.cfm in Studio.
  2. Add the code indicated.
    <HTML>
    <HEAD>
    <TITLE>Retrieving Employee Data Based on Criteia from Form</TITLE>
    </HEAD>
    
    <BODY>
    <CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo">
    SELECT  Departments.Department.Name,
        Employees.FirstName,
        Employees.LastName,
        Employees.StartDate,
        Employees.Salary
        FROM Departments, Employees
    WHERE Departments.Department_ID = Employees.Department_ID
        <CFIF Form.Department_Name IS NOT "">
        AND Departments.Department_Name = 'Form.Department_Name'
    </CFQUERY>
    <H4>Employee Data Based on Criteia from Form</H4>
    <CFIF GetEmployees.RecordCount IS "0">
    No records match your search criteria. <br>
    Please go back to the form and try again.
    <CFELSE>
    <TABLE>
    <TR>
        <TH>First Name</TH>
        <TH>Last Name</TH>
        <TH>Salary</TH>
    </TR>
    <CFOUTPUT QUERY="GetEmployees">
    <TR>
        <TD>#FirstName#</TD>
        <TD>#LastName#</TD>
        <TD>#Salary#</TD>
    </TR>
    </CFOUTPUT>
    </TABLE>
    </BODY>
    </HTML>
    
  3. Save the file.
  4. Return to the form, enter search criteria and submit the form.
  5. If no records match the criteria you specified, the message displays.