Create Rss feed from data in database

Discussion in 'ASP General' started by Billy Barth, Apr 25, 2007.

  1. Billy Barth

    Billy Barth Guest

    I would like to create a RSS feed from my Access Database.
    I have an Access table for news which has the article title, date, and
    story in it. What I would like to do is pull the data from there into an
    xml file to create the RSS feed, so when users upload a news story it
    automatically updates in the xml file. Can this be done with asp? and where
    should I look for more information. Thanks, This is a great group.

    --
    Posted via a free Usenet account from http://www.teranews.com
     
    Billy Barth, Apr 25, 2007
    #1
    1. Advertising

  2. Billy Barth

    Brett_A Guest

    <?xml version="1.0" encoding="ISO-8859-1"?>
    <% Response.Buffer = true
    Response.ContentType = "text/xml"

    Function ApplyXMLFormatting(strInput)
    strInput = Replace(strInput,"&", "&amp;")
    strInput = Replace(strInput,"'", "'")
    strInput = Replace(strInput,"""", "&quot;")
    strInput = Replace(strInput, ">", "&gt;")
    strInput = Replace(strInput,"<","&lt;")

    ApplyXMLFormatting = strInput
    End Function
    %>
    <rss version="2.0">
    <channel>
    <title>xxxxxxxxxxxx</title>
    <description>xxxxxxxxxxxxx</description>
    <link>xxxxxxxxxxxxx</link>
    <language>en-us</language>
    <copyright>xxxxxxxxxxx</copyright>
    <managingEditor>xxxxxxxxxxxx</managingEditor>

    <%
    Dim rs_blog_feed
    Dim rs_blog_feed_numRows

    Set rs_blog_feed = Server.CreateObject("ADODB.Recordset")
    rs_blog_feed.ActiveConnection = xxxxxxxx
    rs_blog_feed.Source = "xxxxxxxxxxxxxxxx"
    rs_blog_feed.CursorType = 0
    rs_blog_feed.CursorLocation = 2
    rs_blog_feed.LockType = 1
    rs_blog_feed.Open()

    rs_blog_feed_numRows = 0
    %>

    <%
    Dim Repeat1__numRows
    Dim Repeat1__index

    Repeat1__numRows = 10
    Repeat1__index = 0
    rs_blog_feed_numRows = rs_blog_feed_numRows + Repeat1__numRows
    %>

    <%
    While ((Repeat1__numRows <> 0) AND (NOT rs_blog_feed.EOF))
    %>
    <item>
    <title>xxxxxxxxxxx</title>
    <description><%=ApplyXMLFormatting(xxxxxxxxxxxxxx)%></description>
    <link>xxxxxxxxxxxxxxxxx</link>
    <pubDate>xxxxxxxxxxxxxx</pubDate>
    </item>
    <%
    Repeat1__index=Repeat1__index+1
    Repeat1__numRows=Repeat1__numRows-1
    rs_blog_feed.MoveNext()
    Wend
    %>

    <%
    rs_blog_feed.Close()
    Set rs_blog_feed = Nothing
    %>
    </channel>
    </rss>










    On Apr 25, 12:10 pm, Billy Barth <> wrote:
    > I would like to create a RSS feed from my Access Database.
    > I have an Access table for news which has the article title, date, and
    > story in it. What I would like to do is pull the data from there into an
    > xml file to create the RSS feed, so when users upload a news story it
    > automatically updates in the xml file. Can this be done with asp? and where
    > should I look for more information. Thanks, This is a great group.
    >
    > --
    > Posted via a free Usenet account fromhttp://www.teranews.com
     
    Brett_A, Apr 26, 2007
    #2
    1. Advertising

  3. Billy Barth

    Mike Brind Guest

    "Billy Barth" <> wrote in message
    news:Xns991D84DF985CCbjbarthgmailcom@66.150.105.47...
    >I would like to create a RSS feed from my Access Database.
    > I have an Access table for news which has the article title, date, and
    > story in it. What I would like to do is pull the data from there into an
    > xml file to create the RSS feed, so when users upload a news story it
    > automatically updates in the xml file. Can this be done with asp? and
    > where
    > should I look for more information. Thanks, This is a great group.
    >


    Was used to pull all stories entered within the last 30 days. Obviously you
    can alter the date range, regional settings (eg language, date format, time)
    and links according to your needs. The result validates against RSS 2.0.

    <%
    function tidyxml(text)
    text = replace(text,Chr(180),"'")
    text = replace(text,"&","&amp;")
    text = replace(text,"'","'")
    text = replace(text,"'","'")
    text = replace(text,""","""")
    text = replace(text,""","""")
    text = replace(text,"<br>","")
    text = replace(text,"-","-")
    text = replace(text,"<","<")
    text = replace(text,">",">")
    tidyxml = text
    End function

    thedate = Month(CurrDate) & "/" & Day(CurrDate) & "/" & Year(CurrDate)
    if Month(CurrDate) = 1 then
    thepreviousdate = "12/" & Day(CurrDate) & "/" & (Year(CurrDate)-1)
    else
    thepreviousdate = (Month(CurrDate)-1) & "/" & Day(CurrDate) & "/" &
    Year(CurrDate)
    end if

    newsSQL = "SELECT StoryID,Headline, Description, DateEntered FROM Stories "
    & _
    WHERE DateEntered BETWEEN " & _
    "#" & thedate & " 23:59:59# And #" & thepreviousdate & " 0:0:1#;"
    set conn = server.createobject("ADODB.Connection")
    conn.Open connectionstring
    set rsNews = conn.execute(newsSQL)

    If Not rs.EOF Then

    arrNews = rsNews.GetRows()
    rsNews.close : set rsNews = nothing
    conn.close : set conn = nothing

    filename = "rss/rss.xml"
    set fso = createobject("scripting.filesystemobject")
    set xmlfile = fso.createtextfile(server.mappath(filename),true)

    xmlfile.writeline("<?xml version=""1.0"" encoding=""iso-8859-1""?>")
    xmlfile.writeline("<rss version=""2.0"">")
    xmlfile.writeline("<channel>")
    xmlfile.writeline("<title>Title</title>")
    xmlfile.writeline("<link>Url</link>")
    xmlfile.writeline("<description>Description</description>")
    xmlfile.writeline("<language>en-gb</language>")

    CurrDate = Now()
    CurrHour = Hour(CurrDate)
    if CurrHour < 10 then CurrHour = "0" & CurrHour
    CurrMin = Minute(CurrDate)
    if CurrMin < 10 then CurrMin = "0" & CurrMin
    CurrSec = Second(CurrDate)
    if CurrSec < 10 then CurrSec = "0" & CurrSec
    CurrDateT = WeekdayName(Weekday(CurrDate), TRUE) & ", " & Day(CurrDate) & "
    " & _
    MonthName(Month(CurrDate), TRUE) & " " & Year(CurrDate) & " " & _
    CurrHour & ":" & CurrMin & ":" & CurrSec & " GMT"

    xmlfile.writeline("<lastBuildDate>" & CurrDateT & "</lastBuildDate>")
    xmlfile.writeline("<copyright>Copyright: (C) " & Year(Now()) & "
    CopyrightOwner</copyright>")

    for i = 0 to ubound(arrNews,2)
    PubDate = arrNews(3,i)
    PubHour = Hour(PubDate)
    if PubHour < 10 then PubHour = "0" & PubHour
    PubMin = Minute(PubDate)
    if PubMin < 10 then PubMin = "0" & PubMin
    PubSec = Second(PubDate)
    if PubSec < 10 then PubSec = "0" & PubSec

    PubDateT = WeekdayName(Weekday(PubDate), TRUE) & ", " & Day(PubDate) & " " &
    _
    MonthName(Month(PubDate), TRUE) & " " & Year(PubDate) & " " & _
    PubHour & ":" & PubMin & ":" & PubSec & " GMT"

    xmlfile.writeline("<item>")
    xmlfile.writeline("<title>" & tidyxml(arrNews(1,i)) & "</title>")
    xmlfile.writeline("<description>" & tidyxml(arrNews(2,i)) &
    "</description>")
    xmlfile.writeline("<link>URL/story.asp?id=" & arrNews(0,i) & "</link>")
    xmlfile.writeline("<guid isPermaLink=""false"">URL/story.asp?id=" &
    arrNews(0,i) & "</guid>")
    xmlfile.writeline("<pubDate>" & PubDateT & "</pubDate>")
    xmlfile.writeline("</item>")
    next
    xmlfile.writeline("</channel>")
    xmlfile.writeline("</rss>")
    set fso = nothing

    Else
    rs.close
    End If
    %>
     
    Mike Brind, Apr 26, 2007
    #3
  4. Billy Barth

    Billy Barth Guest

    "Mike Brind" <> wrote in
    news::

    >
    > "Billy Barth" <> wrote in message
    > news:Xns991D84DF985CCbjbarthgmailcom@66.150.105.47...
    >>I would like to create a RSS feed from my Access Database.
    >> I have an Access table for news which has the article title, date,
    >> and story in it. What I would like to do is pull the data from there
    >> into an xml file to create the RSS feed, so when users upload a news
    >> story it automatically updates in the xml file. Can this be done with
    >> asp? and where
    >> should I look for more information. Thanks, This is a great group.
    >>

    >
    > Was used to pull all stories entered within the last 30 days.
    > Obviously you can alter the date range, regional settings (eg
    > language, date format, time) and links according to your needs. The
    > result validates against RSS 2.0.
    >
    > <%
    > function tidyxml(text)
    > text = replace(text,Chr(180),"'")
    > text = replace(text,"&","&amp;")
    > text = replace(text,"'","'")
    > text = replace(text,"'","'")
    > text = replace(text,""","""")
    > text = replace(text,""","""")


    >
    >


    Thanks!

    --
    Posted via a free Usenet account from http://www.teranews.com
     
    Billy Barth, Apr 30, 2007
    #4
  5. Billy Barth

    Guest

    On Apr 25, 6:10 pm, Billy Barth <> wrote:
    > I would like to create a RSS feed from my Access Database.
    > I have an Access table for news which has the article title, date, and
    > story in it. What I would like to do is pull the data from there into an
    > xml file to create the RSS feed, so when users upload a news story it
    > automatically updates in the xml file. Can this be done with asp? and where
    > should I look for more information. Thanks, This is a great group.


    I have created a class in ASP that enables you to easily create Atom
    feeds:

    http://www.codercow.com/AtomXML/

    It is not a complete implementation of the Atom standard, since I
    doubt most people find it relevant to include ie. 2 different sizes of
    images etc. But it is easy to extend to suit your needs.

    Most people just use it out of the box.

    Others have posted the necessary code to retrieve and run through a
    recordset, all you have to do is call the method AddEntry for each
    record.

    Best regards,

    Anders Vind Ebbesen
     
    , May 12, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    5
    Views:
    771
    SpaceGirl
    Feb 25, 2005
  2. Motta
    Replies:
    1
    Views:
    534
    Andy Dingley
    Jun 9, 2004
  3. Scott Gordo
    Replies:
    5
    Views:
    710
  4. Replies:
    1
    Views:
    491
    jamiros
    Apr 15, 2008
  5. Jonathan Groll
    Replies:
    1
    Views:
    280
    Kouhei Sutou
    Jun 27, 2009
Loading...

Share This Page