asked on

Next/Previous Record in database for query

Hi,

I have an access database of 250 records. I also have an admin section where the staff is viewing each record of the database. When viewing the data of the individual record, they would like to move to the next record or the previous record. The query does need to be limited by status.

<cfquery name="qry_previous" datasource="#request.dsn#" dbtype="ODBC">
SELECT grantid
FROM grant
WHERE grantid = #url.grantid# AND status IS NULL
ORDER BY grantID DESC
</cfquery>

Any suggestions? I only need to get the very next record and the very previous record.

Thanks

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zvonko

You need three queries. Not all three will give results every time e.g. when first grantid is searched.
Check this:

<cfquery name="qry_previous" datasource="#request.dsn#" dbtype="ODBC">
SELECT TOP 1 grantid FROM grant
WHERE grantid < #url.grantid# AND status IS NULL
</cfquery>
<cfquery name="qry_theone" datasource="#request.dsn#" dbtype="ODBC">
SELECT grantid FROM grant
WHERE grantid = #url.grantid# AND status IS NULL
</cfquery>
<cfquery name="qry_next" datasource="#request.dsn#" dbtype="ODBC">
SELECT TOP 1 grantid FROM grant
WHERE grantid > #url.grantid# AND status IS NULL
</cfquery>

Open in new window

Zvonko

Another way is to use min() and max() functions:

<cfquery name="qry_previous" datasource="#request.dsn#" dbtype="ODBC">
SELECT max(grantid) FROM grant
WHERE grantid < #url.grantid# AND status IS NULL
</cfquery>
<cfquery name="qry_theone" datasource="#request.dsn#" dbtype="ODBC">
SELECT grantid FROM grant
WHERE grantid = #url.grantid# AND status IS NULL
</cfquery>
<cfquery name="qry_next" datasource="#request.dsn#" dbtype="ODBC">
SELECT min(grantid) FROM grant
WHERE grantid > #url.grantid# AND status IS NULL
</cfquery>

Open in new window

eszaq

Try to use built-in cfquery variales when generating your output:
query_name.CurrentRow
query_name.RecordCount

Sorry, do not have access to CF server at the minute, so did not test. Anyway, something like this:

<cfparam url.recordnum = "1">
 
<cfoutput 
   query = "query_name"
   startRow = "#url.recordnum#"
   maxRows = "1">
   
   <cfif query_name.CurrentRow GT 1>
   <a href="?recordnum=#evaluate(query_name.CurrentRow-1)#">previous</a>
   </cfif>
   
   #query_name.grantid#
   
   <cfif query_name.CurrentRow LT query_name.RecordCount>
   <a href="?recordnum=#evaluate(query_name.CurrentRow-1)#">next</a>
   </cfif>
   
</cfoutput>

Open in new window

eszaq

oops, of course for next record it would be
#evaluate(query_name.CurrentRow+1)#

Mamea

ASKER

Thank you! Your code worked perfectly! I didn't have to edit anything. Thank you for your quick response.