Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Making resultsets available as downloadable files
- Thread starter Ian2
- Start date
This should be possible. The basic process could work something like this:
1) open recordset
2) loop through RS and use the FileSytemObject to write each row as a line to a text file (possible adding commas to separate fields?)
3) add static link to the text file on your page (<a href="textfile.txt">)
Basically your link always goes to the same file, you just change the actual file within the page code. --James
1) open recordset
2) loop through RS and use the FileSytemObject to write each row as a line to a text file (possible adding commas to separate fields?)
3) add static link to the text file on your page (<a href="textfile.txt">)
Basically your link always goes to the same file, you just change the actual file within the page code. --James
- Thread starter
- #3
If this helps, I have posted the code that I use for creating an Excel file using FSO. I have only included a snippet but you should be able to get the gist - I have used two subs - one called display_files() which lists the files created, and one called create_spreadsbeet which does the actual Excel bit:
(If you don't know this, you can call a sub using:
call subname()
)
Cheers
Biffy
Code:
'#########################
sub display_files()
'#########################
'open the file objects
set objfs = Server.CreateObject
("Scripting.FileSystemObject")
FolderPath=request.ServerVariables("APPL_PHYSICAL_PATH")
& "registrations/" 'specify filepath from root
set objfolder = objfs.GetFolder(FolderPath)
set objFiles = objFolder.Files
<html>
<body>
<table align="center" valign="top" width="100%" border="0">
<%if file <> "" then%>
<TR><TD>
<input type="button" value="Download current spreadsheet >>" name="download" onClick="javascript:window.open
('registrations/<%=file%>','registrations','toolbar=1,location=1,directories=1,menuB
ar=1,scrollbars=1,resizable=1,status=1,width=650,height=550,
left=10,top=10');">
<BR>[Filename: <%=file%>]</td></tR>
<%end if 'if file <> ""%>
<table border="1" bgcolor="#FFFFFF" cellspacing="0"
cellpadding="2" width="600">
<caption><B>Registration files</b></caption>
<TR>
<TH>Name</th>
<th>Size (kb)</th>
<th>Created</th>
</tr>
<%for each objFile in objFiles%>
<TR>
<TD><a href="reg_data/<%=objFile.Name%>" target="_blank"><%
=objFile.Name%></a></td>
<TD><%=objFile.Size%></td>
<TD><%=objFile.DateCreated%></td>
</tr>
<%next%>
</table>
</body>
</html>
'#########################
end sub 'display_files()
'#########################
'#########################
sub create_spreadsheet(file)
'#########################
'create the database connection
'(DB_CONNECTIONSTRING not included here)
set objconn=Server.CreateObject("ADODB.Connection")
objconn.open DB_CONNECTIONSTRING
set objrs=Server.CreateObject("ADODB.Recordset")
'get the data from the database you wish to display
SQL="SELECT * FROM tblregistrations WHERE IsNull
(cleared_flag) ORDER BY id asc"
set objrs=objconn.execute(SQL)
if not objrs.BOF and not objrs.EOF then
'base the spreadsheet name on the date (to keep it unique)
path=Server.mappath(".")
file=day(date)&month(date)&year(date)&"-"&hour(time)&minute
(time)&second(time)&"-registrations.xls"
range=day(date)&month(date)&year(date)&"registrations"
'create the file
set fso=CreateObject("scripting.filesystemobject")
set act=fso.CreateTextFile(server.mappath
("registrations/"&file),false)
'write the data to the spreadsheet
strline=""
For each x in objrs.Fields
strline=strline & x.Name & chr(9)
next
act.writeline strline
do while not objrs.EOF
strline=""
for each x in objrs.Fields
strline=strline & x.value & chr(9)
next
act.writeline strline
'loop thru
objrs.movenext
loop
'close the connections
act.close
end if 'if bof and eof
objrs.close
objconn.close
set objrs=nothing
set objconn = nothing
'##############################
end sub 'create_spreadsheet()
'##############################(If you don't know this, you can call a sub using:
call subname()
)
Cheers
Biffy
- Thread starter
- #6
Thanks James (and Biffy)
The page is rather complex and the error does not appear to be related to the solution, but to the way the function is called.
To simplify -
<code>
<%@ Language=VBScript %>
<!--#include file="..\databaseconnections.inc" -->
<%
radio = Request.querystring("radio"
if radio = "Callout" then
strParams = chr(34) & "where...." & chr(34)
set rs.cnn.execute("exec getdata" & strparams)
end if
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>Callout Reports</title>
</HEAD>
<BODY>
<FORM name="TopForm" method="Post">
<TABLE WIDTH=65% BORDER=0 CELLSPACING=1 CELLPADDING=1 align=center >
<TR>
<TD><INPUT type="button" id=DownloadData name=button_DownloadData value="Download Data"></TD>
</TR>
</TABLE>
</FORM>
<form name="BottomForm" method="Post">
<!-- Table for results ------------------------------------------------------>
<TABLE>
<TR> Headings
</TR>
<%
Line=1
Do While NOT rs.EOF
Fieldname = rs.field(name).value
%>
<TR>
<TD><%=field%></TD>
</TR>
<%
rs.movenext
line=line+1
if line=1000 then
exit do
end if
Loop
%>
</TABLE>
</form>
</BODY>
</HTML>
<Script ID=clientEventHandlersVBS Language="VBscript">
Public Sub button_DownloadData_OnClick
CreateCSVFile()
End Sub
</SCRIPT>
<SCRIPT Language="VBScript" runat="server">
Function GenFileName()
dim fname
Select Case radio
Case "Callout"
fname = "Callout"
GenFileName = fname
Case .....
End select
End Function
Function CreateCSVFile()
strFile = GenFileName()
Set fs = Server.CreateObject("Scripting.FileSystemObject"
Set a = fs.CreateTextFile(server.MapPath("." & "\CSV\" & strFile & ".csv",True)
For i = 0 To RS.fields.Count-1
strtext = chr(34) & RS(i).name & chr(34) & ","
a.Write(strtext)
next
a.Writeline()
If Not RS.EOF Then
Do Until RS.EOF
For i = 0 To RS.fields.Count-1
strtext = chr(34) & RS.fields(i) & chr(34) & ","
a.Write(strtext)
Next
a.Writeline()
RS.MoveNext
Loop
End If
a.Close
Set fs=Nothing
Response.Write("Click <A HRef=../Reports/CSV/" & strFile & ".csv>Here</A> to get CSV file"
End Function
</Script>
</code>
There may be some minor slips as I have cannibalised it to take out the extraneous detail. The core is in how the functions are called. Even if I remove the contents -
Function CreateCSVFile()
End Function
it still returns a Type Mismatch error.
The page is rather complex and the error does not appear to be related to the solution, but to the way the function is called.
To simplify -
<code>
<%@ Language=VBScript %>
<!--#include file="..\databaseconnections.inc" -->
<%
radio = Request.querystring("radio"
if radio = "Callout" then
strParams = chr(34) & "where...." & chr(34)
set rs.cnn.execute("exec getdata" & strparams)
end if
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>Callout Reports</title>
</HEAD>
<BODY>
<FORM name="TopForm" method="Post">
<TABLE WIDTH=65% BORDER=0 CELLSPACING=1 CELLPADDING=1 align=center >
<TR>
<TD><INPUT type="button" id=DownloadData name=button_DownloadData value="Download Data"></TD>
</TR>
</TABLE>
</FORM>
<form name="BottomForm" method="Post">
<!-- Table for results ------------------------------------------------------>
<TABLE>
<TR> Headings
</TR>
<%
Line=1
Do While NOT rs.EOF
Fieldname = rs.field(name).value
%>
<TR>
<TD><%=field%></TD>
</TR>
<%
rs.movenext
line=line+1
if line=1000 then
exit do
end if
Loop
%>
</TABLE>
</form>
</BODY>
</HTML>
<Script ID=clientEventHandlersVBS Language="VBscript">
Public Sub button_DownloadData_OnClick
CreateCSVFile()
End Sub
</SCRIPT>
<SCRIPT Language="VBScript" runat="server">
Function GenFileName()
dim fname
Select Case radio
Case "Callout"
fname = "Callout"
GenFileName = fname
Case .....
End select
End Function
Function CreateCSVFile()
strFile = GenFileName()
Set fs = Server.CreateObject("Scripting.FileSystemObject"
Set a = fs.CreateTextFile(server.MapPath("."
& "\CSV\" & strFile & ".csv",True)For i = 0 To RS.fields.Count-1
strtext = chr(34) & RS(i).name & chr(34) & ","
a.Write(strtext)
next
a.Writeline()
If Not RS.EOF Then
Do Until RS.EOF
For i = 0 To RS.fields.Count-1
strtext = chr(34) & RS.fields(i) & chr(34) & ","
a.Write(strtext)
Next
a.Writeline()
RS.MoveNext
Loop
End If
a.Close
Set fs=Nothing
Response.Write("Click <A HRef=../Reports/CSV/" & strFile & ".csv>Here</A> to get CSV file"
End Function
</Script>
</code>
There may be some minor slips as I have cannibalised it to take out the extraneous detail. The core is in how the functions are called. Even if I remove the contents -
Function CreateCSVFile()
End Function
it still returns a Type Mismatch error.
Ok, the problem here is your attempting to run a server-side function from a client-side event. This is not something that can be done. Once the server has executed any code it has it is finished and deallocates any memory it was using. At this point the client is just receiving the last portion of the page from the server and the onLoad event wwill fire. Since the Server is no longer processing at this point it is not possible to call the server-side code from the client(not to mention the inherrent communication issues and such that result). What you will need to do is on the event where you would want to create the CSV file you need to submit the page to a new page that actually does the CSV creation and such in ASP.
I would tend to go something similar to Biffy's solution, where you actually write the file on the fly and either display it or allow the user to download it. This means you don't have to worry about several people requesting the page at once because it will be written on the fly each time. The advantage of this is you don't have to worry about a naming technique for you text files. If you were to go with a hardcoded name it would be possible that different people would request the page at the same time, causing that file to be overwritten several times and then the same copy distributed to everyone. If you use a dynamic scheme you have to make sure you delete the files when your done giving them to the user or they will fill up the server with garbage. In any case there is the possibility that someone is going to copy the link into an email or such and expect it to still be there the next time they need it (ie, a lazy person that doesn't want to waste space on their own hard drive).
-Tarwn ________________________________________________
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048
I would tend to go something similar to Biffy's solution, where you actually write the file on the fly and either display it or allow the user to download it. This means you don't have to worry about several people requesting the page at once because it will be written on the fly each time. The advantage of this is you don't have to worry about a naming technique for you text files. If you were to go with a hardcoded name it would be possible that different people would request the page at the same time, causing that file to be overwritten several times and then the same copy distributed to everyone. If you use a dynamic scheme you have to make sure you delete the files when your done giving them to the user or they will fill up the server with garbage. In any case there is the possibility that someone is going to copy the link into an email or such and expect it to still be there the next time they need it (ie, a lazy person that doesn't want to waste space on their own hard drive).
-Tarwn ________________________________________________
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048
- Thread starter
- #8
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
