ColdFusion Programming/Using List variables in CFHTTP

Advertisement

Expert: Clint Willard - 12/8/2008


Question
Hello, I'm trying to pass over a variable containing a list via the url, to use inside a cfheader/cfcontent tag that will use this list to extract information from a database that will then be used to create an excel spreadsheet, which a user can download.
Here is the code from the first page:

<cfquery name="get" datasource="lms1">
select userid
..
</cfquery>

<input ype="button" value="Download User(s) Details" class="button1" onClick="window.location='userExcelSheet.cfm?actionId=#get.userId#'">

as you can see the first error here is that even though get.userid is a list of many userIds, I'm not certain how to use a list inside a <input> tag. However, one userid is currently being passed along, but its failing on the next page. Here is the code:

<cfparam  name="actionId" default="">
<cfset session.editUserList = "#actionId#">

<cfquery name="getUser" datasource="lms1">
   SELECT * FROM #clientId#..EMP
   WHERE userId = 'default_user'
</cfquery>
<cfset userAttributes = getUser.columnlist>
<html>
<head>
<title>Excel User Details</title>
<cfcontent type="application/msexcel">
   <cfheader name="Content-Disposition" value="filename=Employees.xls">
       
   <cfquery name="excelInfo" datasource="lms1">
   SELECT <cfloop from="1" to="#listlen(userAttributes)#" index="i"> #listgetat(userAttributes,i)#
<cfif i lt listlen(userAttributes)>,</cfif></cfloop>  
   FROM #clientid#..EMP
   WHERE userid IN (<cfloop from="1" to="#listlen(session.editUserList)#" index="i">'#listgetat(session.editUserList,i)#'<cfif i lt listlen(session.editUserList)>,</cfif></cfloop>)
</cfquery>
       
<!--- to get the column headings --->
<cfloop from="1" to="#listlen(userAttributes)#" index="i">
      <cfset excelTable = "#listgetat(userAttributes,i)#">
 </cfloop>
       
<!--- to make a space between the headings and the content --->
  <cfset excelTable = excelTable&chr(13)&chr(10)>
       
   <!--- The content --->    
       <cfoutput query="excelInfo">
       <cfset excelTable = ????>
           #excelTable#
       </cfoutput>
   
   </cfheader>
</cfcontent>

As you can see there are many things wrong with the code, particularly where the ???? is.
Any help would be much appreciated
Thank you

Answer
Sorry to take long to get back to you, death in family. Here is something that works you can apply to your problem.

<cfsetting enablecfoutputonly="Yes">

<cfset empids = "120,130">

<!--- Example query --->
<cfset myQuery = QueryNew("employee_id, firstname, lastname, salary", "Integer, VarChar, VarChar, Decimal")>
<cfset newRows = QueryAddRow(MyQuery, 3)>
<cfset temp = QuerySetCell(myQuery, "employee_id", 110, 1)>
<cfset temp = QuerySetCell(myQuery, "firstname", "Carl", 1)>
<cfset temp = QuerySetCell(myQuery, "lastname", "Henson", 1)>
<cfset temp = QuerySetCell(myQuery, "salary", "32.50", 1)>
<cfset temp = QuerySetCell(myQuery, "employee_id", 120, 2)>
<cfset temp = QuerySetCell(myQuery, "firstname", "Tom", 2)>
<cfset temp = QuerySetCell(myQuery, "lastname", "Thumb", 2)>
<cfset temp = QuerySetCell(myQuery, "salary", "12.75", 2)>
<cfset temp = QuerySetCell(myQuery, "employee_id", 130, 3)>
<cfset temp = QuerySetCell(myQuery, "firstname", "Nancy", 3)>
<cfset temp = QuerySetCell(myQuery, "lastname", "Pants", 3)>
<cfset temp = QuerySetCell(myQuery, "salary", "23.25", 3)>

<cfquery dbtype="query" name="myOtherQuery">
Select * From myQuery
Where employee_id IN (#empids#)
</cfquery>

<!--- Set special chars --->
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>

<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=Employees.xls">

<!--- Column headings --->
<cfoutput>Employee_ID#TabChar#LastName#TabChar#FirstName#TabChar#Salary#NewLine#</cfoutput>

<!--- Data rows --->
<cfloop query="myOtherQuery">
 <cfoutput>#Employee_ID##TabChar##LastName##TabChar##FirstName##TabChar##Salary##NewLine#</cfoutput>
</cfloop>

ColdFusion Programming

All Answers

Answers by Expert:

Ask Experts

Volunteer

Clint Willard

Expertise

I will volunteer to answer your Coldfusion programming questions.

Experience

I am an expert certified Coldfusion MX programmer with experience in Flash, Action Script, Flex, SQL, XML, and other related.

Education/Credentials
Associates in Applied Science and Coldfusion MX certificate.

©2012 About.com, a part of The New York Times Company. All rights reserved.