Wrox Programmer Forums
|
ASP Pro Code Clinic As of Oct 5, 2005, this forum is now locked. No posts have been deleted. Please use "Classic ASP Professional" at: http://p2p.wrox.com/forum.asp?FORUM_ID=56 for discussions similar to the old ASP Pro Code Clinic or one of the other many remaining ASP and ASP.NET forums here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP Pro Code Clinic section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 16th, 2004, 12:36 PM
Authorized User
 
Join Date: Sep 2003
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default .XLS file saved as cached using FSO

Hey,

I'm not sure what I am doing wrong here, but this is what is happening. Using FSO, I am saving a SQL query as an excel file on my web server. Then, I have created a link on the "response.asp" page so that users can save the file to their local drive. If there already is a file called "results.xls" on the server, fso first deletes that file before creating a new one (This was done because I could overwrite files in Everyone security, but IUser didn't like it.). Everything works great, except this one catch: the file saved on the local drive seems to be some sort of cached copy of a previously created excel file on the server. I have made sure that new and correct excel files are created on the server. In my own testing, deleting my cache doesn't work, but renaming the folder from "downloads" to "download" prevents this caching. Obviously I don't want to have to delete and create the folder every time. I have tried closing/setting my objects to nothing at different points in my code. It doesn't matter if I click on the link and click "save" on the dialog box that opens, or if I left-click and do "save target as". I doesn't matter if the link is full or relative. I'm sure there are other things I have tried, but I can't think of what they are.

Does anyone have any ideas? Below is the code as it stands now.

---

<%
dim fso, txtstream, txtstring, rs, conn

Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "SQLOLEDB;"
conn.Open "server=xxxxxx; database=xxxx; uid=xxxx; password=xxxxx;"

set rs = conn.execute(request.form("txtSQL"))
set fso = server.createobject("scripting.filesystemobject")

if fso.fileexists("d:\xxxx\xxxx\xxxx\xxxx\results.xls ") = true then
fso.deletefile("d:\xxxx\xxxx\xxxx\xxxx\results.xls ")
end if

set txtstream = fso.createtextfile("d:\xxxx\xxxx\xxxx\xxxx\results .xls", true, false)

rs.movefirst
do while not rs.eof
    'txtstream (fso variable) can't write db records, so we need to populate records in a string variable
    txtstring = rs.fields(0).value & " " & rs.fields(1).value & " " & rs.fields(2).value & " " & rs.fields(3).value & " " & rs.fields(4).value & " " & rs.fields(5).value & " " & rs.fields(6).value & " " & rs.fields(7).value & " " & rs.fields(8).value & rs.fields(9).value & " " & rs.fields(10).value
    txtstream.writeline txtstring
    rs.movenext
loop
txtstream.close
set fso = nothing
set txtstream = nothing
set conn = nothing
set rs = nothing
%>
<p>The file has been saved to the server. Please <a href="../../download/results.xls">open it</a> and save it to your hard drive.</p>

 
Old March 16th, 2004, 12:41 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi there,

Would it be possible for you to save the file with a date and timestamp in its name? That would probably work around the problem, because every second of the day you'll end up with a new file name, for example: Results_2004_03_16_17_41_33.xls

Other than that I can't think of anything else, except for caching options in IIS. Not sure about that, though.....

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old March 16th, 2004, 01:02 PM
Authorized User
 
Join Date: Sep 2003
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar --

Thanks for your response.

I've thought about that, would prefer to look into caching options on IIS. Were you thinking of anything specific for me to look at in IIS, or were you just brainstorming?

The thing about your suggestion and my reluctance to pursue it is, I've done this before, without needing to work with time-stamps. So the cleaner I can make the code, the better. I know, I'm stubborn. However I will take your suggestion into consideration.

 
Old March 16th, 2004, 01:09 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

No, IMHO, you're not stubborn. It makes perfect sense what you're saying. It should work, so why bother creating incomprehensible work arounds if it is supposed to work, right?

If you look at the HTTP Headers tab of the Options dialog for your Web application you'll find a Content Expiration Area. Set it to Expire immediately.

This may increase the load of your site (all pages will now expire immediately, including static HTML), so you could decide to apply this setting on your Downloads folder only.

I still find it strange that the old file is used. Is there a huge difference in time / date between your client and your server?

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old March 16th, 2004, 01:24 PM
Authorized User
 
Join Date: Sep 2003
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

IMHO???

(Ima little slow...)

Just tried changing http headers to expire immediately. No dice.

Time difference between client/server is ~ a minute.

Any other brilliant IIS (or other) ideas?

Thanks for your responses.

 
Old March 16th, 2004, 01:29 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

IMHO = In my humble opinion.

How is Internet Explorer configured? Can you reproduce this problem with other browsers? What happens when you sync the time on both machines?

I have found that when I quickly update pages and save them again (within the same minute) the new version is not picked up correctly. Wild guessing here, but it's all I can do at this moment.....

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old March 16th, 2004, 01:47 PM
Authorized User
 
Join Date: Sep 2003
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, it appears your suggestions provided a new discovery.

I went to the server, opened IE, and ran several tests. They all came out correctly, no caching.

I went to another workstation, one I have never tried this before on, and did the same thing. This time, my own workstation's cached copy was saved to the other workstation's local drive.

Which to me implies two things:
1. The server is not caching itself.
2. The server is caching for all the clients.

In other words, the cache is not on the local client machine(s), but on the server, even though the server itself does not recognize the caching.

Now, I'm using the term "caching", but I'm not exactly sure what I am referring to, because the problem hasn't really been identified.

Any idea how I can proceed from here?

 
Old March 16th, 2004, 01:50 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Are you using a proxy or caching server like ISA? Maybe the file gets cached in the middle of the network, between you and the server?

That would make sense, if the server doesn't cache it for himself, but all the other clients do.

What does your Connections dialog in IE say about proxies and stuff like that?

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old March 16th, 2004, 01:58 PM
Authorized User
 
Join Date: Sep 2003
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Good call.

We are using a network proxy, and we are not bypassing it for local addresses.

So you think this might only be a problem for nodes/clients inside out network, but OK outside?

I'll see what kind of settings are configured on the proxy/firewall server.

Firewalls are a love/hate relationship.

 
Old March 16th, 2004, 02:01 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

If it's public, mail me the link and I'll try.

Otherwise, you'll need to test a few machines.

Is "Bypass proxy for local addresses" not an option??

Cheers,

Imar (off now, may be back on later)


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help! how to read the file of *.xls hussar91404 C# 4 April 25th, 2014 03:41 AM
Reading xls file balesh.mind ASP.NET 2.0 Professional 1 August 27th, 2007 11:54 PM
want to create a xls file vinita24 SQL Language 2 March 27th, 2007 11:27 AM
.csv file saved with Excel, issue with date forma pitophe Excel VBA 0 March 25th, 2004 07:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.