Solved

Cfquery within a Cfoutput

Posted on 2001-07-01
17
628 Views
Last Modified: 2013-12-24
I have a problem creating a menu page.  What happens is the user selects a reports and from a table the report is generated.  Each field that makes the report can contain a list, and the list has to be generated from SQL. The first part works fine, getting the lists and assigning them different cfquery names.  I need a cfif to see if the field required a select box, but when I do the cfif I get errors with an extraneous cfif tag. I do the cfset's so they work with any cfoutput.

How do I do this??

<cfif Format is "Distinct"><cfset GDF=#FieldName#></cfoutput>
     <cfoutput query="GetDistinct#GDF#"><SELECT NAME="#GDF#">              
     <option value="#Field#" >#Field#</option>
     </cfoutput></select>
</cfif>
0
Comment
Question by:mor4eus
  • 10
  • 4
  • 2
  • +1
17 Comments
 
LVL 1

Author Comment

by:mor4eus
ID: 6243556
Oh yeah.  Before the cfif I already have a <cfoutput> open and after the if I have a </cfoutput>
0
 
LVL 1

Expert Comment

by:Soulive
ID: 6243579
First of all, your extraneous cfif error is caused by misplacing cfoutput tags in association with cfif tags.  If you are using conditional logic of any kind, CF requires you to have all your related code residing within the cfif tags.  For example:

<cfoutput>
#myvar#

 <cfif myvar is "me">
  This is myvar #myvar#

</cfoutput>

 </cfif>

The preceding code would generate an extraneous tag error. Basically cf is telling you that it can't have orphaned tags lying around.  In my example if myvar didn't equal "me" than cf would theoretically never see the closing output tag so it wouldn't function.  Hope this starts helping your issue.  I would also advice posting more of your code.
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6243645
Oh yeah.  Before the cfif I already have a <cfoutput> open and after the if I have a </cfoutput>
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6243647
Here is all the code

<cfquery name="GetReports" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1'
</cfquery>
<cfquery name="GetDistincts" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1' AND Format = 'Distinct'
</cfquery>
<cfoutput query="GetDistincts">
<cfquery name="GetDistinct#Field#" datasource="ReportsSQL">
SELECT distinct #Field# from Catalogue
</cfquery>
</cfoutput>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
     <title>Untitled</title>
</head>

<body>
<table>
<tr bgcolor="#FFFFCC"><td align="right"><font size="1">Check to include<br>this field</font></td><td> Matching</td></tr>

<cfoutput query="GetReports">
<tr><td align="right">#Legend# <input type="Checkbox" name="#Field#Tick" value="1" <cfif Checkbox is 1>checked</cfif>></td>
     
<td>
<cfif Format is "Distinct"><cfset GDF=#Field#></cfoutput>
     <cfoutput query="GetDistinct#GDField#"><SELECT NAME="#GDF#">              
     <option value="#Field#" >#Field#</option>
     </cfoutput></select>
</cfif>
#field#
</td>
</tr>
</cfoutput>
</table>
</body>
</html>
0
 
LVL 5

Expert Comment

by:Yog
ID: 6243806
<cfoutput query="GetReports">
<tr>
<td align="right">
#Legend#
<input type="Checkbox" name="#Field#Tick" value="1"
<cfif Checkbox is 1>checked</cfif>
>
</td>
<td>
<cfset myquery = "GetDistinct#GDField#">
<cfset myquery = evaluate(myquery)>

<cfoutput query="GetDistinct#GDField#">
<Cfloop from="1" to="#myquery.recordcount#" index="i_Index">

<cfif Format is "Distinct">
<cfset GDF=#Field[i_Index]#>
<SELECT NAME="#GDF#">        
<option value="#Field[i_Index]#" >#Field[i_Index]#</option>
</select>
</cfif>
#field[i_Index]#

</cfloop>

</td>
</tr>
</cfoutput>

tell me if this is what you want ..

cheers,
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6243818
Yeah, just can't get it to work properly.  Not enought </cfoutput> tags.  DOn't know where to put it.
0
 
LVL 5

Expert Comment

by:Yog
ID: 6243838
hi you dont need to do 3 queries at all, just 2 queries would do i think , is format a field name or you need unique records..



0
 
LVL 5

Expert Comment

by:Yog
ID: 6243870
hi,
check if this works, if you could tell the query field names that may help coz #field# is confusing for all the query ..is the name same in all cases
-----------------
<cfquery name="GetReports" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1'
</cfquery>
<cfquery name="GetDistincts" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1' AND Format = 'Distinct'
</cfquery>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>Untitled</title>
</head>

