generate Excel balks

S

shank

I generate Excel from ASP with all manufacturers except one. The one problem
manuf has 27,000 rows of data. In QA, it takes 30 seconds to run results.

Using ASP, I get a page not found error. I submit all other manuf and it
works as expected.

What am I doing wrong?

<%
Server.ScriptTimeout=240
Response.Clear
Response.Buffer = True
Response.contenttype = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=" & varFilename & ".xls"
%>

thanks
 
D

Daniel Crichton

shank wrote on Wed, 11 Mar 2009 20:23:13 -0400:
I generate Excel from ASP with all manufacturers except one. The one
problem manuf has 27,000 rows of data. In QA, it takes 30 seconds to
run results.
Click to expand...
Using ASP, I get a page not found error. I submit all other manuf and
it works as expected.
Click to expand...
What am I doing wrong?
Click to expand...
<%
Server.ScriptTimeout=240
Response.Clear
Response.Buffer = True
Response.contenttype = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=" & varFilename &
".xls"
%>
Click to expand...
thanks
Click to expand...

All you've posted is the part of the code that's adds header. Where's the
code that generates the actual XLS file?

All we can do is guess. My guess is that you might be hitting the response
buffer limit. Are you using sending the data in chunks, or just all in one
go?
 
S

shank

Daniel Crichton said:
shank wrote on Wed, 11 Mar 2009 20:23:13 -0400:






All you've posted is the part of the code that's adds header. Where's the
code that generates the actual XLS file?

All we can do is guess. My guess is that you might be hitting the response
buffer limit. Are you using sending the data in chunks, or just all in one
go?
Click to expand...
============================================
Here's the entire page.... I changed Response.Buffer = True, to False. The
script ran a long time, but failed to save the end result.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Session("Manuf") <> "" Then
varFilename = Replace(Session("Manuf")," ","_")
ElseIf Session("Label") <> "" Then
varFilename = Replace(Session("Label")," ","_")
End If
%>

<%
Server.ScriptTimeout=240
Response.Clear
Response.Buffer = False
Response.contenttype = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=" & varFilename & ".xls"
%>
<%
If Session("Manuf") <> "" Then
varFilename = Session("Manuf")
ElseIf Session("Label") <> "" Then
varFilename = Session("Label")
End If
%>
<%
If Session("Manuf") <> "" Then
varManuf = Session("Manuf")
Else
varManuf = ""
End If
%>
<%
If Session("Label") <> "" Then
varLabel = Session("Label")
Else
varLabel = ""
End If
%>
<%
Compilation = ""
OrderNo = ""
%>
<%
Dim rsS__Label
rsS__Label = "%"
If (varLabel <> "") Then
rsS__Label = varLabel
End If
%>
<%
Dim rsS__Manuf
rsS__Manuf = "%"
If (varManuf <> "") Then
rsS__Manuf = varManuf
End If
%>
<%
Dim rsS
Dim rsS_cmd
Dim rsS_numRows

Set rsS_cmd = Server.CreateObject ("ADODB.Command")
rsS_cmd.ActiveConnection = MM_WSD_STRING
rsS_cmd.CommandText = "{call WSD_admin.stp_WSD_Excel_Label_or_Manuf(?,?)}"
rsS_cmd.Prepared = true
rsS_cmd.Parameters.Append rsS_cmd.CreateParameter("param1", 200, 1, 255,
rsS__Label) ' adVarChar
rsS_cmd.Parameters.Append rsS_cmd.CreateParameter("param2", 200, 1, 255,
rsS__Manuf) ' adVarChar

Set rsS = rsS_cmd.Execute
rsS_numRows = 0
%>

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsS_numRows = rsS_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Create an Excel Spreadsheet</title>
</head>

<body>
<table border="1">
<tr>
<%
'Loop through each Field, printing out the Field Names
For i = 0 to rsS.fields.count - 1
%>
<td><%=rsS(i).name%></td>
<% Next %>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsS.EOF))
%>
<tr>
<td><%=(rsS.Fields.Item("Category").Value)%></td>
<td><%=(rsS.Fields.Item("OrderNo").Value)%></td>
<td><%=(rsS.Fields.Item("ManCode").Value)%></td>
<td><%=(rsS.Fields.Item("Descrip").Value)%></td>
<td><%=(rsS.Fields.Item("Title").Value)%></td>
<td><%=(rsS.Fields.Item("Artist").Value)%></td>
<td><%=(rsS.Fields.Item("SortKey").Value)%></td>
<td><%=(rsS.Fields.Item("Label").Value)%></td>
<td><%=(rsS.Fields.Item("Manuf").Value)%></td>
<td><%=(rsS.Fields.Item("TypeProduct").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsS.MoveNext()
Wend
%>

</table>
</body>
</html>
<%
rsS.Close()
Set rsS = Nothing
%>

thanks
 
D

Daniel Crichton

shank wrote on Thu, 12 Mar 2009 10:35:03 -0400:

============================================
Here's the entire page.... I changed Response.Buffer = True, to False.
The script ran a long time, but failed to save the end result.
Click to expand...

I've snipped the code. There are a few possible problems with your code.

(1) you're sending the content as a .xls file, but it's actually HTML. While
this happens to work most of the time with Excel, it can wrong if the HTML
is invalid.

(2) You are not encoding any of the values being read from your data, so any
that contain a < or > will break the HTML formatting. Wrap each value with
Server.HTMLEncode, eg.

<td><%=Server.HTMLEncode(rsS.Fields.Item("Category").Value)%></td>

and do this for the column headings too.


I'd also be tempted to put a Response.Flush inside your loop, just to make
sure that the buffer isn't used.

If you comment out the response headers and run the code for this particular
manufacturer, what do you see in your browser? There might well be an error
message that will give a clue to why it's not working but you can't see it.
At a guess it's the Server.ScriptTimeout setting - for a very large
resultset 4 minutes might not be long enough.

And what is the point of the following variables:

Repeat1__numRows
Repeat1__index
rsS_numRows

You're checking the value of Repeat1__numRows against 0 at each loop
iteration and yet it will never be zero - it's starts at -1 and is reduced
by 1 each loop. You're incrementing Repeat1__index and yet you never do
anything with it, and rsS_numRows is initialised to -1 and then never used
again. There are also some other pieces of code near the top that set
variables but are not used, the one containing varFilename after the
response headers appearing to be a duplicate of earlier code.
 
S

shank

Response.Flush inside your loop<<
Appears to have done the task.
Result was a 7MB excel file in a minute or so.
thanks
 
D

Daniel Crichton

In that case it sounds as though even though you had Response.Buffer = False
it was still buffering the output and 7MB is over the buffer limit. I've got
some pages here that use buffering and I've got that line after settings the
response headers, it might be that simply moving that line would also have
done the trick.

Dan

shank wrote on Thu, 12 Mar 2009 11:58:41 -0400:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

creating a spreadsheet 4
Exporting images to excel? 1
SaveAs excel Dialog and IIS ConnectionTimeOut 0
export to excel issue 2
problem in excel file opened in Internet exploer 2
Exporting to Excel 0
export to excel 1
Adodb.Stream - Problem download big files 10

Members online

Total: 34 (members: 2, guests: 32)
Robots: 337

Forum statistics

Threads
473,769
Messages
2,569,582
Members
45,058
Latest member
QQXCharlot

Latest Threads

Top