Solved

running ASP code from an onclick event

Posted on 2004-10-11
13
420 Views
Last Modified: 2012-06-21
I'm embarrased I don't know the answer to this but...

I'm retrieving data from a SQL table and displaying it as a table in the browser.  Now I want to give the user the option to download the table as an Excel file.

The code for the download is:

Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
Response.ContentType = "application/vnd.ms-excel"

which works OK but how do I call this from a button?

I assume something like:

<INPUT type="button" value="Download this file" onclick="downloadit()">

Do I put the ASP code inside a function downloadit?
Do I create a variable with a value and then run the response commands with an IF...Then?

What's the best way to do this?  Please be explicit, I'm sort of slow today...
0
Comment
Question by:KenAdney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 19

Expert Comment

by:peh803
ID: 12279372
Something like this:
<%
if len(request("downloadit"))>0 then
   '''Put your code here to download the excel file....
end if
%>
<form name="downloadform">
Click the button to download the data in excel format!<BR>
<input type="submit" name="downloadIt">
</form>


Regards,
peh803
0
 
LVL 14

Expert Comment

by:dfu23
ID: 12279404
The function downloadit() is a client side javascript function which will either need to submit the form to an ASP page (usually the same ASP page that it is on but not always the case) or it will need to point the browser to the ASP page that will begin the download.

So maybe,

function downloadit() {
    document.forms[0].action = "myPage.asp?download=1";
    document.forms[0].submit();
}

This way the ASP page can check for the QueryString of download as a switch to begin the download ... or maybe have a separate ASP page:

function downloadit() {
    document.location.href = "download.asp";
}
0
 
LVL 15

Assisted Solution

by:justinbillig
justinbillig earned 100 total points
ID: 12279787
You have three options, the first two being IE Only


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rmscpt/Html/rmscpt.asp

You can use MSRS (Microsoft Remote Script )

Or you can use the MSXML Object to call server methods

or ( Something like dfu23 ) suggest

Open a new window

<a href="download.asp?file=Excel.xls" target="_new" ( or blank can't remember which ) >

then in download.asp you can force them to download the file using ...

http://www.psacake.com/web/if.asp



0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 14

Expert Comment

by:dfu23
ID: 12279860
Actually if you want to do it as a remote script you could use a javascript remote procedure call which doesn't have to be IE specific ...
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12283505
dfu could you elaborate more on what you mean by javascript remote procedure call. A link if you have one also please
0
 
LVL 26

Assisted Solution

by:Rejojohny
Rejojohny earned 100 total points
ID: 12284176
Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
Response.ContentType = "application/vnd.ms-excel"

The above statement tells the browser how to treat the content of page been streamed.

>>I'm retrieving data from a SQL table and displaying it as a table in the browser.  Now I want to give the user the option to download the table as an Excel file.
In ur case the user is shown a page in HTML format of the data retrived from the database .. As I understand, now u want the user to be able to download this content that is shown in the browser . but then the page has already been rendered and so u will not be able to use the "contenttype" code directly. so what u will have to do is submit the page and change the content type Also to use the addheader statment, u expect the file to be physically present, which is not present in ur case ... isn't it?.... So the code will go something like this

<HTML>
<HEAD>
if request.form("butDownload") <> "" then
       Response.ContentType = "application/vnd.ms-excel"
end if
<% code to retrive data from ur database
...........
%>
</HEAD>
<BODY>
... formatting of the data
</BODY>
</HTML>
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 12284188
i forgot to add the download button .....
...... as given above
<BODY>
<Form name="frmDownload" method=post>
     <input type=submit name="butDownload" value="Download">

... formatting of the data
</Form>
</BODY>
</HTML
0
 
LVL 14

Accepted Solution

by:
dfu23 earned 100 total points
ID: 12287163
Javascript Remote Procedure Call

Here's the basic idea of how it works ... you know how you can include Javascript files into a web page with:

<script type="text/javascript" src="myScript.js"></script>

Well, the source can be anything so it could easily be changed to something like:

<script type="text/javascript" src="myScript.asp?func=getDBResults"></script>

With the idea that the ASP can connect to a database or whatever is needed in the back end and return the javascript as the result ... which is then process on the page some how ... there are a bunch of different approaches but this is the main concept behind JS RPC. If you do a Google search on "javascript rpc" you should get a bunch of results back that are quite helpful. There's also a pretty good article on The Code Project which calls ASP.NET functions:

http://www.codeproject.com/aspnet/AlvaroRemoteScripting.asp
0
 
LVL 10

Author Comment

by:KenAdney
ID: 12288287
Thanks everyone, I've upped the points now that I see the complexity of what we're dealing with.

Here's the whole code for the page (I've some comments at the bottom):

<%
Dim pageName
pageName = "exceldownload.asp"
bill_month = Trim(Request.form("bill_month"))

If bill_month = "" THEN
%>
<h4>Select a month to retrieve the information</h4>
<% Call showSelectMonthForm(-6,6) 'Prev-#-Months to Future-#-Months. Use '3,3' for a 7 months span including current month%>
<hr>

<%
ELSE
security()
Dim DataConn
Dim contractor, sub_contractor, count_psa, count_brief_office, count_commed, count_intake_office, count_intake_home, count_app_office, count_app_home, count_group


      'get the contractor name based on the login ID
      openDb()
      Set rs = DataConn.Execute ("SELECT sub_contractor, contractor FROM contractors WHERE userid = '" & loginid &"'")
      sub_contractor = rs("sub_contractor")
      contractor = rs("contractor")
      closeDb()

      'retrieve the data for the contractor and specified bill_month
      openDB()
            sql1 = ("SELECT * FROM sub_bill_new WHERE bill_month = '" & bill_month & "' AND contractor = '" & contractor &"' ORDER BY sub_contractor" )
            Set rs1 = Server.CreateObject("ADODB.Recordset")
            rs1.Open sql1, DataConn, 1, 3

If rs1("bill_month") = "" Then die("There aren't any Activity Reports entered.")             
                  count_psa = rs1("count_psa")
                  count_brief_office = rs1("count_brief_office")
                  count_commed = rs1("count_commed")
                  count_intake_office = rs1("count_intake_office")
                  count_intake_home = rs1("count_intake_home")
                  count_app_office = rs1("count_app_office")
                  count_app_home = rs1("count_app_home")
                  count_group = rs1("count_group")
%>

<%

' create the excel file out of a table of the data
Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
Response.ContentType = "application/vnd.ms-excel"

%>




<table width="90%" border="2" bordercolor="green">
<tr>
<th width="70%"><b>Bill Month</b></th>
<th width="70%"><b>Sub-contractor</b></th>
<th width="15%"><b>Brief Office</b></th>
<th width="15%"><b>Intake Office</b></th>
<th width="15%"><b>Intake Home</b></th>
<th width="15%"><b>App Office</b></th>
<th width="15%"><b>App Home</b></th>
<th width="15%"><b>Group</b></th>
<th width="15%"><b>Comm Ed</b></th>
<th width="15%"><b>PSA</b></th>
</tr>
<%
While Not rs1.EOF
%>
<tr>
<td>'<%=bill_month%></td>
<td><%=sub_contractor%></td>
<td><%=count_brief_office%></td>
<td><%=count_intake_office%></td>
<td><%=count_intake_home%></td>
<td><%=count_app_office%></td>
<td><%=count_app_home%></td>
<td><%=count_group%></td>
<td><%=count_commed%></td>
<td><%=count_psa%></td>
</tr>
<%
rs1.MoveNext
Wend
%>
</table>

<%End If%>

<%
Function showSelectMonthForm(prevMonths,futureMonths)
      ' This function creates a small html form that allows the user to select
      '      the BillingContract month to be displayed.
      ' In turn, the values selected from this form will let us auto-fill much of the
      '      rest of the form on this page.
      ' We will get months from 'prefMonths' months back through 'futureMonths' months ahead of the present month,
      '      this is changes by the parameters set when calling this function
      %>
      <form action="<%= pageName %>" method="post" name="selectMonthForm">
            <select name="bill_month">
            <%
            For i = prevMonths To futureMonths
                  
                  'as we loop through, get the date for each month
                  newDate = DateAdd("M",i,Date)
                  
                  ' the value of 'm' is the MONTH portion of each date
                  m = Month(newDate)

                  'add a zero to the front of the month if it is only one digit
                  If Len(m) < 2 Then m = 0 & m

                  ' the value of 'y' is the YEAR portion of each date, it is probably in a 4-digit format, ie '2004'
                  y = Year(newDate)

                  ' change the 4-digit year to a 2-digit year
                  If Len(y) = 4 Then shortYear = Right(y,2)

                  'add the word "SELECTED" if this is the current month, so the default view will be of the current month
                  If DateDiff("M",newDate,Date) = 0 Then isCurrentMonth = " SELECTED" Else isCurrentMonth = ""

                  'however, if this form has already been used, default to the last selected month, not the current month
                  If Request.form("bill_month") <> "" Then
                        bill_month = Request.form("bill_month") ' is in the form of MM-YY, i.e., '09-04'
                        If DateDiff("M",newDate,bill_month) = 0 Then isCurrentMonth = " SELECTED" Else isCurrentMonth = ""
                  End If

                  'print it all out. Note the 'value' uses the shortYear format, but the long year (y) is displayed for the user.
                  %>
                  <option value="<%= m %>-<%= shortYear %>"<%= isCurrentMonth %>><%= m %>-<%= y %></option>
            <% Next %>
            </select>
            <input type="submit" value="Get data from this billing/contract month">
      </form>
      <%
End Function 'showSelectMonthForm()
      %>

me again...there's some functions in a secured logon script here that you won't see (including the dataconn, openDB, making a cookie of the logonID, etc.).  

it makes sense to me that if the page is already rendered, it can't be downloaded directly.  Also that a form inside a form isn't going to work.  It looks like a javascript RPC to a new page (perhaps saving all the fields as session variables so they can be used again) and this new page doing the download.  Waddya think?
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 12288339
any specific reson for not trying what i suggested .. call the same page when u want to download .. but this time change the contenttype ...
0
 
LVL 10

Author Comment

by:KenAdney
ID: 12288751
Other than me being stupid as a sack full of hammers, no.

Turns out, at least with IE, the content type didn't need to change and a form inside a form worked.

Here's what works (although with the hidden fields, renaming bill_month and using the flag to strip the button out of the Excel file, it's a little messy) but at least it is all ASP:

<%
Dim pageName
pageName = "exceldownload.asp"
Dim flag

Flag=True

If request.QueryString("getit")="1" then
      ' create the excel file out of a table of the data
      Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
      Response.ContentType = "application/vnd.ms-excel"

      bill_month = Request.QueryString("bill_month2")
      

      Flag=False
Else
      bill_month = Request.form("bill_month")
End if



If bill_month = "" THEN
%>
<h4>Select a month to retrieve the information</h4>
<% Call showSelectMonthForm(-6,6) 'Prev-#-Months to Future-#-Months. Use '3,3' for a 7 months span including current month


%>

<hr>

<%
ELSE
security()
Dim DataConn
Dim contractor, sub_contractor, count_psa, count_brief_office, count_commed, count_intake_office, count_intake_home, count_app_office, count_app_home, count_group


      'get the contractor name based on the login ID
      openDb()
      Set rs = DataConn.Execute ("SELECT sub_contractor, contractor FROM contractors WHERE userid = '" & loginid &"'")
      sub_contractor = rs("sub_contractor")
      contractor = rs("contractor")
      closeDb()

      'retrieve the data for the contractor and specified bill_month
      openDB()
            sql1 = ("SELECT * FROM sub_bill_new WHERE bill_month = '" & bill_month & "' AND contractor = '" & contractor &"' ORDER BY sub_contractor" )
            Set rs1 = Server.CreateObject("ADODB.Recordset")
            rs1.Open sql1, DataConn, 1, 3

If bill_month = "" Then die("There aren't any Activity Reports entered.")             
                  count_psa = rs1("count_psa")
                  count_brief_office = rs1("count_brief_office")
                  count_commed = rs1("count_commed")
                  count_intake_office = rs1("count_intake_office")
                  count_intake_home = rs1("count_intake_home")
                  count_app_office = rs1("count_app_office")
                  count_app_home = rs1("count_app_home")
                  count_group = rs1("count_group")
%>

<table width="90%" border="2" bordercolor="green">
<tr>
<th width="70%"><b>Bill Month</b></th>
<th width="70%"><b>Sub-contractor</b></th>
<th width="15%"><b>Brief Office</b></th>
<th width="15%"><b>Intake Office</b></th>
<th width="15%"><b>Intake Home</b></th>
<th width="15%"><b>App Office</b></th>
<th width="15%"><b>App Home</b></th>
<th width="15%"><b>Group</b></th>
<th width="15%"><b>Comm Ed</b></th>
<th width="15%"><b>PSA</b></th>
</tr>
<%
'While Not rs1.EOF
%>
<tr>
<td>'<%=bill_month%></td>
<td><%=sub_contractor%></td>
<td><%=count_brief_office%></td>
<td><%=count_intake_office%></td>
<td><%=count_intake_home%></td>
<td><%=count_app_office%></td>
<td><%=count_app_home%></td>
<td><%=count_group%></td>
<td><%=count_commed%></td>
<td><%=count_psa%></td>
</tr>
<%
'rs1.MoveNext
'Wend
%>
</table>

<%If flag=True Then%>

<form name="downloadform" method="get">
Click the button to download the data in excel format!<BR>
<input type="hidden" name="getit" value="1">
<input type="hidden" name="bill_month2" value="<%=bill_month%>">
<input type="submit" name="downloadIt" value="Download the file">
</form>
<%End If%>

0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 12294974
good .. so that works ...
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12301364
oh you just mean remote scripting ... ok yeah i know htat
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question