an ASP automation question

Steven Scaife · Jan 4, 2006

Hello

I am creating a reporting system using SQL Server 2000 and ASP, I have
created 4 pages that display the results i want, however the reports take an
average of 20 mins to run and i have been asked to automate them, i decided
to create another ASP page that uses the filesystemobject to create a static
htm file from the main page.

I decided to put all the declarations and includes my main page creation
page, and stripped these from the asp file so i was left with just the body
of the page, i read the body of the page into a variable and tried to do a
replace on the <%= and the %>

htmFile = replace(htmFile, "<%=", " chr(34) &")
htmFile = replace(htmFile, "%>", " & chr(34)")

I also tried doubling the quotes instead of using chr(34) but with either of
them i get unterminated string constant. I want to be able to just read in
the body of the page replacing any <%=%> so that it scales better. I have 4
reports at the moment but this will grow to any number that the business
wants.

As you can see from my code below a solution i have found is to copy the
body text into a variable and escape the string for the insertion of
variables, but as more and more reports get added the messier its going to
get

hopefully i made sense so if anyone knows how to replace the asp tags or a
way of achieving what i want to do i would appreciate the help

almost forgot the reason i am wanting to replace the asp tags is because
when it creates the page it doesn't show any values

my code is as follows

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% option explicit %>

<%
response.expires = "-1000"

'this declaration belongs to the callcentre report
dim dyTotalAppsWrit, dyTotalValueOfApps, dyTotalSourced, dyTotalSumSourced,
dySrceVol, dySrceVal
dim wkTotalAppsWrit, wkTotalValueOfApps, wkTotalSourced, wkTotalSumSourced,
wkSrceVol, wkSrceVal
dim mnTotalAppsWrit, mnTotalValueOfApps, mnTotalSourced, mnTotalSumSourced,
mnSrceVol, mnSrceVal
dim qtTotalAppsWrit, qtTotalValueOfApps, qtTotalSourced, qtTotalSumSourced,
qtSrceVol, qtSrceVal
dim yrTotalAppsWrit, yrTotalValueOfApps, yrTotalSourced, yrTotalSumSourced,
yrSrceVol, yrSrceVal

'this declaration belongs to the LCT BAG Report
dim dyTotalBooked, dyBookingValue, dyBookingFees, dyVisitsVolume,
dyVisitsValue, dyVisitsFees, dySignVolume, dySignValue, dySignFees
dim wkTotalBooked, wkBookingValue, wkBookingFees, wkVisitsVolume,
wkVisitsValue, wkVisitsFees, wkSignVolume, wkSignValue, wkSignFees
dim mnTotalBooked, mnBookingValue, mnBookingFees, mnVisitsVolume,
mnVisitsValue, mnVisitsFees, mnSignVolume, mnSignValue, mnSignFees
dim qtTotalBooked, qtBookingValue, qtBookingFees, qtVisitsVolume,
qtVisitsValue, qtVisitsFees, qtSignVolume, qtSignValue, qtSignFees
dim yrTotalBooked, yrBookingValue, yrBookingFees, yrVisitsVolume,
yrVisitsValue, yrVisitsFees, yrSignVolume, yrSignValue, yrSignFees

'these are needed for the page to function
dim PageTop, CCBagPage, LCTBagPage
%>



<%
SetUpPages "Call Centre - BAG Report"
GettheValuesCCBag

