Reports in ASP

[ISPrincess]

I have written a simple Intranet system that is fast becoming very popular within the company. I need help!

I display results of sql query's formatted to look like reports on asp pages.

The user's cannot save these pages (at least not formatted correctly) - they cannot email them either.

I would like for them to be able to save them in word or excel and email the results to each other (not a link).

Any ideas / input / references would be greatly appreciated.

Thank you !

 

[BeckahC]

Are you using SQL Server as your database? If so I may have a couple of ideas. BeckahC

 

[ISPrincess]

Yes I am using SQL 7.0

 

[JrClown]

Have the file be written to a PDF file then you can email it.

What I did was create link like "Report" and attached this page to it.


COPY AND PASTE THIS CODE AND MAKE THE PROPER CHANGES WHEN PULLING DATA FROM YOUR DATABASE

*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*--*-*--*-*-*-*-*-*-**
<%@LANGUAGE=&quot;VBSCRIPT&quot;%>
<!--#include file=&quot;Connections/YOUR_DSN.asp&quot; -->
<%

set Recordset1 = Server.CreateObject(&quot;ADODB.Recordset&quot
Recordset1.ActiveConnection = MM_YOUR_DSN_STRING
Recordset1.Source = &quot;SELECT * FROM dbo.TABLE1 WHERE account = '&quot; & session(&quot;MM_Username&quot & &quot;' and password = '&quot; & session(&quot;MM_Password&quot & &quot;'&quot;
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
'response.write recordset1.source


'building the path to the pdf file...............

me = &quot;C OR D:\inetpub\

http://wwwroot\YOUR

SITE\&quot; & Recordset1(&quot;pdf&quot


Response.Buffer = True
response.Expires = 0

Set PDF = Server.CreateObject(&quot;APToolkit.Object&quot
r = PDF.OpenOutputFile(&quot;MEMORY&quot'''''keep this line and everything before it...''''''
u = PDF.MergeFile(me,0,0)
'=================================================
r = PDF.CloseOutputFile()'''''keep this line and everything after it...'''''''
zz = PDF.BinaryImage
response.expires = 0
response.clear
response.ContentType = &quot;application/pdf&quot;
response.AddHeader &quot;Content-Type&quot;, &quot;application/pdf&quot;
response.AddHeader &quot;Content-Disposition&quot;, &quot;inline;filename=test.pdf&quot;
response.BinaryWrite zz
response.End
set APToolKit = nothing

' try this also
%>
<%
Recordset1.Close()
%>
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*--*-*--*-*-*-*-*-*-**

[tt]There are other way of retrieving your recordset from the database and using CDONTs to email your data, but I like this method better[/tt]
Hope it helps...

&quot;The reward of one duty done is the power to fulfill another&quot;
<%

Jr_Clown@Yahoo.com
%>
​

 

[ISPrincess]

What is &quot;me&quot; in your example??

me = &quot;C OR D:\inetpub\

http://wwwroot\YOUR

SITE\&quot; & Recordset1(&quot;pdf&quot

Also, my Interdev app errored on
Set PDF = Server.CreateObject(&quot;APToolkit.Object&quot

Do I need a reference to something in the project? If so, what?

 

[ISPrincess]

We do not own the full-blown version of Adobe.

Any other ideas - maybe any using &quot;Word&quot;?

 

[JeremyG]

One little-known feature of Microsoft Internet explorer is the Web Archive feature.

go to a page and do a File, SaveAs..
For type select &quot;Web Archive, Singlefile(*.MHT)&quot;

This saves a page with all text and graphics included. Pretty cool. Yea i know its not a programatic solution, but it might work for your users. Maybe you could even write some code to do a save as, and then include that file in email.

 

[ISPrincess]

Thank you,

There are 2 problems

1. The page has frames
2. Say I am querying my SQL table for orders between 01/01/02 and 01/02/02. When I opened the .MHT file as you suggested, all it did was link me back to that asp page, and rerun the query and in this case, shows be everything in the DB (because dates were blank).

This puts me back a square one, unless I did something wrong in opening the file.

 

[Guest_imported]

PC -
I suggest creating a link to an excel file you create on your server as the page processes using FileSystemObject.

When your page is done with the display, you will display a link to your excel file that the user can open and also display a link to email that file to an email address input by the user.

There is always a catch however, in that unless you use Office Web Components (not covered here), formatting for the output to the Excel file is non-existent; but with some creative SQL, your result set can be formatted in a very readable fashion.

On your display page, you could create a sub routine such as:

Sub XLFiles()

'Calc date for file
mydt = &quot;&quot;
For x=1 To Len(Date())
if mid(cstr(Date()),x,1) <> &quot;/&quot; then
mydt = mydt & mid(cstr(Date()),x,1)
end if
Next

'Calc time for file
mytm = &quot;&quot;
For x=1 To (Len(Time())-3)
if mid(cstr(Time()),x,1) <> &quot;:&quot; then
mytm = mytm & mid(cstr(Time()),x,1)
end if
Next

nRandom = mydt & &quot;-&quot; & mytm
filePath = Server.mapPath(&quot;\XLFiles&quot
----> XLFiles will be a directory off your Web server root

Response.Write &quot;<h3 style=&quot;&quot;color: blue&quot;&quot;>&quot; + &quot;Data File Download - ResultsName<br><small>This will take a moment to generate... &quot; _
& &quot;When Excel opens - <br> &quot; _
& &quot;1) From the top menu, choose File/Save As <br>&quot; _
& &quot;2) In the Save In entry box: choose a directory on your PC<br>&quot; _
& &quot;3) In the File Name entry box: enter any name you want<br>&quot; _
& &quot;4) In the Save As Type entry box: select Microsoft Excel</small></h3><p>&quot;

rst.open dtl, CommConn, 3, 1
----> rst = Recordset already returned from your ASP code

fileExcel = &quot;FileName-&quot; & CStr(nRandom) & &quot;.xls&quot;
filename = filePath & &quot;\&quot; & fileExcel

Set fs = Server.CreateObject(&quot;Scripting.FileSystemObject&quot
Set MyFile = fs.CreateTextFile(filename, True)

MyFile.writeline &quot;TITLE&quot;
MyFile.writeline &quot; &quot;

strLine=&quot;&quot;
For each x in rst.fields
strLine= strLine & x.name & chr(9)
Next
MyFile.writeline strLine

Do while Not rst.EOF
strLine=&quot;&quot;
For each x in rst.Fields
strLine= strLine & x.value & chr(9)
Next
MyFile.writeline strLine
rst.MoveNext
Loop

'Clean up
rst.close
MyFile.Close
set rst=nothing
Set MyFile=Nothing
set fs=Nothing

'Show a link to the Excel File
link=&quot;<A HREF=&quot; & &quot;\XLFiles\&quot; & fileExcel & &quot; target='1'>Download The XL Data</a><p><br>&quot;
Response.write link + &quot;<br><br>&quot;
'Show a link to the Email page
link = &quot;<A HREF=&quot; & &quot;\EmailXL.asp?fnm=&quot; & fileExcel & &quot; target='1'>Email The XL Data</a><p><br>&quot;
Response.write link

End Sub


Call this sub after you have your recordset.
The email portion would involve passing the XL Filename via another Link - that link displaying another ASP page that would take an email address and have a &quot;send&quot; button to send the email. The code for the link is above already and will pass the XL filename via a querystring.

I'll leave it to you to write the email address input asp page. Creating and sending the email is a piece of cake with CDONTS. From your email address input screen, you can post to another asp page or just post to the same page - from there you need to pass the XL filename received originally in the querystring (request.querystring(&quot;fnm&quot)and the email address input by the user.

Here is sample code that could send an email:

set htmlcdont = server.createobject(&quot;CDONTS.NewMail&quot
htmlcdont.From = &quot;someone@somewhere&quot;
htmlcdont.To = &quot;someone@somewhere&quot;
htmlcdont.Cc = &quot;someone@somewhere&quot;
htmlcdont.Subject = &quot;XL data file&quot;
htmlcdont.AttachFile&quot;d:\

http://wwwroot\inetpub\XLFiles\XLFile.xls&quot;)

---> you need to use the physical path/filename
htmlcdont.BodyFormat = 0
htmlcdont.MailFormat = 0
htmlcdont.Body = &quot;Find the XL file attached&quot;
htmlcdont.Send
set htmlcdont = nothing


Hope this helps

 

[BeckahC]

Looks like my suggestions were already covered! BeckahC

 

[ISPrincess]

Mr. J - You rock!