Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems exporting data to Excel....

Status
Not open for further replies.
golyg

golyg

Programmer
Joined
Jul 22, 2002
Messages
319
Location
US
I have a report that the user can view either on a page with HTML or they can open it in Excel.

I am attempting to change the content type with this statement:

response.contentType = "application/vnd.ms-excel"


But it appears that when I do this I lose all my request variables and my recordset is null.
I know that the recordset is not null...


any ideas?

thanks...
 
Sort by date Sort by votes
Thanks for the feedback...
this is a page which the user can select an organization, a date and how they want to view the report..(web/excel), after they select the criteria and select "Begin Search" the post method calls the same page but checks a variable, strTStatus to determine how to display the page...here is some code, thanks again...

Code: 
<% 

If Not IsEmpty(Request("tstatus")) Then
	strTStatus = Trim(Request("tstatus"))
Else
	strTStatus = "filter"
End If

Dim rptType
'Response.Write request("rpt_output") & "<BR>"
if request("rpt_output")  = "Web" then
	rptType = "Web"
end if
if request("rpt_output") = "Excel" then
	rptType = "Excel"
	response.contentType = "application/vnd.ms-excel"
end if

Session("odv_wtext") = ""
Session("odv_displayview") = "list"
Session("odv_displayequipment_charges") = ""%>




		<% Response.Write "<FORM ACTION=dm_ar_dsAction.asp METHOD=""POST"">" %>
		<table WIDTH="100%" CELLSPACING="0" CELLPADDING="0" BORDER="0">
			<tr>
			<th WIDTH="5%" ROWSPAN="2" VALIGN="top"><img SRC="images/noback.gif" ALT="Do Not Use Your Browser Back Button While Using This Application." BORDER="0"></th>
			<th NOWRAP BGCOLOR="#6633FF" ALIGN="Left">

		<%
		If strTStatus = "query" Then
			Response.write "<FONT SIZE=6>&nbsp;Discrepancy Summary Listing Search Results</FONT>"
		Else
			Response.write "<FONT SIZE=6>&nbsp;Search Discrepancy Summary Listing</FONT>"
		End if
		%>
			</th>
			</tr>
			<tr>
				<td NOWRAP BGCOLOR="#6633FF" VALIGN="top" ALIGN="Right" WIDTH="100%">

<%End If%>

		<%If strTStatus = "query" Then
			Response.write "&nbsp;<INPUT TYPE='HIDDEN' NAME='page' VALUE='" & strTStatus & "'>&nbsp;"
			Response.write "&nbsp;<INPUT TYPE='SUBMIT' NAME='DataAction' VALUE='New Search'>&nbsp;"
		End if

		Response.write "&nbsp;<INPUT TYPE='SUBMIT' NAME='DataAction' VALUE='AC Menu'>&nbsp;"
		%>
				</td>
			</tr>
		</table>
		</form>


