Outputting a SQL query to file for download

[Numpty]

I'm building an application which saves customer order data to an SQL database. The sensitive data from the order is encrypted before being written to the table.

I have an admin area where I (or a colleague) can login to and view the orders etc.

I want to use this interface to download today's orders locally in the format of a text file such as CSV.

Can anyone tell me how I can write the output from an SQL query into a text file, then recieve a prompt to download it.

I can decrypt the encrypted data later, but ideally, I'd like to decrypt it first before downloading. I don't, however want any unencrypted data getting left on the server.

Thanks, Al

[mattglet]

In a nutshell, you want to do these things:

- Create your query, and get your dataset/recordset
- Clear the Response Output
- Set the ContentType of the Response to be "text/csv"
- Add a Response Header that is "Content-disposition", "filename=yourexport.csv"
- Write your dataset/recordset to the Response Output, formatted properly for the CSV file.

[Numpty]

Thanks for the pointers.

It helped me work out the solution.

[aspdaddy]

Post the title and SQL to this page

<%
' export.asp
response.ContentType="application/vnd.ms-excel"
dim objConn, strSQL
set objConn=Server.CreateObject("ADODB.Connection")
openDB( objConn )
strSQL = Sanitise(Request("SQL"))
set objRS=objConn.Execute ( strSQL )
if err.number <> 0 then
raiseError 126, "SQL Provider", "Invalid SQL"
end if
dim i,x
Response.Write "<h3>" & Request("Title") & "</h3>"
Response.Write "<table><tr>"
for each x in objRS.Fields
Response.Write "<td>" & x.name & "</td>"
next
Response.Write "</tr>"
while not objRS.eof
Response.Write "<tr>"
for each x in objRS.Fields
Response.Write "<td>" & x.value & "</td>"
next
Response.Write "</tr>"
objRS.MoveNext
wend
Response.Write "</table>"
closeRS(objRS)
closeDB(objConn)
%>

[Numpty]

Thanks for all your help.

I'm using this to output the data to file...

Response.Clear
Response.AddHeader "Content-disposition","attachment;filename=data.txt"

After the file has downloaded, I want to output some HTML to display a message to the user. However, when I try this, the HTML gets added to the download file.

What code do I add which will output the HTML code to the browser.

[mattglet]

You can only output one type of content on the page if you want to do this. I would just redirect the user to a different page after you serve up the file.

[Numpty]

Thanks, I didn't realise that a redirect would work in this situation.

[Numpty]

I've added a redirect at the end of the page, but now it doesn't offer the file for download - it just goes straight to the redirected page.

Is there a way that I can download the file, then redirect to another page?