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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Populating XML with ASP 1

Status
Not open for further replies.
craigward

craigward

Programmer
Nov 13, 2007
230
GB
Hi,

I have a project to pull data out of a SQL database into and xml file through a dynamic form where the user has options to select variables, date ranges etc.I want this

data to populate or create an .xml file.

I can build the form and can get the data from the database no problem when I submit the form directly to my data.xml but the problem I have is I have to load the page

below (Chart.html) not data.xml The below code references the data.xml file and produces the results in a graphical chart.

What I am looking for is someone’s input on how they would tackle this I am fluent in ASP and SQL and I can work my way round XML but I haven't used the XML technology

enough to no the best way to tackle this. There is going to be many variation of charts to display so I need to build the xml as defined by the users from the form.

Hope this is clear and just so you know I can reference and actual .xml file or an .asp page that formulates an xml page via my chart.html.

Thanks in advance.



Chart.html
Code: 
<html>
   <head>
      <title>My First FusionCharts</title>
   </head>
   <body bgcolor="#ffffff">
      <object classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" codebase="[URL unfurl="true"]http://fpdownload.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=8,0,0,0"[/URL] 

width="900" height="300" id="Column3D" >
         <param name="movie" value="../FusionCharts/Doughnut2D.swf" />
         <param name="FlashVars" value="&dataURL=Data.asp&chartWidth=900&chartHeight=300">
         <param name="quality" value="high" />
         <embed src="../FusionCharts/Doughnut2D.swf" flashVars="&dataURL=Data.asp&chartWidth=900&chartHeight=300" quality="high" width="900" height="300" 

name="Column3D" type="application/x-shockwave-flash" pluginspage="[URL unfurl="true"]http://www.macromedia.com/go/getflashplayer"[/URL] />
      </object>
</body>
</html>


This is the test page I setup to get my data and it does create the required asp/xml file on the fly but not permanently so when I redirect to chart.html above there is

no data to display and no chart appears. Where as if I had a static asp or xml page and load directly the chart.html page the chart is perfect.

data.asp

Code: 
<%@ Language=VBScript %>
<!--#include file="includes/connection.asp"-->

<%

user = Request.Form("wce_uid")

sday = Request.Form("sday")
smonth = Request.Form("smonth")
syear = Request.Form("syear")

eday = Request.Form("eday")
emonth = Request.Form("emonth")
eyear = Request.Form("eyear")

startdate = (syear)&"/"&(smonth)&"/"&(sday)
enddate =  (eyear)&"/"&(emonth)&"/"&(eday)

qry = "SELECT SUM (wce_products.amount) As total FROM wce_sales INNER JOIN wce_products ON wce_sales.uniqueid = wce_products.saleid WHERE wce_sales.recordmanager = '"&

("53b1rjz1rw06k9l7")&"' AND wce_sales.createtime >= '"&(startdate)&"' AND wce_sales.createtime <= '"&(enddate)&"'"
Set oRs = connStr.Execute(qry)

total = oRs("total")
'response.write(total)
'response.end



response.Write("<chart caption='Monthly Sales Summary per user' xAxisName='Month' yAxisName='Sales' numberPrefix='£'>")
response.Write("<set label='test' value='"&(total)&"' />")

response.Write("</chart>")
%>
 
Sort by date Sort by votes
craigward,
I have been looking and asking around and with the help of many I was able to get some code going to be able to build an xml file. I am not a programmer, maybe you can refine the code.
In my case the asp page gets the data from an as400 db and outputs to an xml file
Hope this is kind of what you are looking for attached is the asp page.
example.asp
Code: 
<html>
<title>create xmlfile</title>
<body bgcolor="#FFFFFF">
<%
' Name of the access db being queried


' Connection string to the as400 db

cn="dsn=mydsn;uid=myuserid;pwd=mypassword"

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Query the states table from the state_info db
sql = "select mylib.myfile.myfield1, sum(mylib.myfile.myfield2)as field2 from mylib.myfile group by mylib.myfile.myfield1 order by mylib.myfile.myfield1 asc" 

' Execute the sql
rs.Open sql, cn

' Move to the first record
rs.MoveFirst

' Name for the ouput document 
file_being_created= "myxmlfile.xml"

' create a file system object
set fso = createobject("scripting.filesystemobject")

' create the text file  - true will overwrite any previous files
' Writes the db output to a .xml file in the same directory 
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)

' All non repetitive xml on top goes here
act.WriteLine("<?xml version=""1.0""?>")
act.WriteLine("<items>")

'Loop to output all the query results to the xml document
do while not rs.eof


mo =  rs("myfield1")


myfield2 = rs("myfield2")
act.WriteLine"<item Month=""" & mo & """ value= """ & myfield2 & """/>" & vbcrlf




' move to the next record
rs.movenext
loop

' All non repetitive xml on bottom goes here
act.WriteLine("</items>")


' close the object (xml)
act.close


' Writes a link to the newly created xml document in the browser
response.write "<a href='dailyrevenue.xml'>revenue</a> (.xml) has been created <br>"
response.write "on  " & now() & "<br>"
%>
</body>
</html>
 
Upvote 0 Downvote
If you want to output XML to the screen, you need to tell the browser to output it as an xml stream

<%
response.ContentType="text/xml"
response.Write("<chart caption='Monthly Sales Summary per user' xAxisName='Month' yAxisName='Sales' numberPrefix='£'>")
response.Write("<set label='test' value='"&(total)&"' />")
response.Write("</chart>")
%>

you should also disable page caching for xml, add this to the very top of your page.

Code: 
<%
	response.CacheControl = "no-cache"
	response.AddHeader "Pragma", "no-cache"
%>


--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest: --------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc
 
Upvote 0 Downvote
aarellano thank you for a great post, i have managed to alter the code to giveme the result i need. Thanks again!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
245
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
264
xwb
xwb
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
194
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
175
JScannell
JScannell
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
478
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top