We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

cfquery to excel only outputs 1 record

lhaluska
lhaluska asked
on
Medium Priority
157 Views
Last Modified: 2013-12-24
Here is my code to send my query to the excel file but only one shows up?

<cfquery name="qryAccessData"
         datasource="NSKInfo">
             SELECT     a.systemName, a.systemNumber, a.numberofcpu, a.numberofio, a.ORG, a.division, b.director, a.type, a.comment, a.disposition, a.location, a.dateleftbldg, a.rmano, a.redeploy, a.lightson, a.footprint, a.srdate, a.srby, a.onallcount
FROM         dbo.NSKSystemInfo a INNER JOIN dbo.director b ON a.director_id = b.director_ID
WHERE     (a.onallcount = 'Yes')
order by a.systemname

</cfquery>

<!--- <CFOUTPUT><h1>#holder#</h1></CFOUTPUT>  --->
<br>



      <cfoutput query="qryAccessData">
<cfheader name="content-disposition" value="attachment;filename=allsyscountDump.xls">
<cfcontent type="application/vnd.ms-excel">
<table border="1">
     <tr>
          <th>System Name</th>
          <th>Sys/Order Number</th>
          <th>Number of CPU</th>
          <th>Number of IO</th>
          <th>Type</th>
          <th>Division</th>
          <th>Director</th>
          <th>ORG</th>
          <th>Comment</th>
          <th>Disposition</th>
          <th>Location</th>
          <th>Date Left Bldg</th>
          <th>RMA& Number</th>
          <th>Redeploy</th>
              <th>Lights ON/OFF</th>
          <th>Footprint</th>
          <th>SRDate</th>
              <th>SR BY</th>
     </tr>
     <tr>
          <td>#qryAccessData.systemName#</td>
          <td>#qryAccessData.systemNumber#</td>
          <td>#qryAccessData.numberofcpu#</td>
          <td>#qryAccessData.numberofio#</td>
          <td>#qryAccessData.type#</td>
          <td>#qryAccessData.division#</td>
              <td>#qryAccessData.director#</td>
          <td>#qryAccessData.ORG#</td>
          <td>#qryAccessData.comment#</td>
          <td>#qryAccessData.disposition#</td>
          <td>#qryAccessData.location#</td>
          <td>#qryAccessData.dateleftbldg#</td>
          <td>#qryAccessData.rmano#</td>
          <td>#qryAccessData.redeploy#</td>
          <td>#qryAccessData.lightson#</td>
          <td>#qryAccessData.footprint#</td>
          <td>#qryAccessData.srdate#</td>
          <td>#qryAccessData.srby#</td>
     </tr>
</table>
      </cfoutput>
Comment
Watch Question

Commented:
lhaluska,

<cfheader name="content-disposition" value="attachment;filename=allsyscountDump.xls">
<cfcontent type="application/vnd.ms-excel">

Should be before  <cfoutput query="qryAccessData">

They way you have it you are outputting a headder after each row, thus only getting one row.

Regards
Plucka

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Additionally, your <cfoutput query="qryAccessData"> needs to be between the header row and the data rows.

<cfheader name="content-disposition" value="attachment;filename=allsyscountDump.xls">
<cfcontent type="application/vnd.ms-excel">

<table border="1">
<thead>
     <tr>
          <th>System Name</th>
          <th>Sys/Order Number</th>
          <th>Number of CPU</th>
          <th>Number of IO</th>
          <th>Type</th>
          <th>Division</th>
          <th>Director</th>
          <th>ORG</th>
          <th>Comment</th>
          <th>Disposition</th>
          <th>Location</th>
          <th>Date Left Bldg</th>
          <th>RMA& Number</th>
          <th>Redeploy</th>
          <th>Lights ON/OFF</th>
          <th>Footprint</th>
          <th>SRDate</th>
          <th>SR BY</th>
     </tr>
</thead>
<tbody>
     <cfoutput query="qryAccessData">
     <tr>
          <td>#qryAccessData.systemName#</td>
          <td>#qryAccessData.systemNumber#</td>
          <td>#qryAccessData.numberofcpu#</td>
          <td>#qryAccessData.numberofio#</td>
          <td>#qryAccessData.type#</td>
          <td>#qryAccessData.division#</td>
          <td>#qryAccessData.director#</td>
          <td>#qryAccessData.ORG#</td>
          <td>#qryAccessData.comment#</td>
          <td>#qryAccessData.disposition#</td>
          <td>#qryAccessData.location#</td>
          <td>#qryAccessData.dateleftbldg#</td>
          <td>#qryAccessData.rmano#</td>
          <td>#qryAccessData.redeploy#</td>
          <td>#qryAccessData.lightson#</td>
          <td>#qryAccessData.footprint#</td>
          <td>#qryAccessData.srdate#</td>
          <td>#qryAccessData.srby#</td>
     </tr>
     </cfoutput>
</tbody>
</table>
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.