Morning Mike:
Netscape is notorious for hanging up on large
recordsets in a table display. Internet explorer is much more graceful
in dealing with this problem. The best way to display the records in the
browser to create a paging method and let the user page through the
results. As you have said the use wants to see all the records on one
page that makes this more difficult. You may be better off writing the
query results to a file using cffile and sending the file to the
browser using the cfcontent tag or providing a link to the file for the user
to download.
This is a snippet from a process that I am using
to create an export file on the file with user choices on the file type and
options. This is not the entire page but you should get the idea.
There are a number of ways to do the following but this is something I created
and it seems to work well.
---------------------- start of cfm to create
file ------------------
<cfquery name="getRows"
datasource="db">
select
uid,fname,lname,title,substring(email,1,20) as
email,
haddress,haddress2,hcity,hprov,hpostal,hcountry,hphone,hfax,
len(email)
as
emaillen,bname,baddress,baddress2,bcity,bprov,bcountry,bpostal,
bphone,bfax,teachtype,teachother,infoupdate,summer,contact,lang,region,alumnae,
status,email
as emailfull
from mod_mem_members
where uid >
0
</cfquery>
<!---setup query loop--->
<!---
create temporary file for export and rename with extension
chosen--->
<cfset
tmpfile=gettempfile("#request.sitedir#\wrkdbs","exp")>
<cfif
form.filetype eq "s">
<cfset
fileext="txt">
<cfelse>
<cfset
fileext="csv">
</cfif>
<cfset
newfile=replace("#tmpfile#","tmp",fileext)>
<cffile action="rename"
source="#tmpfile#"
destination="#newfile#">
<cfset
outfile=newfile>
<!---header row--->
<cfset
textoutput="uid,title,fname,lname,email,lang,status,region,teachtype,teachother,contact,busname,address,address2,city,prov,postal,country,phone,fax">
<cffile action="append" file="#outfile#"
output="#textoutput#">
<!---get records and output to file--- used
this with 3000 records in about 25 seconds>
<cfloop
query="getRows">
<cfset
textoutput="#uid#~#title#~#fname#~#lname#~#emailfull#~#curlang#~#status#~#region#~#teachtype#~#teachother#~#curcontact#~">
<cfset
textoutput=textoutput&"~#haddress#~#haddress2#~#hcity#~#hprov#~#hpostal#~#hcountry#~#hphone#~#hfax#">
<!---check if values are to be
quoted--->
<cfif form.quote eq "y">
<cfset
textoutput='"'&replace(textoutput,'~','","','all')&'"'>
<cfelse>
<cfset
textoutput='"'&replace(textoutput,'~',',','all')&'"'>
</cfif>
<cffile
action="append" file="#outfile#"
output="#textoutput#">
</cfloop>
<!--- give user a link to the
file--->
<tr><td class="left11"
colspan="3"> </td></tr>
<tr><td
class="left11"> </td><td class="left11B">Export
Results Destination:</td><td
class="left11"> </td></tr>
<tr><td
class="left11"> </td><td class="dlg-helptext">Export
file is available for download <a
href="blocks/dsp_mem_export_link.cfm/#listlast(newfile,"\")#">here</a></td><td
class="left11"></td></tr>
---------------------- end of cfm to create file
------------------
<!---cfm file called from link in page after
query is exported to a comma separated file - filename is sent in
url
important to have no extra code than needed when
doing a cfcontent--->
<cfset
filenme=mid(cgi.path_info,find(".cfm/",cgi.path_info)+5,20)>
<cfheader
name="Content-Disposition" value="filename=#filenme#">
<cfcontent
type="application/ms-excel" file="drive:\filepath\#filenme#">
----- Original Message -----
Sent: Tuesday, April 23, 2002 8:48
AM
Subject: [CFTALKTor] Access Query to
Excel Spread Sheet
Hi All,
I have problem with a query that I am doing in
access with CF. The query is returns a lot of records and
causes the browser (netscape 4.78 and the network security won't
upgrade!) to go "not responding" for a while then it produces the
query results. The query comes out as one table, they want to see all
the records in one page. Any suggestions on how to make this run
better so the users browser doesn't crash or give them the impression that
it has crashed?
Does having the whole query result in one table
slow things down?
Providing that the above is a problem that the
results are just to big. Can anyone tell me how convert my query
results in to an Excel Spread and produce a link that the can
download.
Thanks
Mike
