Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Next/Previous Record in database for query

Posted on 2008-11-04
6
Medium Priority
?
296 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Mamea
6 Comments
 
LVL 55

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 22883609
try something like this:


SELECT grantid,
(select top 1 p.grantid from grant p where p.grantid < grant.grantid  AND status IS NULL order by p.grantid desc) as previousid,
(select top 1 p.grantid from grant p where p.grantid > grant.grantid  AND status IS NULL order by p.grantid) as nextid
FROM grant
WHERE grantid = #url.grantid# AND status IS NULL
ORDER BY grantID DESC
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 22884266
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

0
 
LVL 63

Expert Comment

by:Zvonko
ID: 22884283
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

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 8

Expert Comment

by:eszaq
ID: 22886903
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

0
 
LVL 8

Expert Comment

by:eszaq
ID: 22886989
oops, of course for next record it would be
#evaluate(query_name.CurrentRow+1)#
0
 

Author Closing Comment

by:Mamea
ID: 31513776
Thank you! Your code worked perfectly! I didn't have to edit anything. Thank you for your quick response.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question