<%
Select Case strTStatus
	Case "filter" %>

	<h4 ALIGN="center">Discrepancy Summary Listing by Budget Center</h4>
	<font SIZE="2" COLOR="navy"><strong>Enter Search Criteria and Click the Begin Search button.</strong></font>
	<!--<form ACTION="dm_ar_ds_rpt.asp?rptType=<%=rpt_out%>" METHOD="POST">-->
	<form ACTION="dm_ar_ds.asp" METHOD="POST" id=form1 name=form1>
	<table WIDTH="100%" CELLSPACING="2" CELLPADDING="1" BORDER="0">
		<tr>
			<% 
			'START-Discrepancy Type Lookup------------------------------------------------
			Dim qry_type
			set conn = Session("DBConn")
			tsql = "SELECT DISTINCT type, type FROM tblDiscrepancySummary ORDER BY type"
			Set rsqry_type = Conn.Execute(tsql)
		'		Response.Write tsql
			qry_type = Null
			On Error Resume Next
			qry_type = rsqry_type.GetRows()
			set rsqry_type = Nothing
			%>
	
			<td WIDTH="15%"><strong>Discrepancy Type:</strong></td>
			<td WIDTH="85%"><select NAME="tType" SIZE="1">
				<option SELECTED VALUE></option>
				<%		' Loop thru the rows in the array
				Do
					If isnull(UBound(qry_type, 2)) then
						Response.Write "</SELECT>"
						Exit Do
					End If
					For intRow = 0 to UBound(qry_type, 2)
						Response.Write "<OPTION VALUE=" & QuotedString(qry_type(0, intRow))
	        			Response.Write ">"
						Response.Write ConvertNull(qry_type(1, intRow))  & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"
						Response.Write "</OPTION>"
					Next
						Response.Write "</SELECT>"
					Exit Do
				Loop
				'END-Discrepancy Type Lookup------------------------------------------------
				%>
			</td>
		</tr>
		<tr>
			<td COLSPAN="2"></td>
		</tr>
			<% 
			'START-Date Lookup------------------------------------------------
			Dim qry_date
			set conn = Session("DBConn")
			tsql = "SELECT DISTINCT rollup_date, rollup_date FROM tblDiscrepancySummary ORDER BY rollup_date desc "
			Set rsqry_date = Conn.Execute(tsql)
		'		Response.Write tsql
			qry_date = Null
			On Error Resume Next
			qry_date = rsqry_date.GetRows()
			set rsqry_date = Nothing
			%>
	
			<td WIDTH="15%"><strong>Date:</strong></td>
			<td WIDTH="85%"><select NAME="tDate" SIZE="1">
				<option SELECTED VALUE></option>
				<%		' Loop thru the rows in the array
				Do
					If isnull(UBound(qry_date, 2)) then
						Response.Write "</SELECT>"
						Exit Do
					End If
					For intRow = 0 to UBound(qry_date, 2)
						if dateadd("yyyy", 1, ConvertNull(qry_date(1, intRow))) > date() then 
							Response.Write "<OPTION VALUE=" & QuotedString(qry_date(0, intRow))
	        				Response.Write ">"
							Response.Write ConvertNull(qry_date(1, intRow))  & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"
							Response.Write "</OPTION>"
						end if
					Next
						Response.Write "</SELECT>"
					Exit Do
				Loop
				'END-Date Lookup------------------------------------------------
				%>
			</td>
		</tr>
		<td WIDTH="15%"><strong>Output:</strong></td>
		<td width="85%"><input type="radio" name="rpt_output" value="Web" checked>Web
		<br><input type="radio" name="rpt_output" value="Excel">Excel</td>
		<%
		rpt_out = request("rpt_output")
		%>
		
				
	</table>

	<br>
	<em>Select a specific Discrepancy Type, Date and Output prior to clicking the
	Begin Search button.  
	</em>	
	<br><br>
		<input TYPE="Hidden" NAME="tstatus" VALUE="query">
		<input TYPE="SUBMIT" NAME="runquery" VALUE="Begin Search">&nbsp;		
		<input TYPE="RESET" NAME="clearcriteria" VALUE="Reset Screen">&nbsp;		
	</form>


<%Case "query"%>
<!-- Start Financial Summary Section ---------------------------------------------------------->
	<table WIDTH="100%" CELLSPACING="0" CELLPADDING="2" BORDER="0">
	<%
	set conn = Session("DBConn")
 
Upvote 0 Downvote
a) start using request.form() or request.querystring() instead of request()
b) the rpt_out = request("rpt_output") line is a little strange, because you assign that value already to rptType in the top of the source
c) when you re-enter the form the selection values are accessable with:

request.form("tType")
request.form("tDate")

i don't see that in this code...


ttmug.gif
 
Upvote 0 Downvote
a) thanks for the input
b) that was frugal attempt at doing something
c) I've been able to access the variables if I enter after selecting the "Web" output..but not after selecting the "Excel" type.

I have redirected to another page in order to change the MIME type at the top of the page but I still cannot access any variables after this statement:
response.contentType = "application/vnd.ms-excel"


still trying....

thanks again,
 
Upvote 0 Downvote
Well, I decided to try the report on a XP system and everything worked fine...but it won't work on my system, a 2000 machine with Office 2000....


must look into that now,
 
Upvote 0 Downvote
This works on my W2K machine and Excel 2K:

(save it as index.asp, because thats the action name in theFORM)

Code: 
<% 

dim lQuery
lQuery = false
if request.ServerVariables("REQUEST_METHOD") = "POST" then

 ' Check Type
 dim cTtype
 cTtype = rtrim(request.Form("ttype"))
 if cTtype = "" then response.Redirect "index.asp"
 
' Check date
 dim cTDate
 cTDate = rtrim(request.Form("tdate"))
 if cTDate = "" then response.Redirect "index.asp"

 Dim rptType
 rptType = rtrim(request.form("rpt_output"))
 if rptType = "Web" then
  ' Output to browser
 elseif rptType = "Excel" then
  ' Output to Excel
  response.contentType = "application/vnd.ms-excel"
 else
  ' not a valid report type
  response.Redirect "index.asp?error=3"
 end if

 lQuery = true

 Session("odv_wtext") = "" 
 Session("odv_displayview") = "list"
 Session("odv_displayequipment_charges") = ""
