i am working on this project, after you search the data is displayed on the screen. on the bottom of the page is an export to excel file button. now the button works but it only shows the headers and not the data. I cant seem to draw the data into the spreadsheet. here is my file:
' set objAssignedRS = nothing
Set objRS = nothing
objConn.Close
Set objConn = nothing
%>
</table>
<center><input type="submit" value="Export to an Excel File"></center>
</form>
<center><a href="javascript:window.close()"><font size="2" ><b>Close Window</b></font></a></center>
</BODY>
</HTML>
Webmaster
http://www.ptsdcs.com
lmf232s
12-10-2004, 04:12 PM
is this the code for page download.asp?
download.asp is the page where you do the excel export?
jmaresca2005
12-10-2004, 04:15 PM
download.asp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title> New Document </title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<% @language ="vbscript" %>
<% response.buffer = true %>
<%
Response.Buffer = True
Dim objXMLHTTP, xml
Set xml = Server.CreateObject("Microsoft.XMLHTTP")
'Point this to the file you wish to download
xml.Open "GET","http://dev-environ/Users/" & request("FileName"),False
xml.Send
' Add a header to give it a file name:
Response.AddHeader "Content-Disposition", _
"attachment;filename=" & request("DisplayFileName")
' Specify the content type to tell the browser what to do.
'THIS FOOLS THE BROWSER INTO THINKING YOU WISH TO DOWNLOAD A ZIP FILE
Response.ContentType = "application/zip"
' Binarywrite the bytes to the browser
Response.BinaryWrite xml.responseBody
Set xml = Nothing
%>
<body>
</body>
</html>
jmaresca2005
12-13-2004, 09:19 AM
Still Need help!!!
Webmaster
http://www.ptsdcs.com
russell
12-13-2004, 01:12 PM
is the xml being written to the page? view source and see if the xml string is there. If it is, you need to use the MSXML2.Domdocument object to parse the XML for display.
jmaresca2005
12-13-2004, 01:15 PM
that script was from an older application. i need to display the contents that are written in asp/vbscript.
russell
12-13-2004, 01:23 PM
what contents? there are none in your code except for the Response.BinaryWrite xml.responseBody.
jmaresca2005
12-13-2004, 01:25 PM
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title> New Document </title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<% @language ="vbscript" %>
<% response.buffer = true %>
<%
Response.Buffer = True
Dim objXMLHTTP, xml
Set xml = Server.CreateObject("Microsoft.XMLHTTP")
'Point this to the file you wish to download
xml.Open "GET","http://dev-environ/Users/" & request("FileName"),False
xml.Send
' Add a header to give it a file name:
Response.AddHeader "Content-Disposition", _
"attachment;filename=" & request("DisplayFileName")
' Specify the content type to tell the browser what to do.
'THIS FOOLS THE BROWSER INTO THINKING YOU WISH TO DOWNLOAD A ZIP FILE
Response.ContentType = "application/zip"
' Binarywrite the bytes to the browser
Response.BinaryWrite xml.responseBody
Set xml = Nothing
%>
<%
'Response.AddHeader "Content-Type", "application/vnd.ms-excel"
'Response.AddHeader "Content-disposition", "attachment; filename=filename.xls"
%>
<body>
<%
Dim objConn
Dim objCmd
Dim rs
Dim GPITEM
Dim EXPSHIP
Dim CUSTNMBR
Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString")
Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn %>
<%Dim counter
counter = 1
Do until objRS.EOF
if counter mod 2 = 0 then
response.write("<TR bgcolor='white'>")
else
response.Write("<TR bgcolor='white'>")
end if%>
Set objRS = nothing
objConn.Close
Set objConn = nothing
%>
</body>
</html>
Webmaster
http://www.ptsdcs.com
russell
12-13-2004, 01:31 PM
You are trying to loop through the recordset, but you haven't declared or opened it. Are you getting an error on this line Do until objRS.EOF?
Your own posting earlier (******* STORED PROCEDURE CALL********) demonstrates one way to open a recordset
jmaresca2005
12-13-2004, 01:34 PM
i added the stroed procude call: i get this error:
Internet Explorer was not able to open thie Internet site. The requested site is either unavaibale or cannot be found.
CODE:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title> New Document </title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<% @language ="vbscript" %>
<% response.buffer = true %>
<%
Response.Buffer = True
Dim objXMLHTTP, xml
Set xml = Server.CreateObject("Microsoft.XMLHTTP")
'Point this to the file you wish to download
xml.Open "GET","http://dev-environ/Users/" & request("FileName"),False
xml.Send
' Add a header to give it a file name:
Response.AddHeader "Content-Disposition", _
"attachment;filename=" & request("DisplayFileName")
' Specify the content type to tell the browser what to do.
'THIS FOOLS THE BROWSER INTO THINKING YOU WISH TO DOWNLOAD A ZIP FILE
Response.ContentType = "application/zip"
' Binarywrite the bytes to the browser
Response.BinaryWrite xml.responseBody
Set xml = Nothing
%>
<%
'Response.AddHeader "Content-Type", "application/vnd.ms-excel"
'Response.AddHeader "Content-disposition", "attachment; filename=filename.xls"
%>
<body>
<%
Dim objConn
Dim objCmd
Dim rs
Dim GPITEM
Dim EXPSHIP
Dim CUSTNMBR
Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString")
Set objRS = nothing
objConn.Close
Set objConn = nothing
%>
</body>
</html>
Webmaster
http://www.ptsdcs.com
russell
12-13-2004, 02:11 PM
1. You still aren't opening the recordset.
2. Try commenting out all of the xml stuff -- the xml.Open line is probably what is causing that error. I'd guess that request("FileName") is an empty string but you'll have to test there.
Also, in IE go to TOOLS, Ineternet Options, Advanced and make sure that "show friendly http error messages" is NOT checked. This will help you debug.
You have a lot going on here, and you need to work on one problem at a time.
jmaresca2005
12-13-2004, 02:16 PM
same error. I have changed the code as per your advice.
<% @language ="vbscript" %>
<% 'response.buffer = true %>
<%
Response.Buffer = True
'Dim objXMLHTTP, xml
'Set xml = Server.CreateObject("Microsoft.XMLHTTP")
'Point this to the file you wish to download
'xml.Open "GET","http://dev-environ/Users/" & request("FileName"),False
'xml.Send
' Add a header to give it a file name:
Response.AddHeader "Content-Disposition", _
"attachment;filename=" & request("")
' Specify the content type to tell the browser what to do.
'THIS FOOLS THE BROWSER INTO THINKING YOU WISH TO DOWNLOAD A ZIP FILE
Response.ContentType = "application/zip"
' Binarywrite the bytes to the browser
'Response.BinaryWrite xml.responseBody
'Set xml = Nothing
%>
<%
'Response.AddHeader "Content-Type", "application/vnd.ms-excel"
'Response.AddHeader "Content-disposition", "attachment; filename=filename.xls"
%>
<body>
<%
Dim objConn
Dim objCmd
Dim rs
Dim GPITEM
Dim EXPSHIP
Dim CUSTNMBR
Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString")
Microsoft OLE DB Provider for SQL Server error '80040e10'
Procedure '_SP_SearchInfo' expects parameter '@GPITEM', which was not supplied.
/Users/download.asp, line 28
@GPITEM is in the stroed procedure, because i checked it 3 times and that same stroed procedure works in my SearchResults.asp page.
russell
12-13-2004, 02:56 PM
GPITEM is empty. You need to provide a value for it. It never should have executed the SP without the value. I made a mistake:
If len(GPITEM) < 1 Then
showData
Else
Response.Write "GPITEM is missing. Need to supply a value to execute the query"
End If
should be
If len(GPITEM) Then
showData
Else
Response.Write "GPITEM is missing. Need to supply a value to execute the query"
End If
That will prevent it from executing with no value for the parameter. You can provide a default value if you wish like so
If len(GPITEM) < 1 Then
GPITEM = SomeDefaultValueHere
End If
showData
jmaresca2005
12-13-2004, 02:59 PM
"GPITEM is missing. Need to supply a value to execute the query" is displayed when i test
russell
12-13-2004, 03:00 PM
good. supply a value
jmaresca2005
12-13-2004, 03:02 PM
where should i insert it. im confused
russell
12-13-2004, 03:51 PM
If len(GPITEM) < 1 Then
GPITEM = SomeDefaultValueHere
End If
jmaresca2005
12-13-2004, 03:53 PM
nothing happens. it diplays a blank page.
russell
12-13-2004, 04:04 PM
Did you supply a valid value? What value did you supply? What data type does the SP expect? What happens when you execute it in Query Analyzer with that value as a paramater?
jmaresca2005
12-13-2004, 04:07 PM
i supplied a value of 20024-2 which is a valid GPITEM number. i ran it in query analyser and got back all trhe records with GITEM number 20024-2, but on the ASp side i get back nothing
russell
12-13-2004, 04:18 PM
did u do it like this?
If len(GPITEM) < 1 Then
GPITEM = "20024-2"
End If
jmaresca2005
12-13-2004, 04:22 PM
yes
russell
12-13-2004, 04:39 PM
<body>
<%
Dim cmd
Dim rs
Dim GPITEM
Dim EXPSHIP
Dim CUSTNMBR
GPITEM = Request("GPITEM")
If len(GPITEM) < 1 Then GPITEM = "20024-2"
showData
Sub showData()
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
With cmd
.ActiveConnection = Application("Conn_ConnectionString")
.CommandText="_SP_SearchInfo"
.CommandType=4
.Parameters.Append .CreateParameter("@GPITEM",129,1,21,GPITEM)
rs.Open .Execute
End With
If Not rs.EOF Then
Response.Write "<table>" & vbCrLf
Do until rs.EOF
%>
<tr bgcolor="white">
<td align=center>
<a href="update.asp?GPITEM=<%=rs("GPITEM")%>"><font size=1 color="black" ><%=rs("GPITEM")%></a></font>
</td>
<td align=center><font size=1><%=rs("EXPSHIP")%> </font></td>
<td align=center><font size=1><%=rs("CUSTNMBR")%></font></td>
</tr>
<%
rs.MoveNext
Loop
rs.Close
Response.Write "</table>" & vbCrLf
Else
Response.Write "No data"
End If
Set rs = nothing
Set cmd = Nothing
End Sub
%>
</body>
jmaresca2005
12-13-2004, 04:42 PM
it displays no data
russell
12-13-2004, 04:44 PM
then your query isn't returning any records.
jmaresca2005
12-13-2004, 04:44 PM
it displays the hard coded GPITEM numbers, but i am not understanding what thjis has to do with exporting to an excel file. this code does exactly what my Search Results.asp page does.
russell
12-13-2004, 04:48 PM
at the top of the file add this:
Response.ContentType = "application/vnd.ms-excel"
Let me suggest a couple of good ASP books.
Beggining ASP 3.0 (http://www.amazon.com/exec/obidos/ASIN/0764543636/qid=1102974051/sr=2-1/ref=pd_ka_b_2_1/103-5888907-8167848)
Professional ASP 3.0 (http://www.amazon.com/exec/obidos/ASIN/1861002610/qid=1102974051/sr=2-3/ref=pd_ka_b_2_3/103-5888907-8167848)
jmaresca2005
12-13-2004, 04:59 PM
i want to be able to pass a dynamic GPITEM number. the hard coded number is the one that is passed to the excel file.
russell
12-13-2004, 05:14 PM
It does it, but you have to PASS the value first. You can do it with a form post or get, or just pass it in the querystring through a link (like your code does when it displays the records).
This line is looking for the value passed in (if there is no value, then we hard coded it, but you don't need to do that -- I showed you earlier how to avoid executing the query if the value dosen't exist)
GPITEM = Request("GPITEM")
Is your question "How do I pass a form variable to my script?" ?
jmaresca2005
12-14-2004, 09:33 AM
yes
jmaresca2005
12-14-2004, 11:06 AM
still have a problem. need code to insert dynamic content into excel
russell
12-14-2004, 11:17 AM
To pass the variable to your asp you have several choices. (1) a link with a querystring:
<a href="update.asp?GPITEM=<%=rs("GPITEM")%">
(2) a form with the method set to get, which will cause the values to appear in the querystring (3) a form with the method set to post.
Find the value with Request("item") where item is the name of the variable you are passing: GPITEM = Request("GPITEM")
The code that searches the database requires this value to be present, so i demonstrated 2 options (1) supply a default value: If len(GPITEM) < 1 Then GPITEM = "20024-2" (2) refuse to execute the code if the value is not present by wrapping the code in an if statement.
So you need to present a user interface by which the user selects the item to display, pass the item selected to the script, then execute it.
The Response.ContentType line will cause all output to be to excel.
Somewhere along the line you need the value of GPITEM to be set, then you can query the database and build your output.
russell
12-14-2004, 11:20 AM
Querystring values and form values are in name/value pairs. in the url update.asp?GPITEM=1212
Request.QueryString("GPITEM") or simply Request("GPITEM") will return "1212"
Same with form post data. If you have a form field named GPITEM <input name=GPITEM value="1212">, the Request.Form("GPITEM") will return the value of that item.
jmaresca2005
12-14-2004, 11:35 AM
got it to work. thank you for everything.
russell
12-14-2004, 02:08 PM
glad 2 help
webdeveloper.com
Copyright Internet.com Inc., All Rights Reserved.