I'm trying to export a record set into Excel but it seems to keep failing on the production servers. However, it seems to work just fine on my development workstation. I'm wondering i fit's a server related issue but I have other apps that can export just fine using the same exact code, well similar code same set up.
<%@ Language=VBScript %>
<%Response.expires = -1%>
<%response.buffer = true%>
<%
Dim today
today = "_" + Replace(Date,"/","") + "_" + Replace(Time(),":", "")
Response.Charset = "ANSI"
Response.ContentType = "application/octet-stream"
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=List" + today + ".xls"
Response.ContentType = "application/download"
set Cnn = server.CreateObject("ADODB.connection")
Cnn.ConnectionString = Application("Cnn_ConnectionString")
Cnn.open
set rs1 = server.CreateObject("ADODB.Recordset")
SQLCollections = "Sp_MysProc @Param1=" & Session("var1")
rs1.open SQLCollections,cnn
%>
<html>
<body>
<table>
<tr>
<td>Number</td>
<td>Name</td>
</tr>
<%if not rs.eof then
do while not rs.eof %>
<tr>
<td><%=rs("Number") %></td>
<td><%=rs("Name") %></td>
</tr>
<%
rs.MoveNext
Loop
rs.Close
set rs = Nothing
End if
%>
</table>
</body>
</html>
Again, this works from my machine. But when I do it from production it gives me the following message:
Internet Explorer cannot download MyFile.asp from www.mydomain.com
Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later.
Beyond the error is there any way to make it export and not display as HTML with a white background and no lines, i.e. like a real Excel file would?
Edit: Content types have been corrected based on Anthony's answer.
The date is not hard coded to allow multiple files to be created daily with out any user intervention (user requested).
I've updated to remove the If Not EOF. I've been noticing a lot of long running connections, perhaps there are a number of these types of issues around the app. Thanks for the tip. Also it still works desipte there being no recordset which was as requested.
Edit 2 I've fixed on eof the issue with an improper column name (oops!) and it now downloads correctly on my computer from production. I have Office 2007. But the thing still will not download on at least one other computer. This other computer has Office 2000 on it. But removeing the headers and allowing it to spill out jsut the HTML it works on all machines.
Might Office 2000 have an issue with this sort of thing?