Can't get output to show all records from query 1

[kathryn]

Good afternoon,

I am attempting to print out a phone listing for my company from a CF page on our intranet.

The code is below. It is NOT as bad as it looks. The gobbledygook is all the rtf codes. The part in question is the CFloop and cfQuery right after the tag, and the parameters way at the end, at the end of the second to last line: #PhoneListingReport.FirstName# #PhoneListingReport.LastName# #PhoneListingReport.extension#

When I run this page, I get the download message, and I can save it and open it. However, I only get one record, the first in the query results, instead of the 100+ that I should.

I have tried the code with and without CFLoop, since CFOUTPUT doesn't need CFLOOP, but I still only get one record output.

Anyone know why?


*****BEGIN CODE*****


<CFHEADER NAME=&quot;Content-Disposition&quot; VALUE=&quot;attachment; filename=TestData.doc&quot;>

<cfquery name=&quot;PhoneListingReport&quot; datasource=&quot;Intranet&quot; dbtype=&quot;ODBC&quot;>
select firstname, lastname, extension from tblEmployee
</cfquery>

<CFCONTENT TYPE=&quot;application/rtf&quot;><cfloop query=&quot;PhoneListingReport&quot;><cfoutput>{\rtf1\ansi\ansicpg1252\uc1 \deff0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f16\froman\fcharset238\fprq2 Times New Roman CE;}{\f17\froman\fcharset204\fprq2 Times New Roman Cyr;}
{\f19\froman\fcharset161\fprq2 Times New Roman Greek;}{\f20\froman\fcharset162\fprq2 Times New Roman Tur;}{\f21\froman\fcharset186\fprq2 Times New Roman Baltic;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;
\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;
\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\widctlpar\adjustright \fs20\cgrid \snext0 Normal;}{\*\cs10 \additive Default Paragraph Font;}{\s15\widctlpar\tqc\tx4320\tqr\tx8640\adjustright \fs20\cgrid \sbasedon0 \snext15 header;}{
\s16\widctlpar\tqc\tx4320\tqr\tx8640\adjustright \fs20\cgrid \sbasedon0 \snext16 footer;}}{\info{\title TEST DATA}{\author Kathryn Butterly}{\operator Kathryn Butterly}{\creatim\yr2001\mo2\dy8\hr10\min25}{\revtim\yr2001\mo2\dy8\hr10\min26}{\version1}
{\edmins1}{\nofpages1}{\nofwords0}{\nofchars0}{\*\company Washington Mutual Finance}{\nofcharsws0}{\vern37}}\widowctrl\ftnbj\aenddoc\formshade\viewkind1\viewscale83\viewzk2\pgbrdrhead\pgbrdrfoot \fet0\sectd \linex0\endnhere\sectdefaultcl {\footer
\pard\plain \s16\widctlpar\tqc\tx4320\tqr\tx8640\adjustright \fs20\cgrid {\field{\*\fldinst { FILENAME\\p \\* MERGEFORMAT }}{\fldrslt }}{\field{\*\fldinst { FILENAME\\p \\* MERGEFORMAT }}{\fldrslt }}{
\par }}{\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang{\pntxta )}}
{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl8
\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}\pard\plain \widctlpar\adjustright \fs20\cgrid {#PhoneListingReport.FirstName# #PhoneListingReport.LastName# #PhoneListingReport.extension#
\par }}</cfoutput></cfloop>


*****CODE*****



Kathryn

 

[rmz8]

Just a shot, but try removing the query name, PhoneListingReport, from the specific code.

Ryan ;-]

 

[kathryn]

Thank for the reply Ryan.

I moved the query name from the individual parameters to the CFOUTPUT tag, as you suggested. (Just removing the query name from the parameters without adding it to the CFOUTPUT tag results in an &quot;Error resolving parameter&quot; error.)

Anyway, when I move the query name, I still only get one record.



Kathryn

 

[SteelDragon]

Kathryn,

I have a similar page and I do not use the CF loop function, but in my CFOutput tag I have the query specified, like this: <CFOUTPUT query=&quot;SearchResult&quot;> try that and nest your formatting in there. It worked for my page, I'm not sure how it will work for you.

Good Luck,

SteelDragon

 

[kathryn]

Steel (if I may call you by your first name ;-))

I have actually tried that and that is the current state of my code:

***********BEGIN CODE****************


<CFHEADER NAME=&quot;Content-Disposition&quot; VALUE=&quot;attachment; filename=TestData.doc&quot;>

<cfquery name=&quot;PhoneListingReport&quot; datasource=&quot;Intranet&quot; dbtype=&quot;ODBC&quot;>
SELECT firstname, lastname, extension
FROM tblEmployee e, tblLocation l, tblDepartment d
WHERE e.departmentid=d.departmentid and d.locationid=l.locationid and l.locationname='tampa'
ORDER BY lastname, firstname
</cfquery>

<CFCONTENT TYPE=&quot;application/rtf&quot;>{\rtf1\ansi\ansicpg1252\uc1 \deff0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f16\froman\fcharset238\fprq2 Times New Roman CE;}{\f17\froman\fcharset204\fprq2 Times New Roman Cyr;}
{\f19\froman\fcharset161\fprq2 Times New Roman Greek;}{\f20\froman\fcharset162\fprq2 Times New Roman Tur;}{\f21\froman\fcharset186\fprq2 Times New Roman Baltic;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;
\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;
\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\widctlpar\adjustright \fs20\cgrid \snext0 Normal;}{\*\cs10 \additive Default Paragraph Font;}{\s15\widctlpar\tqc\tx4320\tqr\tx8640\adjustright \fs20\cgrid \sbasedon0 \snext15 header;}{
\s16\widctlpar\tqc\tx4320\tqr\tx8640\adjustright \fs20\cgrid \sbasedon0 \snext16 footer;}}{\info{\title TEST DATA}{\author Kathryn Butterly}{\operator Kathryn Butterly}{\creatim\yr2001\mo2\dy8\hr10\min25}{\revtim\yr2001\mo2\dy8\hr10\min26}{\version1}
{\edmins1}{\nofpages1}{\nofwords0}{\nofchars0}{\*\company Washington Mutual Finance}{\nofcharsws0}{\vern37}}\widowctrl\ftnbj\aenddoc\formshade\viewkind1\viewscale83\viewzk2\pgbrdrhead\pgbrdrfoot \fet0\sectd \linex0\endnhere\sectdefaultcl {\footer
\pard\plain \s16\widctlpar\tqc\tx4320\tqr\tx8640\adjustright \fs20\cgrid {\field{\*\fldinst { FILENAME\\p \\* MERGEFORMAT }}{\fldrslt }}{\field{\*\fldinst { FILENAME\\p \\* MERGEFORMAT }}{\fldrslt }}{
\par }}{\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang{\pntxta )}}
{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl8
\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}\pard\plain \widctlpar\adjustright \fs20\cgrid <cfoutput query = &quot;PhoneListingReport&quot;>{#FirstName# #LastName# #extension#
\par }}</cfoutput>


****************END CODE************************


Could you please post your code (or at least the cfoutput part) so I can see if I am missing something stupid.

I would really appreciate it.


Kathryn

 

[SteelDragon]

Kathryn,

Here is the code from my online knowledge base search results. It displays all of the query results for a given search, my other code is at home, I'm on a laptop now, I'll post the other later if this is of no assistance.

<CFQUERY name=&quot;SearchResult&quot; dataSource=&quot;support&quot; username=&quot;######&quot;>
SELECT DISTINCT ABCard.Card_ID AS ResultField1, ABCard.Summary AS ResultField2, ABCard.Card_ID AS ID_Field, ABCard.ChangeDate AS DetailField4
FROM ABCard
WHERE #PreserveSingleQuotes(Criteria)#
Order by ABCard.ChangeDate Desc
</CFQUERY>
<CFOUTPUT query=&quot;SearchResult&quot;>
<TR bgcolor=&quot;#IIf(CurrentRow Mod 2, DE('ffffff'), DE('cccccc'))#&quot;>
<TD><font face='Tahoma'>#ResultField1#</font></TD>
<TD></TD>
<TD><A href=&quot;support_Detail.cfm?ID=#URLEncodedFormat(ID_Field)#&quot;><font face='Tahoma'>#ResultField2#</font></A></TD>
<TD><Font face='Tahoma'>#DetailField4#</FONT></TD>


</TR>
</CFOUTPUT>


SteelDragon

 

[GunJack]

Kathryn,

What happens if you remove the cfcontent tag, remove all the rtf coding, and just do a
<cfoutput>#PhoneListingReport.recordcount#</cfoutput>
<p>
<cfoutput query=&quot;PhoneListingReport&quot;>#PhoneListingReport.LastName#</cfoutput>

My first check would be to verify that the recordcount is actually greater than 1. If it is, I'd want to see what happens when the page is returned as a normal html page. I'm wondering if it's really generating the page with the 100+ records but when you open it up in an rtf viewer, the codes aren't correct and it just displays the first record.

Hope this helps,
GJ

 

[kathryn]

Thanks for the reply GJ,

I did as you suggested and the record count is 175, and all 175 last names appeared. At the end of the page was all the standard CF stuff, such as the SQL of the query and all the Variables.

So the query is returning the records, but something in the RTF is stopping it from outputting more than one line. That is where I am stuck; what is wrong in the RTF?

SteelDragon,

If you could post the form with the RTF code in it, I would appreciate it.

Thanks to you both.



Kathryn

 

[GunJack]

Hey Kathryn,

I think I see the problem. The records are being returned but your rtf syntax isn't correct so the rtf viewer is only showing the first one. I just took your syntax from your last posting and verified this. If you save off the file instead of opening it and then open it with Notepad, I think you'll see all of your records at the bottom.

In particular, I think you just need to change

<cfoutput query = &quot;PhoneListingReport&quot;>{#FirstName# #LastName# #extension#
\par }}</cfoutput>
to

{<cfoutput query = &quot;PhoneListingReport&quot;>#FirstName# #LastName# #extension# \par
</cfoutput>}{\par }}

in order to make it work. Your current code is putting a &quot;{&quot; before each record when there should just be one &quot;{&quot; at the start of the records.

Let me know if you still have problems,
GJ

 

[kathryn]

You are wonderful! I really don't understand all the rtf codes, which is my basic problem. I will look at the MSDN reference site and see what I can learn.

In the meantime, do you know if there is a way to output the file using table formatting? The file is a phone list, so I need a table with name in one column and phone extension right justified in the second column.

If it can't be done, I may just give the user some code behind her Normal.dot template in Word and then put a toolbar up with the name of the code.

Thanks again.

Kathryn

 

[GunJack]

Hey Kathryn,

I'm not sure if you mean table formatting inside of an rtf document or just in a table in a regular html document. If you just want to show it in html, I think this will work.

<table>
<cfoutput query = &quot;PhoneListingReport&quot;>
<tr>
<td>#FirstName# #LastName#</td><td>#extension#</td>
</tr>
</cfoutput>

If you want to make a table in an rtf document, I would just create a simple table in Word with a few rows, save it as an .rtf document and then open it in Notepad to see how the codes need to be structured.

Hope this helps,
GJ