|
| |
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
 |
ASP to XML - 7/12/2005 14:50:14
HI,
just a quick question,
is it possible to use an asp (classic) page to query an access database and have the results sent back as an XML file?
If so, please could you point me in the direction of either some sample code to look at or a tutorial.
many thanks
Rob.
|
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/12/2005 16:39:34
Thanks for the pointer, much appreciated.
I thought I'd create a first pass asp page to produce XML results.
The code I used is based on the example shown on the link above.
The ouptut complains about white space and yet in the example it shows the output as having spaces.
If I remove the Description field where the spaces exist I get XML, which is great, but I need the descriptions also.
any ideas what the problem might be?
Cheers Rob.
<%@LANGUAGE = VBScript%>
<?xml version="1.0"?>
<Photos>
<%
Dim oConn, oRS, strSQL
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("gen_dib_data.mdb")
strSQL="SELECT * FROM Photo"
oRS.Open strSQL, oConn, 2, 3
Do While Not oRS.EOF
%>
<Photo>
<Description></Description>
<Thumbnail></Thumbnail>
<ID></ID>
</Photo>
<Photo>
<Description><%=oRS("PDesc")%></Description>
<Thumbnail><%=oRS("Filename")%></Thumbnail>
<ID><%=oRS("PhotoID")%></ID>
</Photo>
<% oRS.MoveNext
Loop
%> <% oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
%>
</Photos>
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
Whitespace is not allowed at this location. Error processing resource 'http://thebes/ImageRealm_new_1/Test_Folder%202/dib1....
<Description>Father & baby</Description>
---------------------------^
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/12/2005 17:19:44
yes that worked , I removed & and the ' chars both of which XML did not like where I had a lot of instances of , so now I get a complete list just fine.
However, what if I need to keep them in place can this be done?
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/12/2005 17:22:18
This the complete code corrected from the previous message.
<%@LANGUAGE = VBScript%>
<?xml version="1.0" encoding="UTF-8"?>
<Photos>
<%
Dim oConn, oRS, strSQL
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("gen_dib_data.mdb")
strSQL="SELECT * FROM Photo"
oRS.Open strSQL, oConn, 2, 3
Do While Not oRS.EOF
%>
<Photo>
<Description><%=oRS("PDesc")%></Description>
<Thumbnail><%=oRS("Filename")%></Thumbnail>
<ID><%=oRS("PhotoID")%></ID>
</Photo>
<% oRS.MoveNext
Loop
%> <% oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
%>
</Photos>
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/14/2005 11:48:27
Ok thanks Ian I'll try that.
just as an aside, the code above produces XML sure enough, but does not create an actual XML file. I tried to display some text from the asp file after it has run and nothing appears in Flash but if I copy the XML output in notepad and create an XML file Flash reads the records. (after I change the file name to look for in the ActionScript)
Cheers Rob.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: ASP to XML - 7/14/2005 11:58:46
I use this vbscript function I nabbed from somewhere (was it Spooky??)
<%
function WriteToFile(FileName, Contents, Append)
on error resume next
if Append = true then
iMode = 8
else
iMode = 2
end if
set oFs = server.createobject("Scripting.FileSystemObject")
set oTextFile = oFs.OpenTextFile(FileName, iMode, True)
oTextFile.Write Contents
oTextFile.Close
set oTextFile = nothing
set oFS = nothing
end function
%>
Anyway, this will help write stuff to a notepad automatically for you, below I've shown a bit of the code in use writing results from an array, which are then appended line by line to a notepad.
myFileN = "myxmlfile.asp"
myFoldN = "xmlfolder\"
For rowN = 0 to numrows
myCode = alldata(0,rowN)
myProgN = alldata(1,rowN)
myContent = "<option value='" & myCode & "'>" & myProgN & "</option>"
WriteToFile "E:\public\web\" & myFoldN & myFileN, myContent, True
Next
Use True to append, False to overwrite. Hope this helps (anyone! ).
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/14/2005 12:17:46
woah there, this is a bit over my head, where do I put this code - in my file somewhere?
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/14/2005 13:13:07
I added the code as in below, but it seems to be failing at line 31
For i = 0 to Ubound(alldata)
<%@LANGUAGE = VBScript%>
<?xml version="1.0" encoding="UTF-8"?>
<PORTFOLIO>
<%
Dim oConn, oRS, strSQL
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("dib/gen_dib_data/gen_dib_data.mdb")
strSQL="SELECT DISTINCT Photo.PDesc, Photographer.Fullname AS Expr1, Photo.PhotoID, Photo.[Date], Photo.[Size], Photo.Filename, Photographer.photogID, Photographer.GalleryLink FROM Photographer AS Photographer_1, (tCat INNER JOIN tSCat ON tCat.CatID = tSCat.CatID) INNER JOIN ((Photo INNER JOIN Photographer ON Photo.PhotogID = Photographer.photogID) INNER JOIN (tSSCat INNER JOIN SSC2Photo ON tSSCat.SSCatID = SSC2Photo.SSCatID) ON Photo.PhotoID = SSC2Photo.PhotoID) ON tSCat.SCatID = tSSCat.SCatID WHERE (((tCat.CatID)=35));"
oRS.Open strSQL, oConn, 2, 3
Do While Not oRS.EOF
%>
<IMAGE>
<IMAGEDESC><%=oRS("PDesc")%></IMAGEDESC>
<PHOTOGRAPHER><%=oRS("Expr1")%></PHOTOGRAPHER>
<IMAGEFILE><%=oRS("Filename")%></IMAGEFILE>
<IMAGEID><%=oRS("PhotoID")%></IMAGEID>
</IMAGE>
<!--INCLUDE FILE="file.asp"-->
<%
myFileN = "myXML.txt"
myFoldN = "\xml"
For i = 0 to Ubound(alldata)
'put the data into a variable
myContent = "<start>" & alldata(i) & "</end>"
'call the write to file function
WriteToFile myFoldN & myFileN, myContent, True
Next
oRS.MoveNext
Loop
%>
<% oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
%>
</PORTFOLIO>
Cheers Rob
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: ASP to XML - 7/14/2005 13:20:27
I use this method to get data
'SETTING RECORDSET OBJECT AND OPENING RECORDSET USING SQL STATEMENT
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL1, myConn
alldata=myRS.getrows
myRS.Close
Set myRS = Nothing
myConn.Close
Set myConn = Nothing
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)
have a quick read of http://www.4guysfromrolla.com/ASPScripts/PrintFAQ.asp?FAQID=161 this for more info on GetRows
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/14/2005 16:55:59
The message I get now is this, which is this line
myContent = "<start>" & alldata(i) & "</end>"
Subscript out of range: 'alldata'
/ImageRealm_new_1/Test_Folder 2/dib3.asp, line 30
<%@LANGUAGE = VBScript%>
<%
Dim oConn, oRS, strSQL
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("dib/gen_dib_data/gen_dib_data.mdb")
strSQL="SELECT * FROM Photo"
oRS.Open strSQL, oConn, 2, 3
alldata=oRS.getrows()
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)
%>
<!--INCLUDE FILE="file.asp"-->
<%
myFileN = "myXML.txt"
myFoldN = "\xml"
myContent = "<start>" & alldata(i) & "</end>"
WriteToFile myFoldN & myFileN, myContent, True
%>
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: ASP to XML - 7/14/2005 18:36:46
You're missing For/Next loops to iterate through the array. Put this after myFoldN = "\xml". I haven't tested this!
'move through the rows
For i = 0 to numrows
'add the start tag
myContent = "<photo>"
'and to move along the columns, a nested loop
For j = 0 to numcols
'put the data into a variable for neatness
thisfield = alldata(j,i)
'decide on the tag and add it to myContent
Select Case j
Case 0
myContent = myContent & "<description>" & thisfield & "</description>"
Case 1
myContent = myContent & "<thumbnail>" & thisfield & "</thumbnail>"
Case 2
myContent = myContent & "<id>" & thisfield & "</id>"
End Select
Next
'add the closing tag
myContent = myContent & "</photo>"
WriteToFile myFoldN & myFileN, myContent, True
Next
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/14/2005 19:01:42
Its now coming back with this message
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'WriteToFile'
/ImageRealm_new_1/Test_Folder 2/dib3.asp, line 52
WriteToFile myFoldN & myFileN, myContent, True
<%@LANGUAGE = VBScript%>
<%
Dim oConn, oRS, strSQL
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("dib/gen_dib_data/gen_dib_data.mdb")
strSQL="SELECT * FROM Photo"
oRS.Open strSQL, oConn, 2, 3
alldata=oRS.getrows()
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)
%>
<!--INCLUDE FILE="file.asp"-->
<%
myFileN = "myXML.xml"
myFoldN = "xml\"
'move through the rows
For i = 0 to numrows
'add the start tag
myContent = "<photo>"
'and to move along the columns, a nested loop
For j = 0 to numcols
'put the data into a variable for neatness
thisfield = alldata(j,i)
'decide on the tag and add it to myContent
Select Case j
Case 0
myContent = myContent & "<description>" & PDesc & "</description>"
Case 1
myContent = myContent & "<thumbnail>" & Filename & "</thumbnail>"
Case 2
myContent = myContent & "<id>" & PhotoID & "</id>"
End Select
Next
'add the closing tag
myContent = myContent & "</photo>"
WriteToFile myFoldN & myFileN, myContent, True
Next
%>
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/15/2005 9:23:41
Hi Iain, sorry about the hash thing should of spotted that, it was late last night when I looked at it, you know how it is.
Anyway, the code runs without a failure this time, however, it does not produce the xml either.
WriteToFile "C:\Inetpub\wwwroot\ImageRealm_new_1\Test_Folder 2\" & myFoldN & myFileN, myContent, False
I added the full directory path as shown above and did a response.write to the path which it returns
C:\Inetpub\wwwroot\new_1\Test_Folder 2\xml\myXML.txt
so that looks ok
I also did a response.write on myContent that showed nothing, is this correct?
Cheers Rob.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: ASP to XML - 7/15/2005 11:13:46
I had a quick go with the code and it didn't work, so I set up my own test file - here's the code
<%@LANGUAGE = VBScript%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 TRANSITIONAL//EN">
<html>
<head>
<title>Test</title>
</head>
<body>
<!--#INCLUDE FILE="adovbs.inc"-->
<%
Dim myConnSQL
Set myConnSQL = Server.CreateObject("ADODB.Connection")
myConnSQL.Open "LocalServer"
%>
<%
mySQL = "SELECT TOP 5 OrgId, OrgName, OrgTypeId FROM Org"
'SETTING RECORDSET OBJECT AND OPENING RECORDSET USING SQL STATEMENT
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, myConnSQL
alldata=myRS.getrows()
myRS.Close
Set myRS = Nothing
myConnSQL.Close
Set myConnSQL = Nothing
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)
Response.Write "numcols: " & numcols & "<br />"
Response.Write "numrows: " & numrows & "<br />"
%>
<%
function WriteToFile(FileName, Contents, Append)
on error resume next
if Append = true then
iMode = 8
else
iMode = 2
end if
set oFs = server.createobject("Scripting.FileSystemObject")
set oTextFile = oFs.OpenTextFile(FileName, iMode, True)
oTextFile.Write Contents
oTextFile.Close
set oTextFile = nothing
set oFS = nothing
end function
%>
<%
myFileN = "myXML.txt"
myFoldN = "xml\"
'move through the rows
For i = 0 to numrows
'add the start tag
myContent = "<photo>"
'and to move along the columns, a nested loop
For j = 0 to numcols
'put the data into a variable for neatness
thisfield = alldata(j,i)
'decide on the tag and add it to myContent
Select Case j
Case 0
myContent = myContent & "<description>" & thisfield & "</description>"
Case 1
myContent = myContent & "<thumbnail" & thisfield & "</thumbnail>"
Case 2
myContent = myContent & "<id>" & thisfield & "</id>"
End Select
Next
'add the closing tag
myContent = myContent & "</photo>"
Response.Write myContent & "</br>"
Response.Write myFoldN & myFileN & "<br />"
WriteToFile "C:\docs\web\" & myFoldN & myFileN, myContent, True
Next
%>
</body>
</html>
and this is the file it wrote
<photo><description>1</description><thumbnail>NORTH TEES</thumbnail><id>3</id></photo><photo><description>2</description><thumbnail>SELBY AND YORK</thumbnail><id>3</id></photo><photo><description>3</description><thumbnail>EAST YORKSHIRE</thumbnail><id>3</id></photo><photo><description>4</description><thumbnail>YORKSHIRE WOLDS AND COAST</thumbnail><id>3</id></photo><photo><description>5</description><thumbnail>EASTERN HULL</thumbnail><id>3</id></photo>
so hopefully if you change the bits of the file to yours then it might just work... (fingers crossed). 
You'll need to add a <root></root> to the file to make it valid xml (and a dtd or xsl etc etc).
Let me know how it goes...
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/15/2005 13:59:46
Hi Iain,
during the the course of today I found this code which seems to work and it uses an xsl style sheet
<%@LANGUAGE = VBScript%>
<%
Response.Expires = -1
Response.Buffer = True
Dim conn, rs, xml, xsl
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("gen_dib_data.mdb") & ";User Id=admin;Password=;"
conn.Open
Set rs = Server.CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = conn
rs.Open "SELECT * FROM Photo"
If Not rs.EOF Then
Set xml = Server.CreateObject("MSXML2.DOMDocument")
Set xsl = Server.CreateObject("MSXML2.DOMDocument")
xml.async = False
xsl.async = False
rs.Save xml, 1 'adPersistXML
xsl.load Server.MapPath("ADOGeneric.xsl")
Response.Write xml.transformNode(xsl)
End If
Set xsl = Nothing
Set xml = Nothing
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema"/>
<xsl:output method="xml" omit-xml-declaration="yes" />
<xsl:template match="/">
<xsl:apply-templates select="//z:row"/>
</xsl:template>
<xsl:template match="z:row">
<xsl:text disable-output-escaping="no"><row></xsl:text>
<xsl:for-each select="@*">
<xsl:text disable-output-escaping="no"><</xsl:text>
<xsl:value-of select="name()"/>
<xsl:text disable-output-escaping="no">></xsl:text>
<xsl:value-of select="."/>
<xsl:text disable-output-escaping="no"></</xsl:text>
<xsl:value-of select="name()"/>
<xsl:text disable-output-escaping="no">></xsl:text>
</xsl:for-each>
<xsl:text disable-output-escaping="no"></row></xsl:text>
</xsl:template>
</xsl:stylesheet>
this is the output
<row><PDesc>Hot Rod</PDesc><Filename>ab_0001.jpg</Filename><PhotoID>223</PhotoID><PhotogID>5</PhotogID><GalleryPicture>1</GalleryPicture></row><row><PDesc>Brass Headlamps</PDesc><Filename>ab_0002.jpg</Filename><PhotoID>224</PhotoID><PhotogID>5</PhotogID><GalleryPicture>2</GalleryPicture></row><row><PDesc>St. Martins in the field</PDesc><Filename>ab_0003.jpg</Filename><PhotoID>225</PhotoID><PhotogID>5</PhotogID><GalleryPicture>3</GalleryPicture></row
although it does not produce an xml file, I was able to grab the data in Flash and display it in a text box, which is great.
So I think this is another version, let me know what you think.
I got the source from this link.
http://builder.com.com/5100-31-5078101.html
Cheers Rob.
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/18/2005 4:15:07
ok thanks Iain, I'll do that, it'll mean changing the database fieldnames as this code grabs the names from there to create the tag names.
My next task will be to format the output into columns in flash, currently its just one long string. I'll post the code as when I get it sorted. It may be of use to someone as it will be an end to end solution from asp to Flash
ASP >> Query >> Access DB >> XML >> FLASH
Cheers Rob.
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/18/2005 18:59:28
thanks Iain
that did the trick,
I replaced
Response.Write xml.transformNode(xsl)
with
Response.Write(lcase(xml.transformNode(xsl)))
Cheers Rob.
| |
|
|
|
RobM_01
Posts: 135
Joined: 1/1/2004
Status: offline
|
RE: ASP to XML - 7/18/2005 19:14:11
do you think there would be any advantage to run an xsl script to transform the elements to attributes?
<collection>
<image>
<pdesc>hot rod</pdesc>
<filename>ab_0001.jpg</filename>
<photoid>223</photoid>
<photogid>5</photogid>
</image>
<image>
<pdesc>brass headlamps</pdesc>
<filename>ab_0002.jpg</filename>
<photoid>224</photoid>
<photogid>5</photogid>
</image>
</collection>
<collection>
<image pdesc="hot rod" filename="ab_0001.jpg" photoid="223" photogid="5"/>
<image pdesc="brass headlamps" filename="ab_0002.jpg" photoid="224" photogid="5"/>
</collection>
I thought I read somewhere that flash could read it quicker.
| |
|
|
|
kt
Posts: 195
Joined: 11/3/2004
Status: offline
|
RE: ASP to XML - 7/30/2005 17:04:13
I've been reading this thread with interest but got a bit lost about half way down...
I'm trying to produce an xml file and have got this far:
<%@LANGUAGE = VBScript%>
<?xml version="1.0" encoding="UTF-8"?>
<Jobs>
<%
Dim oConn, oRS, strSQL
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("fpdb/jobs.mdb")
strSQL="SELECT * FROM jobs"
oRS.Open strSQL, oConn, 2, 3
Do While Not oRS.EOF
%>
<Job>
<company></company>
<country></country>
<city></city>
</Job>
<Job>
<company><%=oRS("company")%></company>
<country><%=oRS("country")%></country>
<city><%=oRS("city")%></city>
</Job>
<% oRS.MoveNext
Loop
%> <% oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
%>
</Jobs>
which produces an asp page with good xml.
What I need though is the file to be .xml, not .asp. I'm guessing that the second half of this thread is talking about that, but it gets a little over my head....
How can I make this an .xml page? (Step by step please...!)
thanks, kt
| |
|
|
|
kt
Posts: 195
Joined: 11/3/2004
Status: offline
|
RE: ASP to XML - 7/31/2005 9:44:49
Okay, trying all sorts of things here, and learning (a little) as I go along...
I came across this at http://www.asp101.com/samples/db_xml.asp:
<%
Dim objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(Server.MapPath("db_xml.xml")) Then
objFSO.DeleteFile Server.MapPath("db_xml.xml")
End IF
Set objFSO = Nothing
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("fpdb/jobs.mdb")
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oRS = oConn.Execute("SELECT country, city, company FROM jobs")
Response.Write "<p>Saving data as XML...</p>" & vbCrLf
oRS.Save Server.MapPath ("db_xml.xml"), adPersistXML
oRS.Close
Set oRS = Nothing
oComm.Close
Set oComm = Nothing
Response.Write "<p>XML file written...</p>" & vbCrLf
Response.Write "<p>Click <a href=""db_xml.xml"">here</a> to view the file.</p>" & vbCrLf
%>
At this line:
oRS.Save Server.MapPath ("db_xml.xml"), adPersistXML
I get this error:
Provider error '80030103'
Can't save.
Anyone have any idea how to get around that?
Thanks, kt (learning slowly...)
| |
|
|
|
kt
Posts: 195
Joined: 11/3/2004
Status: offline
|
RE: ASP to XML - 7/31/2005 18:38:30
Hi Iain
Thanks for the reply.
I think I've figured it out, but in a totally different way - with the help of http://www.codeave.com/asp/code.asp?u_log=149.
Here's what I'm using now:
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("../fpdb/jobs.mdb")
Set rs = Server.CreateObject("ADODB.Recordset")
Set rs = cn.Execute("SELECT ID, country, city, title, dateposted FROM jobs ORDER BY dateposted DESC")
rs.MoveFirst
file_being_created= "jobs12.xml"
set fso = createobject("scripting.filesystemobject")
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)
act.WriteLine("<?xml version=""1.0""?>")
act.WriteLine("<rss version=""2.0"">")
act.WriteLine("<channel>")
act.WriteLine("<title>Latest jobs from eslbase.com</title>")
act.WriteLine("<description>Latest jobs</description>")
act.WriteLine("<link>http://www.eslbase.com/tefl_jobs.asp</link>")
do while not rs.eof
counter=counter+1
act.WriteLine("<item>")
act.WriteLine("<title>" & rs("country") & "</title>" )
act.WriteLine("<description>" & rs("title") & "</description>")
act.WriteLine("<link>http://www.eslbase.com/tefl_job_details?ID=</link>")
act.WriteLine("</item>")
rs.movenext
loop
act.WriteLine("</channel>")
act.WriteLine("</rss>")
act.close
response.write "<a href='jobs12.xml'>Jobs</a> (.xml) has been created <br>"
response.write "on " & now() & "<br>"
%>
The output is this RSS page:
<?xml version="1.0"?>
<rss version="2.0">
<channel>
<title>Latest jobs from eslbase.com</title>
<description>Latest jobs</description>
<link>http://www.eslbase.com/tefl_jobs.asp</link>
<item>
<title>Oman</title>
<description>REQUIRED ENGLISH TEACHERS TO OMAN</description>
<link>http://www.eslbase.com/tefl_job_details?ID=</link>
</item>
<item>
<title>China</title>
<description>Working In a Famous Training Center 1000USD/MONTH</description>
<link>http://www.eslbase.com/tefl_job_details?ID=</link>
</item>
<item>
<title>China</title>
<description>International Primary School Teacher</description>
<link>http://www.eslbase.com/tefl_job_details?ID=</link>
</item>
.....Items go on until the end here
</channel>
</rss>
Basic RSS but works well for what I need...
A couple of questions you (or anyone else!!) may be able to help me out with though...
1 How can I change this line on the asp page...
Set rs = cn.Execute("SELECT ID, country, city, title, dateposted FROM jobs ORDER BY dateposted DESC")
...to retun only, say, 10 results? (i know there's a "TOP" command, but can't seem to make it work...)
2 On this line on the asp page...
act.WriteLine("<link>http://www.eslbase.com/tefl_job_details?ID=</link>")
...I want it to say .../tefl_job_details?ID=rs("ID") (i.e. the ID called in the SQL)
I've played around with it but can't seem to get the syntax right.
Thanks again for the help
kt
EDIT: OK, both problems solved - it was late at night, wasn't thinking straight!!
1 Set rs = cn.Execute("SELECT TOP 10 ID, country, city, title, dateposted FROM jobs ORDER BY dateposted DESC")
...was sure I'd tried this before.
2 act.WriteLine("<link>" & "http://www.eslbase.com/tefl_job_details.asp?ID=" & rs("ID") & "</link>")
< Message edited by kt -- 8/1/2005 5:26:34 >
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
Got to watch out for typos!