end if
%>

<FORM ACTION="index.asp" METHOD="POST">
<table WIDTH="100%" CELLSPACING="0" CELLPADDING="0" BORDER="0">
<tr>
<th WIDTH="5%" ROWSPAN="2" VALIGN="top"></th>
<th NOWRAP BGCOLOR="#6633FF" ALIGN="Left">

<%
If lQuery Then
 Response.write "<FONT SIZE=+2>&nbsp;Discrepancy Summary Listing Search Results</FONT>"
Else
 Response.write "<FONT SIZE=+2>&nbsp;Search Discrepancy Summary Listing</FONT>"
End if
%>
</th>
</tr>

<tr>
 <td NOWRAP BGCOLOR="#6633FF" VALIGN="top" ALIGN="Right" WIDTH="100%">
 <%
 if not lQuery then
   response.Write "&nbsp;<INPUT TYPE=""SUBMIT"" NAME=""DataAction"" VALUE=""AC Menu"">"
 end if
 %>
 </td>
</tr>
</table>
</form>


<%
if not lQuery then
%>

<h4 ALIGN="center">Discrepancy Summary Listing by Budget Center</h4>
<font SIZE="2" COLOR="navy">Enter Search Criteria and Click the Begin Search button.</font>
<form ACTION="index.asp" METHOD="POST">
<table CELLSPACING="2" CELLPADDING="1" BORDER="0">
<tr>
 <td>Discrepancy Type:</td>
 <td>
  <!-- simulation without db -->
  <select NAME="tType">
  <option SELECTED>
  <option>A
  <option>B
  </select>
 </td>
</tr>

<tr>
 <td>Date:</td>
 <td>
  <!-- simulation without db -->
  <select NAME="tDate" SIZE="1">
   <option SELECTED>
   <option>01-01-2004
   <option>17-03-2004
   </select>
 </td>
</tr>

<tr>
 <td>Output:</td>
 <td>
  <input type="radio" name="rpt_output" value="Web" checked>Web<br>
  <input type="radio" name="rpt_output" value="Excel">Excel
 </td>
</tr>              
</table>

<br>
<em>Select a specific Discrepancy Type, Date and Output prior to clicking the
Begin Search button.</em>    
<br>
<br>

<input TYPE="SUBMIT" VALUE="Begin Search">&nbsp;        
<input TYPE="RESET"  VALUE="Reset Screen"> 

</form>

<% 
else
 ' OUTPUT! 
%> 
 <table>
 <tr><td>Variable</td><td>Value</td></tr>
 <tr><td>lQuery </td><td><%= lQuery %></td></tr>
 <tr><td>cTtype </td><td><%= cTtype %></td></tr>
 <tr><td>cTDate </td><td><%= cTDate %></td></tr>
 </table>
<%
end if
%>

ttmug.gif
 
Upvote 0 Downvote
Yeah my code works on someone elses win2000, office2000 machine as well.....

I'm trying to compare some registry values now...doing some searching on this topic now...


thanks for all the help...
 
Upvote 0 Downvote
It's no problem to use Request() instead if using Request.Form or QuerryString.
That is not the problem with your code. Somehow could be cause of your IE browser since on Windows 2000 it's IE5.0.(XP comes with IE 6.0 as i remember)
Try to get latest IE version and test with that.

________
George, M
Searches(faq333-4906),Carts(faq333-4911)
 
Upvote 0 Downvote
I meant to post this yesterday...
I am using IE 6 as well, What I did to correct the problem was uninstall office and re-install it right after.
This seem to fix the problem....


thanks
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question Question
How to send HTML emails with ASP classic?
Replies
0
Views
1K
leifoet
leifoet
leifoet
  • Locked
  • Question Question
How to check if a datatable is empty ?
Replies
0
Views
807
leifoet
leifoet
penguinspeaks
  • Locked
  • Question Question
Return to current location on page after asp processes.
Replies
2
Views
1K
penguinspeaks
penguinspeaks
Swi
  • Locked
  • Question Question
JQUERY AJAX POST and how to receive data via Classic ASP
Replies
0
Views
1K
Swi
Swi
Swi
  • Locked
  • Question Question
Best/Quickest Grid to display data
Replies
1
Views
760
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top