• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

cfquery to excel only outputs 1 record

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>
0
lhaluska
Asked:
lhaluska
2 Solutions
 
PluckaCommented:
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
0
 
JeffHowdenCommented:
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>
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now