CCBagPage = "<table width='950' border='1' cellspacing='0' cellpadding='0'
align='center' style='border-collapse:collapse;'><tr> <td>Call Centre </td> <td>Applications Written Volumes </td> <td>Applications Written Value </td> <td>Applications Sourced Volume </td> <td>%</td> <td>Applications
Sourced Value </td> <td>%</td></tr><tr>
<td>Daily</td> <td>" &
dyTotalAppsWrit & "</td><td>" &
formatcurrency(dyTotalValueOfApps) & "</td> <td>" & dyTotalSourced & "</td> <td width='30px'>" &
dySrceVol & "</td> <td>" &
formatcurrency(dyTotalSumSourced) & "</td> <td width='30px'>" & dySrceVal & "</td></tr><tr> <td>Week To Date </td> <td>" &
wkTotalAppsWrit & "</td> <td>" &
formatcurrency(wkTotalValueOfApps) & "</td> <td>" & wkTotalSourced & "</td> <td width='30px'>" &
wkSrceVol & "</td> <td>" &
formatcurrency(wkTotalSumSourced) & "</td> <td width='30px'>" & wkSrceVal & "</td></tr><tr> <td>Month To Date </td> <td>" &
mnTotalAppsWrit & "</td> <td>" &
formatcurrency(mnTotalValueOfApps) & "</td> <td>" & mnTotalSourced & "</td> <td width='30px'>" &
mnSrceVol & "</td> <td>" &
formatcurrency(mnTotalSumSourced) & "</td> <td width='30px'>" & mnSrceVal & "</td></tr><tr> <td>Quarter To Date </td> <td>" &
qtTotalAppsWrit & "</td> <td>" &
formatcurrency(qtTotalValueOfApps) & "</td> <td>" & qtTotalSourced & "</td> <td width='30px'>" &
qtSrceVol & "</td> <td>" &
formatcurrency(qtTotalSumSourced) & "</td> <td width='30px'>" & qtSrceVal & "</td></tr><tr> <td>Year To Date </td> <td>" &
yrTotalAppsWrit & "</td> <td>" &
formatcurrency(yrTotalValueOfApps) & "</td> <td>" & yrTotalSourced & "</td> <td width='30px'>" &
yrSrceVol & "</td> <td>" &
formatcurrency(yrTotalSumSourced) & "</td> <td width='30px'>" & yrSrceVal & "</td></tr></table> <table
width='950' border='1' cellspacing='0' cellpadding='0' align='center'
style='border-collapse:collapse;'><tr> <td width='166'>Call Centre </td> <td width='166'>Dead - Agent Error Volume </td> <td
width='30'>%</td> <td width='166'>Dead - Agent Error
Value </td> <td width='30'>%</td> <td
width='166'>Dead - Out of Criteria Volume</td>
<td width='30'>%</td> <td width='166'>Dead- Out of Criteria Value </td>
<td width='30'>%</td></tr><tr> <td>Daily</td>
<td></td> <td></td> <td></td> <td
width='40'> </td> <td></td> <td
width='24'> </td> <td width='159'> </td> <td width='43'></td></tr><tr> <td>Week To
Date </td> <td></td> <td></td> <td></td>
<td></td> <td></td> <td> </td> <td> </td> <td></td></tr><tr> <td>Month To
Date </td> <td></td> <td></td> <td></td>
<td></td> <td></td> <td> </td> <td> </td> <td></td></tr><tr> <td>Quarter To
Date </td> <td></td> <td></td> <td></td>
<td></td> <td></td> <td> </td> <td> </td> <td></td></tr><tr> <td>Year To
Date </td> <td></td> <td></td> <td></td>
<td></td> <td></td> <td> </td> <td> </td> <td></td></tr></table></body></html>"

If makeHtmFile(CCBagPage) Then
response.write "File created Successfully"
Else
response.write "File creation failed : " & err.description
end if
%>

<%
Sub SetUpPages(pageTitle)
PageTop = "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN'
'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'><html
xmlns='http://www.w3.org/1999/xhtml'><head><meta http-equiv='Content-Type'
content='text/html; charset=iso-8859-1' />" & _
"<title>" & pageTitle & "</title><link href='styles/style.css'
rel='stylesheet' type='text/css' /></head><body><table width='950'
border='1' cellspacing='0' cellpadding='0' align='center'
style='border-collapse:collapse;'><tr><td colspan='3'><img
src='images/logo.png' /></td></tr> <tr><td colspan='3'><div
align='center'><img src='images/menubar.png' alt='earth logo' width='830'
height='30' /></div></td> </tr> <tr><td colspan='3'> </td></tr>
<tr> <td>Earth Mortgages - Business at a glance -
report</td> <td>Date</td> <td> " &
formatdatetime(now(), vblongdate) & "</td> </tr> <tr><td
colspan='3'> </td></tr></table> "
End Sub
%>

