Solved

Search and resul page code in SQL and coldfusion (advanced recordsets)

Posted on 2008-11-03
1
226 Views
Last Modified: 2013-12-24
SQL statement for the advanced recordset in result page does not work in Coldfusion. No data is retrieved from database.
Please see attached codes for both the search and result pages
<THIS IS THE SEARCH PAGE CODE>

 

<cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">

  <cfset MM_Username=Iif(IsDefined("Session.MM_Username"),"Session.MM_Username",DE(""))>

  <cfset MM_UserAuthorization=Iif(IsDefined("Session.MM_UserAuthorization"),"Session.MM_UserAuthorization",DE(""))>

</cflock>

<cfif MM_Username EQ "">

  <cfset MM_referer=CGI.SCRIPT_NAME>

  <cfif CGI.QUERY_STRING NEQ "">

    <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>

  </cfif>

  <cfset MM_failureURL="index.cfm?accessdenied=" & URLEncodedFormat(MM_referer)>

  <cflocation url="#MM_failureURL#" addtoken="no">

</cfif>

<cfdump var="#url#">

<cfquery name="rsCategory" datasource="Deals">

SELECT Category

FROM deals01 

 

</cfquery>

<cfquery name="rsSubcategory" datasource="Deals">

SELECT deals01.SubCategory

FROM deals01 

</cfquery>

</END OF SEARCH PAGE CODE>

 

<THIS IS THE RESULT PAGE CODE>

<cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">

  <cfset MM_Username=Iif(IsDefined("Session.MM_Username"),"Session.MM_Username",DE(""))>

  <cfset MM_UserAuthorization=Iif(IsDefined("Session.MM_UserAuthorization"),"Session.MM_UserAuthorization",DE(""))>

</cflock>

<cfif MM_Username EQ "">

  <cfset MM_referer=CGI.SCRIPT_NAME>

  <cfif CGI.QUERY_STRING NEQ "">

    <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>

  </cfif>

  <cfset MM_failureURL="login.cfm?accessdenied=" & URLEncodedFormat(MM_referer)>

  <cflocation url="#MM_failureURL#" addtoken="no">

</cfif>

<cfparam name="PageNum_rsdealsresult" default="1" type="string">

<cfparam name="URL.rsCategory" default="1">

<cfparam name="URL.rsSubcategory" default="2">

<cfquery name="rsCategory" datasource="Deals">

SELECT deals01.Category

FROM deals01 

 

</cfquery>

<cfquery name="rsSubcategory" datasource="Deals">

SELECT deals01.SubCategory

FROM deals01

 

</cfquery>

<cfquery name="rsdealsresult" datasource="Deals">

SELECT Category, Company, links, SubCategory, brief,itemImage

FROM deals01 INNER JOIN dlimag ON deals01.imageID = dlimag.imageID

WHERE Category = <cfqueryparam value="#URL.rsCategory#" cfsqltype="cf_sql_numeric"> AND SubCategory = <cfqueryparam value="#URL.rsSubcategory#" cfsqltype="cf_sql_numeric"> 

 

</cfquery>

<cfset MaxRows_rsdealsresult=10>

<cfset StartRow_rsdealsresult=Min((PageNum_rsdealsresult-1)*MaxRows_rsdealsresult+1,Max(rsdealsresult.RecordCount,1))>

<cfset EndRow_rsdealsresult=Min(StartRow_rsdealsresult+MaxRows_rsdealsresult-1,rsdealsresult.RecordCount)>

<cfset TotalPages_rsdealsresult=Ceiling(rsdealsresult.RecordCount/MaxRows_rsdealsresult)>

 

</END OF RESULT PAGE CODE>

Open in new window

0
Comment
Question by:omojesu
1 Comment
 
LVL 16

Accepted Solution

by:
duncancumming earned 500 total points
Comment Utility
you say "No data is retrieved from database."... however you're doing 3 queries.  do you just mean for the final query, rsdealsresult, that no data is returned?  what happens when you try just running this query directly in your database
SELECT Category, Company, links, SubCategory, brief,itemImage
FROM deals01 INNER JOIN dlimag ON deals01.imageID = dlimag.imageID
WHERE Category = X AND SubCategory =Y
(replacing X and Y with the values that are being passed in the url)
???
Do you get anything then, or nothing.  If nothing, the fault isn't with CF, but with your query and/or data.  

What happens when you try cfdumping rsdealsresult - anything or nothing?  

Show us a sample of your data from the tables deals01 and dlimag .
0

Featured Post

Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now