<body>
<table>
<tr bgcolor="#FFFFCC"><td align="right"><font size="1">Check to include<br>this field</font></td><td>
Matching</td></tr>

<cfloop from="1" query="#GetReports.RecordCount#" index="i">
<tr><td align="right">
<cfoutput>
#Legend[i]#
 <input type="Checkbox" name="#Field[i]#Tick" value="1"
 <cfif Checkbox is 1>checked</cfif>>
</cfoutput>
</td>
   
<td>
<cfif Format is "Distinct">
<cfset GDF=#Field[i]#>
<cfset GDF = evaluate(GDF)>
<cfoutput query="GetDistincts">
     <cfquery name="GetDistinct#Field[i]#" datasource="ReportsSQL">
          SELECT distinct #Field[i]# from Catalogue
     </cfquery>
     <SELECT NAME="#GDF#">
     <cfoutput query="GetDistinct#Field[i]#">
      <option value="#Field#" >#Field#</option>    
      </cfoutput>
     </select>
#field[i]#
</cfoutput>
</cfif>
</td>
</tr>
</cfloop>
</table>
</body>
</html>
0
Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

 
LVL 1

Author Comment

by:mor4eus
ID: 6243900
Yes the name is the same.  What it does it generate a selection page to genereate a report.  The column field are the different selection criteria.  Etc Album, Artist, CatalogueNo, Format, Price.

A sample line from the sql for the field is like this.

ReportName     TableName     Field     Order     Legend     Format     DisplayFormat     CheckBox     Default     Range
cat1     catalogue     artist     30     Artist Name     text     text     1          

Hope this helps.  STill the same though with the code though.
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6243907
Oh, the multiple queries are for the multiple fields which require a drop down.  For instance: the fields are first..

Format - alb, single, video...
Genre - Pop, Rock, R&B, Dance...
Label - Sputnik, Jive, Echo
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6243928
If you want to see an example of what I want send me your email and I will send you a link.
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6243951
If you want to see an example of what I want send me your email and I will send you a link.
0
 
LVL 5

Expert Comment

by:Yog
ID: 6243971
ooty_boy2000@yahoo.com
0
 
LVL 4

Accepted Solution

by:
CF_Spike earned 200 total points
ID: 6244323
If you are having problems with nested cfoutput tags you can use cfloop to output a query. The code you posted earlier could be rewritten as below. I am not sure which queries the variables are coming from, so I have guessed the query scope, but the principle is correct.

<cfquery name="GetReports" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1'
</cfquery>
<cfquery name="GetDistincts" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1' AND Format = 'Distinct'
</cfquery>
<cfloop query="GetDistincts">
<cfquery name="GetDistinct#Field#" datasource="ReportsSQL">
SELECT distinct #Field# from Catalogue
</cfquery>
</cfloop>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>Untitled</title>
</head>

<body>
<table>
<tr bgcolor="#FFFFCC"><td align="right"><font size="1">Check to include<br>this field</font></td><td>
Matching</td></tr>
<CFOUTPUT>
<cfloop query="GetReports">
<tr><td align="right">#Legend# <input type="Checkbox" name="#Field#Tick" value="1" <cfif Checkbox is
1>checked</cfif>></td>
   
<td>
<cfif Format is "Distinct"><cfset GDF=#getreports.Field#>
    <cfloop query="GetDistinct#GDField#"><SELECT NAME="#GDF#">              
    <option value="#Evaluate('getdistinct' & getreports.GDField & '.' & 'Field')#" >#Evaluate('getdistinct' & getreports.GDField & '.' & 'Field')#</option>
    </cfloop></select>
</cfif>
#getreports.field#
</td>
</tr>
</cfloop>
</CFOUTPUT>
</table>
</body>
</html>

Spike
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6244492
That works fine.  But why the
('getdistinct' & getreports.GDField & '.' & 'Field')
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6244507
In order to dynamically evaluate the correct query and colunn name I had to combine the static parts - 'getdistinct', '.' and 'field' with the dynamic part -getreports.GTField. This could be rewritten as:

"getdistinct#getreports.GTField#.field" without the evaluate function. It's just a question of syntax preference.

Spike
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6244516
Thanks again Spike and Yog.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Time Sheet Help 6 45
Restrict IIS Website without Windows login 7 54
Firewall Speed Issue 6 66
Coldfusion Datefield problem 2 96
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

17 Experts available now in Live!

Get 1:1 Help Now