<%
Function makeHTMFile(pageBody)

dim htmFile, fso, ts, htmFileName

'set the filename of the htmfile in the format yyyy-mm-dd-ReportTitle
htmFileName = server.MapPath(".") & "\automation\" & year(date) & "-" &
month(date) & "-" & day(date) & "-CallCentreBAG.htm"

set fso = Server.CreateObject("Scripting.FileSystemObject") 'instantiate the
object
'Set ts = fs

penTextFile(server.MapPath(".") & "\indextest.asp", 1)
htmFile = pageTop & pageBody 'ts.readall

response.write htmFile 'for debugging purposes

Set ts = fso.CreateTextFile(htmFileName, True) 'create a textfile using the
fso object

ts.write htmFile
ts.close

set fso = nothing

If err.number = 0 Then
makeHTMFile = True
Else
makeHTMFile = False
End If

End Function

%>

Steven Scaife · Jan 4, 2006

After messing with it for a while i have found out that it is replacing the
<%= and the " that is now there is causing the problem when i try and
replace the %>

I have tried replace(replace(htmFile, "<%=", chr(34) & " & "), "%>", " & " &
chr(34)) but this throws up the same error

htmFile = replace(replace(htmFile, "<%=", chr(34) & " & "), "
-------------------------------------------------------------^any ideas ?

Patrice · Jan 4, 2006

I'm not sure what you are trying to do...

Possible solution I see are :
- use MyFile.Write instead of Response.Write so that the output goes to a
file
- use ServerXMLHttp to request this page so that you can get and grab this
HTML code

You may want also to invest some time into seeing why it is so long. In
particular note that if most of the time is spent in downloading a 100 Mb
report file, it won't change much when you'll try to download a 100 Mb
static file...

Good luck.

--
Patrice

Steven Scaife said:
Hello

I am creating a reporting system using SQL Server 2000 and ASP, I have
created 4 pages that display the results i want, however the reports take an
average of 20 mins to run and i have been asked to automate them, i decided
to create another ASP page that uses the filesystemobject to create a static
htm file from the main page.

I decided to put all the declarations and includes my main page creation
page, and stripped these from the asp file so i was left with just the body
of the page, i read the body of the page into a variable and tried to do a
replace on the <%= and the %>

htmFile = replace(htmFile, "<%=", " chr(34) &")
htmFile = replace(htmFile, "%>", " & chr(34)")

I also tried doubling the quotes instead of using chr(34) but with either of
them i get unterminated string constant. I want to be able to just read in
the body of the page replacing any <%=%> so that it scales better. I have 4
reports at the moment but this will grow to any number that the business
wants.

As you can see from my code below a solution i have found is to copy the
body text into a variable and escape the string for the insertion of
variables, but as more and more reports get added the messier its going to
get

hopefully i made sense so if anyone knows how to replace the asp tags or a
way of achieving what i want to do i would appreciate the help

almost forgot the reason i am wanting to replace the asp tags is because
when it creates the page it doesn't show any values

my code is as follows

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% option explicit %>

<%
response.expires = "-1000"

'this declaration belongs to the callcentre report
dim dyTotalAppsWrit, dyTotalValueOfApps, dyTotalSourced, dyTotalSumSourced,
dySrceVol, dySrceVal
dim wkTotalAppsWrit, wkTotalValueOfApps, wkTotalSourced, wkTotalSumSourced,
wkSrceVol, wkSrceVal
dim mnTotalAppsWrit, mnTotalValueOfApps, mnTotalSourced, mnTotalSumSourced,
mnSrceVol, mnSrceVal
dim qtTotalAppsWrit, qtTotalValueOfApps, qtTotalSourced, qtTotalSumSourced,
qtSrceVol, qtSrceVal
dim yrTotalAppsWrit, yrTotalValueOfApps, yrTotalSourced, yrTotalSumSourced,
yrSrceVol, yrSrceVal

'this declaration belongs to the LCT BAG Report
dim dyTotalBooked, dyBookingValue, dyBookingFees, dyVisitsVolume,
dyVisitsValue, dyVisitsFees, dySignVolume, dySignValue, dySignFees
dim wkTotalBooked, wkBookingValue, wkBookingFees, wkVisitsVolume,
wkVisitsValue, wkVisitsFees, wkSignVolume, wkSignValue, wkSignFees
dim mnTotalBooked, mnBookingValue, mnBookingFees, mnVisitsVolume,
mnVisitsValue, mnVisitsFees, mnSignVolume, mnSignValue, mnSignFees
dim qtTotalBooked, qtBookingValue, qtBookingFees, qtVisitsVolume,
qtVisitsValue, qtVisitsFees, qtSignVolume, qtSignValue, qtSignFees
dim yrTotalBooked, yrBookingValue, yrBookingFees, yrVisitsVolume,
yrVisitsValue, yrVisitsFees, yrSignVolume, yrSignValue, yrSignFees

'these are needed for the page to function
dim PageTop, CCBagPage, LCTBagPage
%>



<%
SetUpPages "Call Centre - BAG Report"
GettheValuesCCBag

CCBagPage = "<table width='950' border='1' cellspacing='0' cellpadding='0'
align='center' style='border-collapse:collapse;'><tr> <td>Call Centre </td> <td>Applications Written Volumes </td> <td>Applications Written Value </td> <td>Applications Sourced Volume </td> <td>%</td> <td>Applications
Sourced Value </td> <td><span

class='style13'>% said:
<td>Daily</td> <td>" &
dyTotalAppsWrit & "</td><td>" &
formatcurrency(dyTotalValueOfApps) & "</td> <td>" & dyTotalSourced & "</td> <td width='30px'>" &
dySrceVol & "</td> <td>" &
formatcurrency(dyTotalSumSourced) & "</td> <td width='30px'>" & dySrceVal & "</td></tr><tr> <td>Week To Date </td> <td>" &
wkTotalAppsWrit & "</td> <td>" &
formatcurrency(wkTotalValueOfApps) & "</td> <td>" & wkTotalSourced & "</td> <td width='30px'>" &
wkSrceVol & "</td> <td>" &
formatcurrency(wkTotalSumSourced) & "</td> <td width='30px'>" & wkSrceVal & "</td></tr><tr> <td>Month To Date </td> <td>" &
mnTotalAppsWrit & "</td> <td>" &
formatcurrency(mnTotalValueOfApps) & "</td> <td>" & mnTotalSourced & "</td> <td width='30px'>" &
mnSrceVol & "</td> <td>" &
formatcurrency(mnTotalSumSourced) & "</td> <td width='30px'>" & mnSrceVal & "</td></tr><tr> <td>Quarter To Date </td> <td>" &
qtTotalAppsWrit & "</td> <td>" &
formatcurrency(qtTotalValueOfApps) & "</td> <td>" & qtTotalSourced & "</td> <td width='30px'>" &
qtSrceVol & "</td> <td>" &
formatcurrency(qtTotalSumSourced) & "</td> <td width='30px'>" & qtSrceVal & "</td></tr><tr> <td>Year To Date </td> <td>" &
yrTotalAppsWrit & "</td> <td>" &
formatcurrency(yrTotalValueOfApps) & "</td> <td>" & yrTotalSourced & "</td> <td width='30px'>" &
yrSrceVol & "</td> <td>" &
formatcurrency(yrTotalSumSourced) & "</td> <td width='30px'>" & yrSrceVal & "</td></tr></table> <table
width='950' border='1' cellspacing='0' cellpadding='0' align='center'
style='border-collapse:collapse;'><tr> <td width='166'>Call Centre </td> <td width='166'>Dead - Agent Error Volume </td> <td
width='30'>%</td> <td width='166'>Dead - Agent Error
Value </td> <td width='30'>%</td> <td
width='166'>Dead - Out of Criteria

Steven Scaife · Jan 4, 2006

I have built a few pages that show an overall picture of how a department is
performing, currently there are 4 of these for the 4 different areas of the
business.

In a few weeks the business will ask for more detailed reports of these
departments and in 6 months time i expect to have built around 30 or so
reports. By the end of january i have to automate the reports so when the
directors enter the business in a morning they have a static html page to
view of the reports accurate to the day before. I have all the queries in
place and the pages function, however they dont want to wait 20 or so mins
for the reports to run. (the reports show daily, weekly, monthly, quarterly
and yearly all in the same report) So i decided to create a page that using
a loop or something creates the static page with the data within them. I
can create the htm page no problem it has a page title the only thing
missing is the information. Because i have built the pages i dont want to
go through and manually replace all the <%= and %> tags with " & and & ".
It is likely that i will build every page first to test that it works all i
want to do then is pass it through to the page that will build a static htm
file.

Hope this makes sense so far.

I have no problem reading the asp file using the fso object into a variable,
everything seems to be in order except for the fact that my asp page is
littered with <%= %> anything inside of these tags doesnt display so i need
to replace them that way my page variable will have the body text of the
page and the values from my recordset.

I can go through and do a find and replace on all the tags but i would
prefer to not do that and have the system totally free from that. I create
the page and then drop it into the directory that holds them all, and using
a loop i pass every page into the function that creates a static copy of
them.

Hope this makes sense, if what i want cant be done then i'll have to
manually find and replace

cheers

Patrice · Jan 4, 2006

Basically it looks like to me you are looking for something like :

htmlFile=Replace("<%=MyNumber%>",MyRealNumber) ? Is this what you mean ?
Bascially you are trying to "run" an ASP page yourself instead of letting
ASP to do the job ?

Depending how your pages are at this time I would really make the plunge to
use a principle such as :

obj.Write

Then at the top if obj is a file, it writes to a file, if it si response, it
writes to the browser allowing quite good flexibility. IMO it would be
quicker on the mean term than trying to let your current pages unchanged (as
you'll keep having all ASP capabilities).

Also I'm quite suspicious about this time. In particular not that if the
report is huge it could be a download file in whihc case you likely won't
gain much from creating the static file.

Sorry for the poor help.

Else shows a small code that shows what you want...

strHtml="<body><%=MyValue%></body>"
strHtml=Replace(strHtml,"<%=MyValue%>",RealValue)
Response.Write strHtml

Larry Bud · Jan 4, 2006

Steven said:
Hello

I am creating a reporting system using SQL Server 2000 and ASP, I have
created 4 pages that display the results i want, however the reports take an
average of 20 mins to run and i have been asked to automate them, i decided
to create another ASP page that uses the filesystemobject to create a static
htm file from the main page.

I would ask myself a couple of things before you try to accomplish your
task:

Why does the report take 20 minutes to run? Are you using a bunch of
SQL views? If so, convert the query to a stored procedure or stored
function and see how long it takes.

Now, if the SP still takes long to run and you still want to create a
snapshot of the data, use SQL to run the automated task query, and have
it dump the results into a flat table. Update it as often as you wish.
Then use ASP to simply retrieve the contents of the flat table.

If you still think there are too many rows to retrieve via ASP and
STILL want a static HTM file, I would still use SQL to create the HTML
file and take ASP out the equation all together.

You're reengineering the wheel in what you're trying to do.

Paxton · Jan 5, 2006

Steven said:
Hello

I am creating a reporting system using SQL Server 2000 and ASP, I have
created 4 pages that display the results i want, however the reports take an
average of 20 mins to run and i have been asked to automate them, i decided
to create another ASP page that uses the filesystemobject to create a static
htm file from the main page.

I decided to put all the declarations and includes my main page creation
page, and stripped these from the asp file so i was left with just the body
of the page, i read the body of the page into a variable and tried to do a
replace on the <%= and the %>

htmFile = replace(htmFile, "<%=", " chr(34) &")
htmFile = replace(htmFile, "%>", " & chr(34)")

I also tried doubling the quotes instead of using chr(34) but with either of
them i get unterminated string constant. I want to be able to just read in
the body of the page replacing any <%=%> so that it scales better. I have 4
reports at the moment but this will grow to any number that the business
wants.

As you can see from my code below a solution i have found is to copy the
body text into a variable and escape the string for the insertion of
variables, but as more and more reports get added the messier its going to
get

hopefully i made sense so if anyone knows how to replace the asp tags or a
way of achieving what i want to do i would appreciate the help

almost forgot the reason i am wanting to replace the asp tags is because
when it creates the page it doesn't show any values

my code is as follows

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% option explicit %>

<%
response.expires = "-1000"

'this declaration belongs to the callcentre report
dim dyTotalAppsWrit, dyTotalValueOfApps, dyTotalSourced, dyTotalSumSourced,
dySrceVol, dySrceVal
dim wkTotalAppsWrit, wkTotalValueOfApps, wkTotalSourced, wkTotalSumSourced,
wkSrceVol, wkSrceVal
dim mnTotalAppsWrit, mnTotalValueOfApps, mnTotalSourced, mnTotalSumSourced,
mnSrceVol, mnSrceVal
dim qtTotalAppsWrit, qtTotalValueOfApps, qtTotalSourced, qtTotalSumSourced,
qtSrceVol, qtSrceVal
dim yrTotalAppsWrit, yrTotalValueOfApps, yrTotalSourced, yrTotalSumSourced,
yrSrceVol, yrSrceVal

'this declaration belongs to the LCT BAG Report
dim dyTotalBooked, dyBookingValue, dyBookingFees, dyVisitsVolume,
dyVisitsValue, dyVisitsFees, dySignVolume, dySignValue, dySignFees
dim wkTotalBooked, wkBookingValue, wkBookingFees, wkVisitsVolume,
wkVisitsValue, wkVisitsFees, wkSignVolume, wkSignValue, wkSignFees
dim mnTotalBooked, mnBookingValue, mnBookingFees, mnVisitsVolume,
mnVisitsValue, mnVisitsFees, mnSignVolume, mnSignValue, mnSignFees
dim qtTotalBooked, qtBookingValue, qtBookingFees, qtVisitsVolume,
qtVisitsValue, qtVisitsFees, qtSignVolume, qtSignValue, qtSignFees
dim yrTotalBooked, yrBookingValue, yrBookingFees, yrVisitsVolume,
yrVisitsValue, yrVisitsFees, yrSignVolume, yrSignValue, yrSignFees

'these are needed for the page to function
dim PageTop, CCBagPage, LCTBagPage
%>

<snip>

Not an answer to your original question, but you really shouldn't keep
database connection information in .inc files. Unless you have set your
webserver to process them as asp files, they download as text files.
You may just as well post your database details and passwords to
alt.crackers.

/P.

Steven Scaife · Jan 5, 2006

<snip>

Not an answer to your original question, but you really shouldn't keep
database connection information in .inc files. Unless you have set your
webserver to process them as asp files, they download as text files.
You may just as well post your database details and passwords to
alt.crackers.

/P.

This i overlooked

its the way i've always done it / learnt it but in my
defence its only ever going to be used internally on the company intranet
and there are only 6 IT staff of which only 1 person bar myself would
possibly know this. If it went externally then i probably would have a
problem.

Thanks for the info though will definitely need this as i am building the
company website that is going to be database driven. As all the page data
will be pulled from the db i can do without some one changing the pages.

Kyle Peterson · Jan 5, 2006

just some info:
pretty sure in iis6 they changed things so it does not matter...
..inc run from browser will not show code.. just http error

I still use .asp for them myself

Can someone tell me if this a real tracker? Or is it one designed to show you a different message at certain times, ie. acting like one?	0	Jan 10, 2021
Sort by number of characters	1	Nov 2, 2023
Angularjs newbie - second JSON datasource does not display	0	May 18, 2022
Nested Loop Insert Page Break	1	Nov 5, 2021
Filter table rows based on multiple checkboxes value	2	Jan 13, 2023
Having trouble centering contents of td ?	3	May 2, 2023
Image shifts to the right when export the page to pdf	4	May 5, 2023
Help needed with thank you message	5	Sep 11, 2021

an ASP automation question

Steven Scaife

Steven Scaife

Patrice

Steven Scaife

Patrice

Larry Bud

Paxton

Steven Scaife

Kyle Peterson

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads