IE 5.5 can't handle SQL-to-Excel output? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IE 5.5 can't handle SQL-to-Excel output?

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-12 : 16:05:59

I've been using a variation of this nice ASP procedure

http://www.codeave.com/asp/code.asp?u_log=40

to allow my users to redirect the results of SQL database queries directly to MS Excel via an ASP page. They can press one of two buttons on the web page: "to browser" or "to excel". My ASP script redirects based on button push. Today I discovered that one of my users is getting "no records in the database" when choosing the "to excel" output option, while getting normal results returned for the same (valid) query when he picks the "to browser" option.

He is using IE 5.5 as opposed to most folks, who are using IE 5.0. Here are the key elements of the script. Note that the original ASP form passes a range of values to an SPROC. It appears that IE/Excel is somehow losing the range of values during processing (in this specific IE 5.5 case only -- works fine on IE 5.0) , hence the "no records" returned due to empty recordset...

================================================================

<%@ LANGUAGE="VBSCRIPT" %>



<%
response.buffer=true
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "Cache-Control", "private"
%>
<html><head>
<title>All Prescription in SQL, by Person_ID</title>
<meta http-equiv="pragma" content="no-cache">
</head><body bgcolor="#FFFFFF">
<%

'GET ALL FIELDS FROM INPUT BOXES
'THIS INCLUDES THE TWO POSSIBLE SUBMIT SCENARIOS

strPersIDRange1 = cStr(Request.Form("PersIDRange1"))
strPersIDRange2 = cStr(Request.Form("PersIDRange2"))
strExcelPrescription = cStr(Request.Form("ExcelPrescription"))
strBrowserPrescription = cStr(Request.Form("BrowserPrescription"))

myDSN="DSN=filter"

mySQL="sp_Prescription_results_range '" & strPersIDRange1 &"','" & strPersIDRange2 &"'"

set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(mySQL)
howmanyfields=rstemp.fields.count -1

' Only display table if there's a record
' in the recordset
If Not rstemp.EOF Then
' Display the form

%>

<%

'==============================
'CHECKING FOR EXCEL BUTTON PUSH
'==============================

if strExcelPrescription = "To Excel" Then%>

<%

'===========================
'WE HAVE EXCEL BUTTON PUSH
'============================
' Tells the browser to open excel
Response.ContentType = "application/vnd.ms-excel" %>

<br>

<br>

<CENTER>

<table border="0" cellspacing="2">

<tr>
<td valign="top" bgcolor="#333399"><font face="verdana" color="white" size="1">Person_ID</font></td>
<td valign="top" bgcolor="#333399"><font face="verdana" color="white" size="1">Prescription</font></td>

</tr>

<% ' Now lets grab all the records

do while not rstemp.eof %>

<tr>

<td valign="top" bgcolor=#96B696><font face=verdana size=1><%=rstemp("Person_ID")%></font></td>

<td valign="top" bgcolor=#96B696><font face=verdana size=1><%=rstemp("Prescription")%></font></td>

</tr>
<%
rstemp.movenext
loop

rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
%>

</table>

</CENTER>

<center>
<font size=2 face=verdana>Above are Prescription results based on the Person_ID Range you entered.</font>
</center>

*The rest of the code redirects to either standard browser output, based on "to browser" button push or final <%else%> which shows "no records" message in the event of empty recordset.

thx

Edited by - steelkilt on 12/12/2002 16:07:33

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-12 : 16:33:35

If I had to guess, I would say that IE 5.5 is not recognizing the ContentType = "application/vnd.ms-excel". It may also require that the file is written with a .XLS extension and not .ASP or .HTM(L).

This is even more likely if he is using a different version of Windows and/or a different service pack than everyone else. Also look at how Windows treats .XLS files on his computer (Windows Explorer, Tools, Folder Option, File Types) His machine may or may not use DDE to interpret .XLS files. Any combination of these factors could be causing his computer to not recognize the web output as Excel output.

You might also want to modify this snippet of code:

<% ' Now lets grab all the records

do while not rstemp.eof %>

<tr>

<td valign="top" bgcolor=#96B696><font face=verdana size=1><%=rstemp("Person_ID")%></font></td>

<td valign="top" bgcolor=#96B696><font face=verdana size=1><%=rstemp("Prescription")%></font></td>

</tr>
<%
rstemp.movenext
loop

To this:

<% ' Now lets grab all the records
data=rstemp.GetString(, , "</font></td><td valign=top bgcolor=#96B696><font face=verdana size=1>",
"</font></td></tr><tr><td valign=top bgcolor=#96B696><font face=verdana size=1>",
"") %>
<tr>
<td valign="top" bgcolor=#96B696><font face=verdana size=1><%= data%>

Getstring() will vastly improve the performance of your web page if you have more than 30-40 rows of data to display (especially if you add more columns to your current output) You might also want to look at some basic CSS styles, they can cut down on the amount of HTML generated and can further speed up the page:

<html><head>
<style> td.verd { font-family: Verdana; font-size: medium; background-color: #96B696; } </style>
</head> (add this to the <head> element of your page)

<% ' Now lets grab all the records
data=rstemp.GetString(, , "</td><td valign=top class=verd>", "</td></tr><tr><td valign=top class=verd>",
"") %>
<tr>
<td valign=top class=verd><%= data%>

Search the SQL Team forums for "Getstring" and you'll find some more code samples and other links that use it.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-12 : 17:02:27

Don't know if this helps but, what I've done is when someone wants to see the data in excel I write out a file with the extension csv. Then I redirect the browser to that file.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-13 : 13:22:54

Valter,

Interesting approach. Could you supply a code snippet?

thx.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-13 : 13:30:13

SteelKit,
I've done some things along the same lines as Valter. Basically, use FSO (FileSystemObject) to create a new file (usually named guid.filexetension), write out the data, and then response.redirect the user to the newly created file. Depending on the file type etc, it will open it or ask them to open/save as.

Do some searching on FileSystemObject at google and you'll see examples of creating new files and writing data to them.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 15:16:37

Here is an article that talks about the FileSystemObject.
http://www.winscriptingsolutions.com/Articles/Index.cfm?ArticleID=8389

Just Open a file give it a unique name you can get a GUID from SQL Server or use VB (can't remember the function or object that returns these) and a .csv extension.

The loop over your recordset and write it out to a file in csv format.

"field1", "field2"...etc..

remember to escape " with double "

Then use Response.Redirect() or Server.Transfer() to point at the csv file you just created.

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2002-12-16 : 09:05:39

Hmmm...

It could also be because your setting the response type *after* sending back some HTML.
If IIS is using the response buffer, I'd have thought this wouldn't have happened.... but who knows

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-16 : 09:11:47

Nice catch Maverick! The ContentType does indeed need to be set before any content is written by the Response object.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources