sorting by column header and go to next page and back page or to page number

Hi jedrtd!

Try to evaluate the sample code below :

<cfparam name="NewQS" default="#CGI.QUERY_STRING#">
<cfparam name="page" default="1">

<cfif ListContainsNoCase(CGI.QUERY_STRING,"orderby","&")>
  <cfset NewQS = ListDeleteAt(CGI.QUERY_STRING,ListContainsNoCase(CGI.QUERY_STRING, "orderby","&"),"&")>
  <cfif ListContainsNoCase(NewQS,"page","&")>
    <cfset NewQS = ListDeleteAt(NewQS,ListContainsNoCase(NewQS, "page","&"),"&")>
  </cfif>
</cfif>

<cfif Len(NewQS)>
  <cfset NewQS = '#NewQS#&'>
</cfif>

<cfset PrevPage = page - 1>
<cfset NextPage = page + 1>
<cfif PrevPage eq 0>
  <cfset PrevPage = 1>
</cfif>

<cfoutput>
  <a href="?#NewQS#orderby=column1">Header1</a><br>
  <a href="?#NewQS#orderby=column2">Header2</a><br>
  <a href="?#NewQS#orderby=column3">Header3</a><br>
  <br><br>
  <a href="?#NewQS#orderby=#URL.orderby#&page=#PrevPage#">< Previous</a>&nbsp;&nbsp;<a href="?#NewQS#orderby=#URL.orderby#&page=#NextPage#">Next ></a><br>
</cfoutput>

If you want to have some explanations, just let me know.

Hope this makes sense.


Regards!
eNTRANCE2002 :-)

I'm going to post my code,  this is working but what I did was set the showall=yes when you do the sort,  this is slow and not great for printing just a page.   The filter are not going to be hidden when I'm done,  they come from the summary page.  

Thanks

<!---
  Filename:     .cfm
  Created by:  
  Purpose:     Detail on Profit and cost
  Please Note:  Includes NextNIncludeBackNext.cfm and NextNIncludePageLinks.cfm
  This is being use without FIlter to show detail for Profit summary page (this is the job costing summary page templet)
--->

<!--- Maintain summary filtering variables at session level --->
<CFPARAM NAME="session.cust_summary1.UserFilter" TYPE="string" DEFAULT="">
<CFPARAM NAME="session.cust_summary1.DateFrom" TYPE="string" DEFAULT="">
<CFPARAM NAME="session.cust_summary1.DateThru" TYPE="string" DEFAULT="">
<CFPARAM NAME="session.cust_summary1.Rank_By" TYPE="string" DEFAULT="">
<CFPARAM NAME="session.cust_summary1.Rank_Order" TYPE="string" DEFAULT="">
<CFPARAM NAME="SESSION.Rank_By" TYPE="string" DEFAULT="">
<CFPARAM NAME="SESSION.Rank_Order" TYPE="string" DEFAULT="">
<CFPARAM NAME="SESSION.recordidin" default="">
<CFPARAM NAME="url.recordidin" default="">
<CFPARAM Name="session.orderbytype" default="">
<CFPARAM NAME="session.orderby" default="">

<cfif isdefined("form.debtor_acct_no")>
      <cfset session.recordidin = trim(form.debtor_acct_no)>
<cfelseif isdefined("url.debtor_acct_no")>
      <cfset session.recordidin = trim(url.debtor_acct_no)>
</cfif>

<cfif not isdefined("url.orderbytype")>
     <cfset url.orderbytype="ASC">
</cfif>
<cfif not isdefined("url.orderby")>
     <cfset url.orderby="profit">
</cfif>

<!--- set return to summary page 0--->
<cfset list_page="customer_summary.cfm">
<!--- Retrieve expense records from database --->
<CFQUERY NAME="Summary" DATASOURCE="Printstream" CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
SELECT JOB_NO, BILL_COMPANY, Revenue, COST, Profit, SALESREP, DEBTOR_ACCT_NO,
Margin FROM JOB_SUMMARY WHERE debtor_acct_no='#session.recordidin#' AND Invoice_DATE IS NOT NULL AND PROFIT <> 0
<!--- If the user provided a filter string, --->
<!--- show only matching films and/or expenses --->

<CFIF session.cust_summary1.UserFilter IS NOT "">
  AND (BILL_COMPANY LIKE '%#session.cust_summary1.UserFilter#%' OR JOB_NO LIKE '%#session.cust_summary1.UserFilter#%'
  OR SALESREP LIKE '%#session.cust_summary1.UserFilter#%' OR DEBTOR_ACCT_NO LIKE '%#session.cust_summary1.UserFilter#%'
  OR MARGIN LIKE '%#session.cust_summary1.UserFilter#%')
</CFIF>
<!--- Also filter on From date, if provided --->
<CFIF IsDate(session.cust_summary1.DateFrom)>
  AND INVOICE_DATE >= #CreateODBCDate(session.cust_summary1.DateFrom)#
</CFIF>
<!--- Also filter on Through date, if provided --->
<CFIF IsDate(session.cust_summary1.DateThru)>
  AND INVOICE_DATE <= #CreateODBCDate(session.cust_summary1.DateThru)#
</CFIF>
<cfif session.cust_summary1.Rank_By IS "">
  <cfset session.cust_summary1.Rank_By = "PROFIT">
</cfif>
<cfif session.cust_summary1.Rank_Order IS "">
  <cfset session.cust_summary1.Rank_Order = "ASC">
</cfif>
ORDER BY #url.orderby# #url.orderbytype#
<!--- ORDER BY Variance ASC --->
</CFQUERY>
<cfquery dbtype="query" name="SummarySum" >
SELECT sum(REVENUE) AS TotalRev, sum(Cost) AS TotalCost, sum(PROFIT) AS TotalProfit, ((sum(Profit)/sum(Revenue))*100) AS TotalMargin FROM Summary
where debtor_acct_no='#session.recordidin#'
</cfquery>

<!--- Number of rows to display per Next/Back page  --->
<CFSET RowsPerPage = 20>
<!--- What row to start at? Assume first by default --->
<CFPARAM NAME="URL.StartRow" DEFAULT="1" TYPE="numeric">
<!--- Allow for Show All parameter in the URL --->
<CFPARAM NAME="URL.ShowAll" TYPE="boolean" DEFAULT="No">

<!--- We know the total number of rows from query   --->
<CFSET TotalRows = summary.RecordCount>
<!--- Show all on page if ShowAll passed in URL   --->
<CFIF URL.ShowAll>
  <CFSET RowsPerPage = TotalRows>
</CFIF>
<!--- Last row is 10 rows past the starting row, or --->
<!--- total number of query rows, whichever is less --->
<CFSET EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
<!--- Next button goes to 1 past current end row  --->
<CFSET StartRowNext = EndRow + 1>
<!--- Back button goes back N rows from start row --->
<CFSET StartRowBack = URL.StartRow - RowsPerPage>


<!--- Page Title --->
<HTML>
<HEAD>
<TITLE>Customer Profit detail</TITLE>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</HEAD>
<SCRIPT LANGUAGE="JavaScript">
<!--
var browser=navigator.appName + " " + navigator.appVersion;

function calendar1()
   {
      var newWind=window.open('calendar1.cfm','remote','width=350,height=230');
      if (newWind.opener == null)
         { newWind.opener = window; }

}
function calendar2()
   {
      var newWind=window.open('calendar2.cfm','remote','width=350,height=230');
      if (newWind.opener == null)
         { newWind.opener = window; }

}
//-->
</SCRIPT>
<BODY background="../ABRLaborCost/Images/abrbacklogogreen.jpg" link="#0000FF" vlink="#0000FF" alink="#0000FF">
<CFOUTPUT>
  <H2 align="center"><font color="##000099">ABR Services, Inc Details for Sales
    and Profit</font></H2>
</CFOUTPUT>
<!--- Simple style sheet for formatting --->
<STYLE>
  FORM     {font-family:sans-serif;font-size:smaller;}
  TH       {font-family:sans-serif;font-size:smaller;
            background:orange;color:white}
  TD       {font-family:sans-serif;font-size:smaller}
  TD.DataA {background:silver;color:black}
  TD.DataB {background:lightgrey;color:black}
</STYLE>
<!--- Simple form to allow user to filter results --->
<CFOUTPUT>
  <H2 align="center">
    <!--- Simple style sheet for formatting --->
    <STYLE>
  FORM     {font-family:sans-serif;font-size:smaller;}
  TH       {font-family:sans-serif;font-size:smaller;
            background:orange;color:white}
  TD       {font-family:sans-serif;font-size:smaller}
  TD.DataA {background:silver;color:black}
  TD.DataB {background:lightgrey;color:black}
</STYLE>
    <!--- Simple form to allow user to filter results --->
  </H2>
</CFOUTPUT>
<div align="center"></div>
<CFFORM ACTION="#CGI.SCRIPT_NAME#" METHOD="POST" name="myform">
  <div align="center"></div>
  <table width="80%" border="2" align="center" cellpadding="1" cellspacing="2" bordercolor="#000099" bgcolor="#CCCCCC">
    <tr>
      <td colspan="2"> <div align="center"><strong><font size="-1">FILTER JOB
          LIST WITH SELECTIONS BELOW AND CLICK APPLY:</font></strong></div></td>
    </tr>
    <tr valign="middle">
      <td bordercolor="#CCCCCC"> &nbsp;&nbsp;<strong><b>&nbsp;&nbsp;&nbsp;Rank
        By</b>:</strong> <select name="Rank_By" id="select6">
          <option value="PROFIT">Profit</option>
          <option value="Job_No">Job Number</option>
          <option value="REVENUE">Revenue</option>
          <option value="Cost">Cost</option>
          <option value="Margin">Margin</option>
        </select> &nbsp;&nbsp;&nbsp;&nbsp;<strong>Order:</strong> <select name="Rank_Order" id="select7">
          <option value="ASC">Ascending</option>
          <option value="DESC">Descending</option>
        </select></td>
      <td nowrap bordercolor="#CCCCCC"><b>
        <!--- Filter string --->
        Filter:</b> <cfinput    name="UserFilter"    type="Text"
    value="#session.cust_summary1.UserFilter#"
    size="25"></td>
    </tr>
    <tr valign="middle">
      <td nowrap bordercolor="#CCCCCC">&nbsp; <b>
        <!--- From date --->
        &nbsp;&nbsp;&nbsp;Dates:</b> From
        <cfinput    name="DateFrom"    type="Text"
    value="#session.cust_summary1.DateFrom#"
    size="9"
    message="Please enter a valid date, or leave it blank."
    validate="date"> <a href="javascript:calendar1()"><img src="file:///C|/CFusionMX/wwwroot/Printstream/Job_Rank/calendarpage.jpg" width="18" height="19" border="0"></a>
        <!--- Through date --->
        Through
        <cfinput    name="DateThru"    type="Text"
    value="#session.cust_summary1.DateThru#"
    size="9"
    message="Please enter a valid date, or leave it blank."
    validate="date"> <a href="javascript:calendar2()"><img src="file:///C|/CFusionMX/wwwroot/Printstream/Job_Rank/calendarpage.jpg" width="18" height="19" border="0"></a>
      </td>
      <td width="20%" nowrap bordercolor="#CCCCCC"> <div align="center">
          <!--- Submit button to activate/change/clear filter --->
          <input name="Submit"
    type="Submit"
    value="Apply">
        </div></td>
    </tr>
  </table>
</CFFORM>
<CFFORM ACTION="#CGI.SCRIPT_NAME#" METHOD="POST" name="myform">
  <table width="100%" border="0" align="center" cellpadding="1" cellspacing="0">
    <!--DWLayoutTable-->
    <!--- Row at top of table, above column headers --->
    <tr>
      <td height="53" colspan="2">
        <!--- Message about which rows are being displayed --->
        <cfoutput> Displaying <b>#URL.StartRow#</b> to <b>#EndRow#</b> of <b>#TotalRows#</b> Records <a href=#list_page#>[Link
          to Profit Summary Page]</a><br>
        </cfoutput> </td>
      <td colspan="5" align="right" valign="bottom"> <cfif NOT URL.ShowAll>
          <!--- Provide Next/Back links --->
          <cfinclude template="../Job_Rank/NextNIncludeBackNext.cfm">
        </cfif> </td>
    </tr>
    <!--- Row for column headers --->
    <tr>
      <th height="48" colspan="2" valign="middle"> <div align="right">Totals For
          Selection&nbsp;&nbsp;</div></th>
      <th width="176" valign="middle"><!--DWLayoutEmptyCell-->&nbsp;</th>
      <th width="200"> <div align="right"><cfoutput>#Dollarformat(SummarySum.TotalRev)#</cfoutput>&nbsp;</div></th>
      <th width="200"> <div align="right">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<cfoutput>#Dollarformat(SummarySum.TotalCost)#</cfoutput>&nbsp;</div></th>
      <th width="200"> <div align="right"><cfoutput>#Dollarformat(SummarySum.TotalProfit)#</cfoutput>&nbsp;</div></th>
      <th width="200"> <div align="left"><cfoutput>
            <div align="right">#LSNumberFormat(SummarySum.TotalMargin,',99999999999999.9')# %</div>
          </cfoutput></div>
        <div align="right"></div></th>
    </tr>
    <tr>
      <th width="155" height="25"><div align="left">Salesrep</div></th>
      <th><div align="left"></div>
        <div align="left">Customer</div></th>
      <th> <div align="left"><A href="customer_profit_detail.cfm?&orderby=job_no<cfif url.orderbytype eq "ASC">&orderbytype=DESC</cfif>&showall=yes">Job No</a></div></th>
      <th><div align="right">Revenue</div></th>
      <th><div align="right">Cost</div></th>
      <th><div align="right">Profit&nbsp;</div></th>
      <th> <div align="right">Margin</div></th>
    </tr>
    <!--- For each query row that should be shown now --->
    <cfloop query="summary" startrow="#URL.StartRow#" endrow="#EndRow#">
      <!--- Use class "DataA" or "DataB" for alternate rows --->
      <cfset Class = IIF(summary.CurrentRow MOD 2 EQ 0, "'DataA'", "'DataB'")>
      <cfoutput>
        <tr valign="baseline">
          <td height="24" nowrap class="#Class#"> <div align="justify"> #Summary.SALESREP#</div></td>
          <td nowrap class="#Class#"> <div align="justify"> &nbsp;#Summary.DEBTOR_ACCT_NO#&nbsp; #Summary.BILL_COMPANY#</div></td>
          <td class="#Class#"> <div align="justify">
              <div align="center">
                <div align="left"> <a href="../Detail.cfm?recordID=#Summary.JOB_NO#">#Summary.JOB_NO#</a></div>
              </div>
            </div></td>
          <td class="#Class#"> <div align="right"><i>#DollarFormat(Summary.REVENUE)#</i></div></td>
          <td class="#Class#"> <div align="right">#DollarFormat(Summary.COST)#</div></td>
          <td class="#Class#"> <div align="right">#DollarFormat(Summary.PROFIT)#</div></td>
          <td class="#Class#"> <div align="right">#LSNumberFormat(Summary.Margin,',99999999999999.9')# %</div></td>
        </tr>
      </cfoutput>
    </cfloop>
    <!--- Row at bottom of table, after rows of data --->
    <tr>
      <td height="70" colspan="5"> <cfif NOT URL.ShowAll AND TotalRows GT RowsPerPage>
          <!--- Shortcut links for "Pages" of search results --->
          Page
          <cfinclude template="../Job_Rank/NextNIncludePageLinks.cfm">
             
          <!--- Show All link --->
          <cfoutput> <a href="#CGI.SCRIPT_NAME#?&ShowAll=Yes">Show
            All</a> </cfoutput> </cfif>
        <!--- <CFIF NOT URL.ShowAll>
        <!--- Provide Next/Back links --->
        <cfinclude template="../Job_Rank/NextNIncludeBackNext.cfm"> </td>
      --->
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
    </tr>
    <tr>
      <td height="3"></td>
      <td width="537"></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
    </tr>
  </table>
  <div align="center"><strong><B>&nbsp;&nbsp;&nbsp;&nbsp;</B></strong> &nbsp;&nbsp;&nbsp;&nbsp;<strong>
    </strong> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  </div>
</CFFORM>
</BODY>
</HTML>

The problem I see is that you are reading from the session variables, but I don't see where you are setting them.  I see a few defaults being set, but no user specific values being set....

I tried doing an isdefined for the orderby and orderbytype Url variable,  and then set the session variable to the url variable,  but it did not work.   then I tried using the form.rank_by and setting it to the URL variable,  still no go.    I'm thinking it has something to do with the Includes NextNIncludeBackNext.cfm and NextNIncludePageLinks.cfm,  I figure I have to pass the session or url to them.  But I just can not figure out how.    .

Well if you are storing in the session, then no - no need to pass.

If you are storing in the URL then include like this:

NextNIncludeBackNext.cfm?#CGI.Query_String#

I am not sure how you would do this for your templates (<cfinclude template="../Job_Rank/NextNIncludeBackNext.cfm">)

You basically need to get the querystring inthe actual next and back links that people are clicking.

Maybe you could put the querystring directly in the template?  Not 100% sure ....

I just have not used Coldfusion enough to know how to do that.  Any suggestion  

Well what does the template itself look like?

here is the nextnincludebacknext.cfm from one of the cfinclude

<!---
  Filename:     NextNIncludeBackNext.cfm
  Created by:   Nate Weiss (NMW)
  Purpose:      Displays Back and Next links for record navigation
  Please Note:  Included by the NextN.cfm templates in this folder
--->

<!--- Provide Next/Back links --->
<CFOUTPUT>
  <!--- Show link for Back, if appropriate --->
  <CFIF StartRowBack GT 0>
    <A HREF="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack#">
      <IMG SRC="BrowseBack.gif" WIDTH="40" HEIGHT="16"
        ALT="Back #RowsPerPage# Records" BORDER="0"></A>
  </CFIF>
  <!--- Show link for Next, if appropriate --->
  <CFIF StartRowNext LTE TotalRows>
    <A HREF="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext#">
      <IMG SRC="BrowseNext.gif" WIDTH="40" HEIGHT="16"
        ALT="Next #RowsPerPage# Records" BORDER="0"></A>
  </CFIF>
</CFOUTPUT>



As you can see I got this from somewhere on the net

thanks
Janet

Okay then what you need to modify to is:

<!---
  Filename:     NextNIncludeBackNext.cfm
  Created by:   Nate Weiss (NMW)
  Purpose:      Displays Back and Next links for record navigation
  Please Note:  Included by the NextN.cfm templates in this folder
--->

<!--- Provide Next/Back links --->
<CFOUTPUT>
  <!--- Show link for Back, if appropriate --->
  <CFIF StartRowBack GT 0>
    <A HREF="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack#&#CGI.QUERY_STRING#">
      <IMG SRC="BrowseBack.gif" WIDTH="40" HEIGHT="16"
        ALT="Back #RowsPerPage# Records" BORDER="0"></A>
  </CFIF>
  <!--- Show link for Next, if appropriate --->
  <CFIF StartRowNext LTE TotalRows>
    <A HREF="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext#&#CGI.QUERY_STRING#">
      <IMG SRC="BrowseNext.gif" WIDTH="40" HEIGHT="16"
        ALT="Next #RowsPerPage# Records" BORDER="0"></A>
  </CFIF>
</CFOUTPUT>

I think we almost have it,   when I click on next or page the first time it go to the correct page,  but after that I get this,  I had my debug on so I just copie it all

Invalid parameter type.  
The value cannot be converted to a numeric because it is not a simple value.Simple values are booleans, numbers, strings, and date-time values.  
 
The Error Occurred in D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm: line 75
 
73 : <!--- What row to start at? Assume first by default --->
74 : <CFPARAM NAME="URL.StartRow" DEFAULT="1" TYPE="numeric">
75 : <!--- Allow for Show All parameter in the URL --->
76 : <CFPARAM NAME="URL.ShowAll" TYPE="boolean" DEFAULT="No">
77 :

 

--------------------------------------------------------------------------------
 
Please Try The Following:

Check the CFML Reference Manual to verify that you are using the correct syntax.

Search the Knowledge Base to find a solution to your problem.

 
Browser   Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; T312461; SV1; .NET CLR 1.1.4322)
Remote Address   192.168.0.100
Referer   http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?StartRow=21&&orderby=job_no&orderbytype=DESC 
Date/Time   05-May-05 01:28 PM
 
Stack Trace (click to expand)  
at cfCustomer_profit_detail2ecfm583600227.runPage(D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm:75)


coldfusion.tagext.lang.ParamTag$InvalidParamTypeException: Invalid parameter type.
      at coldfusion.tagext.lang.ParamTag.checkType(Unknown Source)
      at coldfusion.tagext.lang.ParamTag.doStartTag(Unknown Source)
      at coldfusion.runtime.CfJspPage._emptyTag(Unknown Source)
      at cfCustomer_profit_detail2ecfm583600227.runPage(D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm:75)
      at coldfusion.runtime.CfJspPage.invoke(Unknown Source)
      at coldfusion.tagext.lang.IncludeTag.doStartTag(Unknown Source)
      at coldfusion.filter.CfincludeFilter.invoke(Unknown Source)
      at coldfusion.filter.ApplicationFilter.invoke(Unknown Source)
      at coldfusion.filter.PathFilter.invoke(Unknown Source)
      at coldfusion.filter.ExceptionFilter.invoke(Unknown Source)
      at coldfusion.filter.BrowserDebugFilter.invoke(Unknown Source)
      at coldfusion.filter.ClientScopePersistenceFilter.invoke(Unknown Source)
      at coldfusion.filter.BrowserFilter.invoke(Unknown Source)
      at coldfusion.filter.GlobalsFilter.invoke(Unknown Source)
      at coldfusion.filter.DatasourceFilter.invoke(Unknown Source)
      at coldfusion.CfmServlet.service(Unknown Source)
      at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
      at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
      at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:241)
      at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
      at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:198)
      at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348)
      at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451)
      at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294)
      at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

 
 








