Programming instructions

122 Lesson 5 Implementing the Browsing and Maintenance Database Functions
For example, if the current tripID equals 6, the following table identifies the proper
SQL statement based on the navigation button clicked by the user:
Limiting the number of result rows
Each of the SQL statements in the previous table return a result set of trips rows. The
result set can range
from zero to any number of rows. The navigation action page must
limit the result set count to 1, since only the initial row in the result set is needed for the
Trip Detail page display.
ColdFusion provides the
maxRows attribute on the cfquery tag for this purpose. This
attribute limits the number of result rows returned from the database. To show only a
single row at a time in the Trip Detail page, set
maxRows to 1.
The navigation action page
To properly build the SQL SELECT statement for previous and next row navigation, you
must know the current
tripID. This is the reason for using the hidden input tag
RecordID on the Trip Detail page. You can then use the form variable #Form.RecordID#
in the navigation action page for building the proper test in the WHERE clause of the
SQL SELECT statement. The following code (from the navigationaction.cfm) processes
the navigation button requests on the Trip Detail page:
<!--- NAVIGATION BUTTONS --->
<cfquery name="TripQuery" dataSource="compasstravel" maxRows=1>
SELECT tripID FROM trips
<cfif IsDefined("Form.btnPrev.X")>
WHERE tripID < #Form.RecordID#
ORDER BY tripID DESC
<cfelseif IsDefined("Form.btnNext.X")>
WHERE tripID > #Form.RecordID#
ORDER BY tripID
<cfelseif IsDefined("Form.btnFirst.X")>
ORDER BY tripID
Navigation
button
SQL statement to navigate to
correct trip ID SQL statement description
First Row
SELECT tripID FROM trips
ORDER BY tripID
Returns the list of all tripIDs in
ascending (1,2,3...) order.
Previous Row
SELECT tripID FROM trips
WHERE tripID < 6
ORDER BY tripID DESC
Returns the list of all tripIDs less
than 6 in descending (5,4,3...) order.
Next Row
SELECT tripID FROM trips
WHERE tripID > 6
ORDER BY tripID
Returns the list of all tripIDs greater
than 6 in ascending (7,8,9...) order.
Last Row
SELECT tripID FROM trips
ORDER BY tripID DESC
Returns the list of all tripIDs in
descending (99,98,97...) order.