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