--------------------------------------------------------------------------------
Debugging Information ColdFusion Server Professional 6,0,0,48097
Template  /printstreamtest/Profit_By_Customer/customer_profit_detail.cfm
Time Stamp  05-May-05 01:28 PM
Locale  English (US)
User Agent  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; T312461; SV1; .NET CLR 1.1.4322)
Remote IP  192.168.0.100
Host Name  192.168.0.100




--------------------------------------------------------------------------------
Execution Time

Total Time Avg Time Count Template
32 ms 32 ms 1  D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm
0 ms 0 ms 1 D:\wwwroot\PrintStreamTest\Profit_By_Customer\Application.cfm
46 ms   STARTUP, PARSING, COMPILING, LOADING, & SHUTDOWN
78 ms   TOTAL EXECUTION TIME
red = over 250 ms average execution time


--------------------------------------------------------------------------------
Exceptions

13:28:56.056 - Expression Exception - in D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm : line 75
    Invalid parameter type.
   


--------------------------------------------------------------------------------
SQL Queries

Summary (Datasource=Printstreamtest, Time=16ms, Records=1941, Cached Query) in D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm @ 13:28:56.056

SELECT JOB_NO, BILL_COMPANY, Revenue, COST, Profit, SALESREP, DEBTOR_ACCT_NO,
Margin FROM JOB_SUMMARY WHERE debtor_acct_no='2779' AND Invoice_DATE IS NOT NULL AND PROFIT <> 0



 
ORDER BY job_no DESC
SummarySum (Datasource=, Time=16ms, Records=1) in D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm @ 13:28:56.056

SELECT sum(REVENUE) AS TotalRev, sum(Cost) AS TotalCost, sum(PROFIT) AS TotalProfit, ((sum(Profit)/sum(Revenue))*100) AS TotalMargin FROM Summary
where debtor_acct_no='2779'



--------------------------------------------------------------------------------
Scope Variables

CGI Variables:

SCRIPT_NAME=/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm HTTPS_KEYSIZE= CERT_KEYSIZE= AUTH_USER= SERVER_NAME=192.168.0.102 REQUEST_METHOD=GET AUTH_PASSWORD= AUTH_TYPE= HTTPS_SECRETKEYSIZE= CERT_SERVER_SUBJECT= CERT_SERIALNUMBER= PATH_INFO=/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm HTTP_COOKIE=CFID=9409; CFTOKEN=21610010; JSESSIONID=8030858671115313779285 SERVER_PORT=80 REMOTE_USER= QUERY_STRING=StartRow=41&StartRow=21&&orderby=job_no&orderbytype=DESC CERT_SERVER_ISSUER= CERT_SECRETKEYSIZE= HTTP_REFERER=http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?StartRow=21&&orderby=job_no&orderbytype=DESC HTTP_USER_AGENT=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; T312461; SV1; .NET CLR 1.1.4322) CERT_ISSUER= HTTP_CONNECTION=Keep-Alive REMOTE_ADDR=192.168.0.100 HTTPS_SERVER_SUBJECT= CF_TEMPLATE_PATH=D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm HTTP_HOST=192.168.0.102 SERVER_PROTOCOL=HTTP/1.1 HTTP_ACCEPT_ENCODING=gzip, deflate GATEWAY_INTERFACE=CGI/1.1 CONTENT_LENGTH=0 CERT_FLAGS= CERT_SUBJECT= REMOTE_HOST=192.168.0.100 SERVER_SOFTWARE=Microsoft-IIS/5.0 SERVER_PORT_SECURE=0 HTTP_ACCEPT_LANGUAGE=en-us HTTP_ACCEPT=image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/x-shockwave-flash, application/vnd.ms-powerpoint, application/vnd.ms-excel, application/msword, */* WEB_SERVER_API= HTTPS_SERVER_ISSUER= CERT_COOKIE= CONTENT_TYPE= HTTPS=off PATH_TRANSLATED=d:\wwwroot\printstreamtest\Profit_By_Customer\customer_profit_detail.cfm

Cookie Variables:

CFID=9409 CFTOKEN=21610010 JSESSIONID=8030858671115313779285

Session Variables:

urltoken=CFID=9409&CFTOKEN=21610010&jsessionid=8030858671115313779285 sessionid=8030858671115313779285 rank_order= recordid= rank_by= cust_summary1=Struct (5) orderby= recordidin=2779 orderbytype=

URL Parameters:

ORDERBYTYPE=DESC ORDERBY=job_no STARTROW=41,21

Debug Rendering Time: 32 ms
 

Can you mouseover the link (or in Firefox do right-click COpy Link Location) and show us the link that is being created for the next or back link that you clicked when this error happened?

HTTP://192.168.102/printstreamtest/profitby_customer/customer_profit_detail.CFM?startrow=21&orderby=job_no  this is the 1st time

and when I go to next again the link is the same

If I try to use the back botton it show only my header info

FYI,   I did some testing and this error is happening now on any templete using the next-back.  templetes

Janet

One thing I see is the double && in the error message link between startrow and orderby

The && is  there somtimes when the error occurs but not everytime,  I'm sure it should not be there,  I looked at the next and page where I added the Cgi.query_string looks ok.  Looked where the pagenumber and back and next are on the main page  looks ok.  

I am not sure as to why sometimes it works nd other times it does not.

This has me puzzled.

Can you consistently get it to crash?  If so please say the exact steps

i.e. Inital page loads - records 1-10 shown - sorted by ____, I click Next, page loads - records 11- 20 shown sorted by ____, I click Next, page crashes

Yes it will crash on the 2nd click of next, page number and back is just strange.  

page load  records 1-20 by default sort,  click next startrow=21&debtor_acct_no=3562,  click next again crash startrow=411&startrow=41&debtor_acct_no=3568

Page load records 1-20 by default sort , click next ok startrow=21&debtor_acct_no=3562,  click back again does not crash but it is only showing header info no database info
         startrow38353&starrow=1&startrow=21&debtor_acct_no,  If I click back again no info and the 1st startrow gets bigger,  if I then click next or select a page number it  crashes.  

When I sort by colum Job_NO ok acs/desc http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?&orderby=job_no
     click next ok http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?StartRow=21&&orderby=job_no
     Click next crash   http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?StartRow=41&StartRow=21&&orderby=job_no

sort by colum Job_no
     click page 3 ok,  http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?StartRow=41&&orderby=job_no&orderbytype=DESC
     click back  Crash   http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?StartRow=21&StartRow=41&&orderby=job_no&orderbytype=DESC
   

ok if you need more let me know.  

Okay I see the problem. (or at least the first one)

startrow was already in the URL and it is getting added a second time.

This is due to the code I had you add.

We need to modify it slightly like so:

<!---
  Filename:     NextNIncludeBackNext.cfm
  Created by:   Nate Weiss (NMW)
  Purpose:      Displays Back and Next links for record navigation
  Please Note:  Included by the NextN.cfm templates in this folder
--->

<cfset modifiedQueryString = "">
<cfloop list="#CGI.Query_String#" index="namevaluepair" delimeters="&">
<cfif ListFirst(namevaluepair, "=") NEQ "StartRow">
    <cfset modifiedQueryString = modifiedQueryString  + "&" + namevaluepair>
</cfif>
</cfloop>

<!--- Provide Next/Back links --->
<CFOUTPUT>
  <!--- Show link for Back, if appropriate --->
  <CFIF StartRowBack GT 0>
    <A HREF="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack##modifiedQueryString#">
      <IMG SRC="BrowseBack.gif" WIDTH="40" HEIGHT="16"
        ALT="Back #RowsPerPage# Records" BORDER="0"></A>
  </CFIF>
  <!--- Show link for Next, if appropriate --->
  <CFIF StartRowNext LTE TotalRows>
    <A HREF="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext##modifiedQueryString #">
      <IMG SRC="BrowseNext.gif" WIDTH="40" HEIGHT="16"
        ALT="Next #RowsPerPage# Records" BORDER="0"></A>
  </CFIF>
</CFOUTPUT>


Hopefully that should take care of that problem....

I made the change,  when I click the next or page I get this error

Attribute validation error for tag CFLOOP.  
The tag does not allow the attribute(s) DELIMETERS. The valid attribute(s) are COLLECTION,CONDITION,DELIMITERS,ENDROW,FROM,INDEX,ITEM,LIST,QUERY,STARTROW,STEP,TO.  
 
The Error Occurred in D:\wwwroot\PrintStreamTest\Profit_By_Customer\NextNIncludeBackNext.cfm: line 11
 
9 :
10 : <cfset modifiedQueryString = "">
11 : <cfloop list="#CGI.Query_String#" index="namevaluepair" delimeters="&">
12 : <cfif ListFirst(namevaluepair, "=") NEQ "StartRow">
13 :     <cfset modifiedQueryString = modifiedQueryString  + "&" + namevaluepair>

 

oh wait I just saw that delimited was spelled wrong,  I will make change and try again

duh

ok it is working as long as I don't go to the detail page,  also I have to make the same change to the page part,  I will try and do that myself,  the error and problem are

when I drill down to the detail page this error is display, I'm guessing it is not setting the namevaluepair but that is just a guess on my part


The value "" cannot be converted to a number  
 
 
The Error Occurred in D:\wwwroot\PrintStreamTest\Profit_By_Customer\NextNIncludeBackNext.cfm: line 13
 
11 : <cfloop list="#CGI.Query_String#" index="namevaluepair" delimiters="&">
12 : <cfif ListFirst(namevaluepair, "=") NEQ "StartRow">
13 :     <cfset modifiedQueryString = modifiedQueryString  + "&" + namevaluepair>
14 : </cfif>
15 : </cfloop>

 

--------------------------------------------------------------------------------
 
Please Try The Following:

Check the CFML Reference Manual to verify that you are using the correct syntax.

Search the Knowledge Base to find a solution to your problem.

 
Browser   Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; T312461; SV1; .NET CLR 1.1.4322)
Remote Address   192.168.0.100
Referer   http://192.168.0.102/printstreamtest/Profit_By_Customer/Customer_Summary.cfm?StartRow=1 
Date/Time   05-May-05 08:12 PM
 
Stack Trace (click to expand)  
at cfNextNIncludeBackNext2ecfm480860160.runPage(D:\wwwroot\PrintStreamTest\Profit_By_Customer\NextNIncludeBackNext.cfm:13) at cfCustomer_profit_detail2ecfm583600227.runPage(D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm:212)


coldfusion.runtime.Cast$NumberConversionException: The value "" cannot be converted to a number
      at coldfusion.runtime.Cast._double(Unknown Source)
      at coldfusion.runtime.Cast._double(Unknown Source)
      at cfNextNIncludeBackNext2ecfm480860160.runPage(D:\wwwroot\PrintStreamTest\Profit_By_Customer\NextNIncludeBackNext.cfm:13)
      at coldfusion.runtime.CfJspPage.invoke(Unknown Source)
      at coldfusion.tagext.lang.IncludeTag.doStartTag(Unknown Source)
      at coldfusion.runtime.CfJspPage._emptyTag(Unknown Source)
      at cfCustomer_profit_detail2ecfm583600227.runPage(D:\wwwroot\PrintStreamTest\Profit_By_Customer\Customer_profit_detail.cfm:212)
      at coldfusion.runtime.CfJspPage.invoke(Unknown Source)
      at coldfusion.tagext.lang.IncludeTag.doStartTag(Unknown Source)
      at coldfusion.filter.CfincludeFilter.invoke(Unknown Source)
      at coldfusion.filter.ApplicationFilter.invoke(Unknown Source)
      at coldfusion.filter.PathFilter.invoke(Unknown Source)
      at coldfusion.filter.ExceptionFilter.invoke(Unknown Source)
      at coldfusion.filter.BrowserDebugFilter.invoke(Unknown Source)
      at coldfusion.filter.ClientScopePersistenceFilter.invoke(Unknown Source)
      at coldfusion.filter.BrowserFilter.invoke(Unknown Source)
      at coldfusion.filter.GlobalsFilter.invoke(Unknown Source)
      at coldfusion.filter.DatasourceFilter.invoke(Unknown Source)
      at coldfusion.CfmServlet.service(Unknown Source)
      at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
      at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
      at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:241)
      at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
      at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:198)
      at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348)
      at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451)
      at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294)
      at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

 
 

I did some testing this morning,   it must be because on the detail page I pass the debtor_acct_number to so Im guessing that is throwing the query from the CGI script.  I tested by removing the url.recordidin and it run good.   But I'm not sure how to fix it.

Just a guess but should you even have the next/back record navigation on the detail page at all?

I need it because this is a list of job's that make up summary page,  I can have 1000 jobs.  So the next/back needs to be used,    The Next/Back was working fine on all the page,  it just was not working
when I tired to do the column header sort asc/desc.    Maybe I will have to use a diff nextback on the detail page with the extra url.   Not sure

Yes most likely since you need the sort on the main page and no sort on the details page since you are showing only 1 record.

I'm new with cgi so can you tell me what each part that you added is,  and do you know what I would need to add to make the detail page work,  because I'm also going to be adding the
orderby and asc or dsce when I sort by the column header,  I figure that will crash it also.    I just never figure it would be this hard to get it to work.  Is there a better way?  

Hard to tell without seeing a working example of what youa re doing.

As for CGI maybe this will help:

CGI Environment variables:
http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/Expressions5.htm

Exceprt:
"When a browser makes a request to a server, the web server and the browser create environment variables. In ColdFusion, these variables are referred to as CGI environment variables. They take the CGI prefix regardless of whether the server uses a server API or CGI to communicate with the ColdFusion Server....."

It also contains a description of all of the ones available and what they are.

ok let me ask you,  if you had to do this,   how would you do it.    this is next/prev and page with column header sorts?


janet

Again - it is hard to say without seeing a working example of what you are doing.

In general if you are paging through records you have

1) SUmmary page with a listing of all records - this page allows sorting and next/previous navigation along with clicking on a specific record to see details

2) Detail page that shows one specific record.

I don't know if you are doing this or something in between....

basicly yes, but in my case the summary page list all customer then the detail page list all job that we have for that customer(could be up to 1000).   So that is why I have to do it on both pages
The customer ID is passes to the detail page as a URL.variable.  I could send you a working sample of my pages if you want.  I can make a test DB.   This is something I can see using over and over.  We use it now on all of our page,  I just wanted to add header column sorts so you did not have to use the filter they are a pain.  

ah so levels of paging....

would you be sorting those too?

yeap,  that was the problem,  when I tired to do the sorting on the column I lost the sort.    It would use the default sort,   the 1st page would be ok,  but as soon as you clicked next or back or tried to go to a page number,  the orderby would be set to the default.   I tired to set a session.orderby but it would not hold it.   That is why I figure it had to have something to do with the nextback,  CFinclude would not let me add to the link,  like I did for the column sort.   I hope this make sense

hmm.. not really.  :o)  sorry.

But in general I think what we did above has the summary pagination with sorting working correct?

To get the detail working I would make a separate template for the next/previous links at least to start.

Get it working then worry about if it is worthwhile combining in to one or leaving as is.


Hope that helps

Having one for the summary and one for the detail is not a problem,  I'm just not sure how to do it.   The Cgi is something I have not use much.   I think I understand it,  but just don't know where to start so if the code was
<cfset modifiedQueryString = "">
<cfloop list="#CGI.Query_String#" index="namevaluepair" delimiters="&">
<cfif ListFirst(namevaluepair, "=") NEQ "StartRow">
    <cfset modifiedQueryString = modifiedQueryString  + "&" + namevaluepair>
</cfif>
</cfloop>


<CFOUTPUT>
  <!--- Show link for Back, if appropriate --->
  <CFIF StartRowBack GT 0>
    <A HREF="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack##modifiedQueryString#">
      <IMG SRC="BrowseBack.gif" WIDTH="40" HEIGHT="16"
        ALT="Back #RowsPerPage# Records" BORDER="0"></A>
  </CFIF>
  <!--- Show link for Next, if appropriate --->
  <CFIF StartRowNext LTE TotalRows>
    <A HREF="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext##modifiedQueryString#">
      <IMG SRC="BrowseNext.gif" WIDTH="40" HEIGHT="16"
        ALT="Next #RowsPerPage# Records" BORDER="0"></A>
  </CFIF>
</CFOUTPUT>
 
This worked on the summary page  but on the detail page the link is
http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?debtor_acct_no=2779  so I need to do somthing to check for data after the ? (cgi_querystring) and some how make it part of the modifiedquerystring if it is there.  

I'm I on the right track???

Yes you are.

The loop basically removes the StartRow pair from the querystring since you are adding the new values back in.

So the rest of the name value pairs such as the debtor_acct_no=2779 should remian in the link without needing to modify the code.


So maybe it will work as is?

What does a link to get to a detail page look like?

This? http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?debtor_acct_no=2779

And the paging like this?
http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?debtor_acct_no=2779&StartRow=21

If so then no modification should be needed....

the page load link look right but the

the paging link look like this
http://192.168.0.102/printstream/Profit_By_Customer/customer_profit_detail.cfm?StartRow=21 (this is from my working templete,  I can not get to this point on my test because
I get      The value "" cannot be converted to a number    error.  

and if I try to colum header sort error is The value "" cannot be converted to a number  
link looks like   http://192.168.0.102/printstreamtest/Profit_By_Customer/customer_profit_detail.cfm?&orderby=job_no&orderbytype=DESC

you should also need the debtor_acct_no in the URL as well if you are on a detail page....

I set that to a session.variable so I guess it does not show up in the url after that I guess,  

ok, I'm going to start on this again this morning,  any suggestion, tip trick to figure out how to get this to work.  


Thanks

Could you tell me step by step what this is doing,  then I will take it from there.


<cfset modifiedQueryString = "1">
<cfloop list="#CGI.Query_String#" index="namevaluepair" delimiters="&">
<cfif ListFirst(namevaluepair, "=") NEQ "StartRow">
    <cfset modifiedQueryString = modifiedQueryString  + "&" + namevaluepair>
</cfif>
</cfloop>

Sure.

<cfset modifiedQueryString = "1">

Setting the modofied querystring variable to 1 - no idea why it is doing so.  
My code had it set to blank - reason: so variable was declared and cleared


<cfloop list="#CGI.Query_String#" index="namevaluepair" delimiters="&">

Treating the CGI.QUERY_STRING variable as a list.  It looks like:
varname1=value1&varname2=value2&varname3=value3
So we specify & as the delimeter so that each time through the loop the variable namevaluepair gets set to
varname1=value1 then varname2=value2 etc...


<cfif ListFirst(namevaluepair, "=") NEQ "StartRow">

Treat the namevaluepair as a list with = as the delimeter and get the first list item.  This in effect
gives you the varname portion of the namevaluepair.  If the varname is NOT "StartRow" then go do cfif statements.


<cfset modifiedQueryString = modifiedQueryString  + "&" + namevaluepair>

Build the querystring by taking what was there, append a & and the namevaluepair


So at the beginning of the code you have soomething like

name=joe&title=President&Startrow=21&color=blue

At the end you have the smae querystring, but with a & at the beginning and the startrow stripped out so:

&name=joe&title=President&color=blue

Since you are manually Startrow for the link you just tack this on the end and end up with the new querystring that you
want, but with a modified startrow.

I have been working on this all day but I can not get this error out of here.

The value "" cannot be converted to a number  
 
 
The Error Occurred in D:\wwwroot\PrintStreamTest\Profit_By_Customer\NextNIncludeBackNext.cfm: line 13
 
11 : <cfloop list="#CGI.Query_String#" index="namevaluepair" delimiters="&">
12 : <cfif ListFirst(namevaluepair, "=") NEQ "StartRow">
13 :     <cfset modifiedQueryString = modifiedQueryString  + "&" + namevaluepair>
14 : </cfif>
15 : </cfloop


If I get it to not show the error,  then the sorting from page to page does not work.  any suggestion

That error is most likely being caused by a cfparam statement with type set to numeric somewhere....

I looked and the only code I can found with numeric set is this code for the startrow.

<!--- What row to start at? Assume first by default --->
<CFPARAM NAME="URL.StartRow" DEFAULT="1" TYPE="numeric">
<!--- Allow for Show All parameter in the URL --->
<CFPARAM NAME="URL.ShowAll" TYPE="boolean" DEFAULT="No">

The debtor_acct_number is also numeric coming from the customer_summary,  but is does it when the orderby or orderbytype are on the url

Well the above cfparam would cause the error if the following were sent in:

page.cfm?Startrow=
page.cfm?startRow=abc
etc

So an error in the URL where startrow is not numeric is most likely causing that error.

I dont know why it would not have a startrow

I changed it to

  <cfset modifiedQueryString = "#modifiedQueryString#&#namevaluepair#"> and the error is gone,  look like it my be working

Happens to me all the time,  thanks so much for the help,  I will change to you suggestion.   It was a hard road, but now I think I understand a little more about CGI and coldfusion.  I'm going to clean this up and move on to my next coldfusion project(the Boss keeps thinging up new one)  I should up the point on this one for sure.