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.
| Author |
Topic |
|
icecube
Starting Member
15 Posts |
 Posted - 2003-11-20 : 09:16:05
|
| Two questions from an asp-dummy....I am using the filesystemobject to create an Excel file on the server based on an sql-query, in c:\stats\stats.xls. The sql-query is based on an html-form that the user fills out (specifying dates, customer, etc.). Problem is that when user A accesses the file and has the file open in his browser and user B runs a question, the system tries to over-write the file, which is already open in user A's browser - and user B gets an error screen specifying permission denied.....I didn't want to create a new file for every query since the server would eventually run out of disk space. How can I solve this in the best way?Also, it seems that the asp-page that creates the question sometimes doesn't recompile the question, but uses a previously created question....Sample of my coding:Set cn = Server.CreateObject("ADODB.Connection")cn.Open "DRIVER=SQL ....."set rs = Server.CreateObject("ADODB.Recordset").....sql = sql & "order by " & Request.Form("customerid")rs.open sql,cnset fso = createobject("scripting.filesystemobject")Set act = fso.CreateTextFile(filepath)act.WriteLine("<html><head><link rel=""stylesheet"" href=""..\..\style.css""><body>").....act.closeSet act = Nothingrs.CloseSet rs = Nothingcn.CloseSet cn = Nothing%><html><head><link rel="stylesheet" href="style.css"><br><body><a href="<%response.write(filepath)%>"><B>Access file</a>.... |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-11-20 : 11:18:29
|
| What exactly do you use the Excel file for? Maybe we can come up with a better solution that just gets around this problem.What you might need to do is use a dynamic file name for each query (like guid.xls) and that would get around your problem. If user A needs to see what user B is doing, the multiple files thing will not work for you.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-11-20 : 12:54:03
|
| Why go to the effort of turning it into an Excel file? Why not just bring it back as an HTML table? Or use some other ASP object, but don't actually write a file to your server.--------------------------------------------------------Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url] |
|
|
|
icecube
Starting Member
15 Posts |
Posted - 2003-11-20 : 14:39:51
|
| Thank you gentlemen and I totally agree with you, but the client specified that they needed it in Excel. However, the dynamic file name might work. How do I do that? Sorry for my stupidity, but what is guid.xls?Isac |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-11-20 : 15:02:22
|
Ice, basically what I was saying is create a random filename. You could use the Random() function in ASP, and that will give you a semi random number. Then, when you save your file, you use that number as the filename (12345.xls). For the GUID, you can generate it on the SQL side of things with SELECT NewID() That would give you back a GUID that you can use in the filename, and that would look like:6D36CEDC-2844-4BBC-A1A1-FC0A62129DDB.xlsIf you want to do it on the ASP side, you'll need to write a COM object that handles it. I've got one that will do it, but I can't distrubte it. It's only about 5 lines of code, and I can tell you want those are if you want to create the COM object.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
icecube
Starting Member
15 Posts |
Posted - 2003-11-20 : 15:31:08
|
| Thank you Michael - I got it! I think... Otherwise, hope I can come back.Have a great day.Isac |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-11-20 : 16:22:11
|
| I'm subscribed to this topic, so if you need more help, just post in here.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
Need help.....