Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-11-03
1
Medium Priority
?
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 16

Accepted Solution

by:
duncancumming earned 1500 total points
ID: 22869740
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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