Database navigation problems

Report · Dec 25, 2011

I have a problem with displaying records from my database where I wish to navigate through the records.

I use a framset where I gather the search data in a form on the left side of the page and display the database results in a frame on the right hand side of the page.

The website I have displays 1 record at a time in a table format. There's no problem getting records from the database - if I try to display 1 record on the page its Ok, if I disply X records on the page its fine - the problem comes where I display 1 record and then use hyperlinks to move to the next record in data set.

The code is set so the navigation links only appear where there is more than 1 matching record so I know I'm not trying to call records that do not exist.

The error only appears when I try to navigate through the records to display te 2nd record returned by any search, it appears to be something to do with the RecordCount.

Any help appreciated.

For Reference the next/prev record is adapated from here - http://tutorial20.easycfm.com/#

The error is:

Element RECORDCOUNT is undefined in GETDATABASESEARCHRESULT.
The error occurred in *****/testquery.cfm: line 36
34 :
35 : <CFSET end=Start + disp>
36 : <CFIF start + disp GREATER THAN GetDatabaseSearchResult.RecordCount>
37 : <CFSET end=999>
38 : <CFELSE>

The website code is:

<CFPARAM name="start" default="1" />
         
<CFPARAM name="disp" default="1" />

<CFPARAM Name="Form.Forename" Default="">
<CFPARAM Name="Form.Surname" Default="">
<CFPARAM Name="Form.Battalion" Default="">
<HTML>
<HEAD>
</HEAD>
<BODY>

<CFIF #Form.Battalion# IS "Sixth" >
<CFQUERY NAME="GetDatabaseSearchResult" DATASOURCE="durhamli_durhamli"
         CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
    SELECT Surname,Forename, Pre1917ServiceNumber, Post1917ServiceNumber, Battalion, YearOfBirth, DateEnlisted, PlaceEnlisted,     DateOverseas, Notes
    FROM SixthDLI
WHERE Forename LIKE '%#Form.Forename#%' AND Surname LIKE '%#Form.Surname#%'
</CFQUERY>
</CFIF>
<CFSET end=Start + disp>
<CFIF start + disp GREATER THAN GetDatabaseSearchResult.RecordCount>
     <CFSET end=999>
<CFELSE>
    <CFSET end=disp>
</CFIF>

<CFOUTPUT query="GetDatabaseSearchResult" startrow="#start#" maxrows="#end#">
<table border="2" cellpadding="5" cellspacing="2" width="90%" cols="5">
<th>Surname</th>
<th>Forename</th>
<th>Pre1917 No.</th>
<th>Post1917 No.</th>
<th>Battalion</th>
<tr>
<td>
#GetDatabaseSearchResult.Surname#
</td>
<td>
#GetDatabaseSearchResult.Forename#
</td>
<td>
#GetDatabaseSearchResult.Pre1917ServiceNumber#
</td>
<td>
#GetDatabaseSearchResult.Post1917ServiceNumber#
</td>
<td>
#GetDatabaseSearchResult.Battalion#   Battalion
</td>
</tr>
<th>Year Born</th>
<th>Date Enlisted</th>
<th colspan="2">Place</th>
<th>Posted Overseas</th>
<tr>
<td>
#GetDatabaseSearchResult.YearOfBirth#
</td>
<td>
#GetDatabaseSearchResult.DateEnlisted#
</td>
<td colspan="2">
#GetDatabaseSearchResult.PlaceEnlisted#
</td>
<td>
#GetDatabaseSearchResult.DateOverseas#
</td>
</tr>
<th colspan="5" align="left">Notes</th>
<tr>
<td colspan="5">
#GetDatabaseSearchResult.Notes#
</td>
<tr>
</table>
Record Number   :      #CurrentRow#   of   #GetDatabaseSearchResult.RecordCount#
</CFOUTPUT>

<CFOUTPUT>
    <table border="0" cellpadding="10">
        <tr>
            
                <CFIF start NOT EQUAL 1>
                    <CFIF start GTE disp>
                        <CFSET prev=disp />
                        <CFSET prevrec=start - disp />
                    <CFELSE>
                        <CFSET prev=start - 1 />
                        <CFSET prevrec=1 />
                    </CFIF>
                    
                    <CFIF #GetDatabaseSearchResult.RecordCount# GT 1>
                    <td><font face="wingdings">ç</font> <a href="testquery.cfm?start=#prevrec#"> Previous #prev# record</a></td>
                    <CFELSE>
                    </CFIF>
                </CFIF>
                
                <CFIF end LT GetDatabaseSearchResult.RecordCount>
                    <CFIF start + disp * 2 GTE GetDatabaseSearchResult.RecordCount>
                        <CFSET next=GetDatabaseSearchResult.RecordCount - start - disp + 1 />
                    <CFELSE>
                        <CFSET next=disp />
                    </CFIF>
                    
                    <CFIF #GetDatabaseSearchResult.RecordCount# GT 1>
                    <a href="testquery.cfm?start=#Evaluate("start + disp")#">Next record</a> <font face="wingdings"></td>
                    <CFELSE>
                    </CFIF>
               </cfif>
           </tr>
       </table>
    </CFOUTPUT>
</BODY>
</HTML>

Report · Dec 25, 2011

Your query only exists when form.battalion is "sixth". You are probably running your page when form.battalion is not "sixth".

Report · Dec 25, 2011

Thanks, you've nailed it.

I hadn't set a default parameter for form.battalion (although I presumed it would retain the parameter passed from the form to the query when I initially create the datbase query) and it now seems to work for now. It must clear the form variables every tim the page refreshes as it changes record.

I'm not sure how it'll work when I add the 4 extra tables - 'Fifth', 'Seventh', 'Eigth' and 'Ninth' if it clears the value passed from the form each time the page refreshes I suspect it's going to default to the value set on the CFPARAM tag each time but I'll deal with that at the time.

Database navigation problems

1 Correct answer