Create an iCal file from your data using ASP
June 23, 2010
ASP | Web Building
Google Calendar and many other calendar apps will import or subscribe to iCal format files (.ics) If your event data is locked away in a database, you may find it useful to display and distribute it using Google's extensive calendaring features. Following is an example of an ASP file that will write an iCal file. Generate the iCal file in a web-accessible directory, then have Google Calendar or other iCal compatible application subscribe to it.
More after the jump...
Ads by Google
Posted by ellen at June 23, 2010 05:56 PM 


<title>Generates an ical feed for HREG-HFST classes</title>


<%@LANGUAGE="VBSCRIPT"%>

  <!--#include file=".\Connections\yourdatabase.asp" -->

 
  <%

  'see for more info on the lines below http://bytes.com/topic/asp-classic/answers/637751-create-rss-feed-data-database

  CONST ForReading   = 1 

  CONST ForWriting   = 2 

  CONST ForAppending = 8 


function tidyxml(text)

  If text <> "" Then

  text = replace(text,Chr(180),"'")

  text = replace(text,chr(10),"")

  text = replace(text,chr(13),"")

  text = replace(text,"&","&amp;")

  text = replace(text,"'","'")

  text = replace(text,"'","'")

  text = replace(text,"<b>","")

  text = replace(text,"</b>","")

  text = replace(text,"<p>"," ")

  text = replace(text,"</p>"," ")

  text = replace(text,"<br>"," ")

  text = replace(text,"<br/>"," ")

  text = replace(text,"<br />"," ")

  text = replace(text,"<font>","")

  text = replace(text,"</font>","")

  text = replace(text,"<a href=""mailto:","")

  text = replace(text,"<ahref=""mailto:","")

  text = replace(text,"<a href="," ")

  text = replace(text,"</a>"," ")

  text = replace(text,"<strong>","")

  text = replace(text,"</strong>","")

  text = replace(text,"<img>","")

text = replace(text,"</img>","")

text = replace(text,""">"," ")

text = replace(text,"-","-")

'text = replace(text,"<"," &lt;")

'text = replace(text,">","&gt;")


'text = replace(text,"<","<")

  'text = replace(text,">",">")

  tidyxml = text

  end if

  End function


dim rs, rs_cmd, rs_numRows,conn_numRows, FirstName, LastName, d_todayYR, d_todayMONTH, d_todayDAY, descSHORT,gnumber

  Set rs_cmd = Server.CreateObject ("ADODB.Command")

  rs_cmd.ActiveConnection = MM_YOURDATABASE_STRING

  

  rs_cmd.Prepared = true

  


rs_cmd.CommandText = "SELECT la.code as code, lai.code as instcode, lai.id as instid, la.title as title, la.outlinelink as link,  to_char(laim.meetingstart, 'YYYYMMDD' ) as startdate,  to_char(new_time(laim.meetingstart, 'GMT','EDT'), 'HH24MISS') as starttime, to_char(laim.meetingend, 'YYYYMMDD') as enddate, to_char(new_time(laim.meetingend, 'GMT','EDT'), 'HH24MISS') as endtime,  res.name as room,  la.description as gdescription,   loc.name as location, loc.address as address FROM drlearningactivity la, DRLEARNINGACTIVITYINSTANCE lai, DRLAINSTANCEMEETING laim, drlocation loc, drresourcereservation reser, drresource res WHERE (la.code like 'HFST%' or la.code like 'HREG%') and la.id=lai.learningactivityid and laim.LEARNINGACTIVITYINSTANCEID = lai.id and lai.locationid=loc.id and reser.lainstancemeetingid=laim.id and reser.resourceid=res.id and res.resourcetypeid=2 and laim.meetingstart >=current_date order by la.code,lai.code"

    

  set rs = rs_cmd.execute 


 


d_todayYR=Year(Date) 

  d_MONTH=Month(Date)

  d_todayMONTH=String(2 - Len(d_Month), "0") & d_MONTH

  d_DAY=Day(Date)

  d_todayDAY = String(2 - Len(d_Day), "0") & d_DAY




d_todayHOUR=Hour(Time)

  d_todayMINUTE=Minute(Time)


d_timestamp=d_todayYR & d_todayMONTH & d_todayDAY & d_todayHOUR & d_todayMINUTE

  response.write("RSS feed is being updated. This is just a demo  - NOT FINAL ." & " " & d_timestamp & "<br/>")

  

  'See http://icalendar.rubyforge.org for explanation of some of the items below

  filename = "rss/icalDemo.ics"

  set fso = createobject("scripting.filesystemobject")

  set icsfile = fso.createtextfile(server.mappath("icalDemo.ics"),true)

  icsfile.writeline("BEGIN:VCALENDAR")

  icsfile.writeline("PRODID:MLearning") 

  icsfile.writeline("VERSION:2.0")

  icsfile.writeline("CALSCALE:GREGORIAN")

  icsfile.writeline("METHOD:PUBLISH")

  icsfile.writeline("X-WR-CALNAME:Upcoming HREG and HFST courses in MLearning")

  icsfile.writeline("X-WR-TIMEZONE:America/New_York")

  icsfile.writeline("BEGIN:VTIMEZONE")

  icsfile.writeline("TZID:America/New_York")

  icsfile.writeline("X-LIC-LOCATION:America/New_York")

  icsfile.writeline("BEGIN:DAYLIGHT")

  icsfile.writeline("TZOFFSETFROM:-0500")

  icsfile.writeline("TZOFFSETTO:-0400")

  icsfile.writeline("TZNAME:EDT")

  icsfile.writeline("DTSTART:19700308T020000")

  icsfile.writeline("RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=2SU")

  icsfile.writeline("END:DAYLIGHT")

  icsfile.writeline("BEGIN:STANDARD")

  icsfile.writeline("TZOFFSETFROM:-0400")

  icsfile.writeline("TZOFFSETTO:-0500")

  icsfile.writeline("TZNAME:EST")

  icsfile.writeline("DTSTART:19701101T020000")

  icsfile.writeline("RRULE:FREQ=YEARLY;BYMONTH=11;BYDAY=1SU")

  icsfile.writeline("END:STANDARD")

  icsfile.writeline("END:VTIMEZONE")

  icsfile.writeline("TZNAME:EST")

  icsfile.writeline("TZNAME:EST")


 


While Not rs.EOF 

  startdate = rs.Fields.Item("startdate").Value

  enddate = rs.Fields.Item("enddate").Value

  starttime = rs.Fields.Item("starttime").Value

  endtime = rs.Fields.Item("endtime").Value

  room = rs.Fields.Item("room").Value

  location = rs.Fields.Item("location").Value

  address = rs.Fields.Item("address").Value

  location = rs.Fields.Item("location").Value

  code = rs.Fields.Item("code").Value

  instid = rs.Fields.Item("instid").Value

  instcode = rs.Fields.Item("instcode").Value

  title = rs.Fields.Item("title").Value

  link = rs.Fields.Item("link").Value


'truncate the description at the ### symbols

  gdescription = rs.Fields.Item("gdescription").Value

  gnumber = Instr(gdescription,"###") -1

  'Response.Write(gdescription)

  If gnumber > 0 Then 

  gdescription = Left(gdescription,gnumber)

  ElseIf gnumber=0 Then 

  gdescription = gdescription


End If

 
  


icsfile.writeline("BEGIN:VEVENT")

  icsfile.writeline("DTSTART;TZID=America/New_York:" & startdate & "T" & starttime)

  icsfile.writeline("DTEND;TZID=America/New_York:" & enddate &"T" & endtime)

  icsfile.writeline("DTSTAMP:20100521T162341Z")

  icsfile.writeline("UID:"& code &instcode)

  icsfile.writeline("CREATED:20100523T175642Z")

  icsfile.writeline("DESCRIPTION:"& code &"-" & instcode & " " & tidyxml(gdescription) & " http://yourserver.com?quot; & instid)

  icsfile.writeline("LAST-MODIFIED:20100430T153745Z")

  icsfile.writeline("LOCATION:" & address & " " & location & " " & room)

  icsfile.writeline("SUMMARY:"& code &"-" & instcode  &" " & title & "\n")

  icsfile.writeline("TRANSP:TRANSPARENT")

  icsfile.writeline("SEQUENCE:2")

  icsfile.writeline("URL:http://your.server.com?event=" & instid )

  icsfile.writeline("END:VEVENT")


rs.movenext

  wend

  icsfile.writeline("END:VCALENDAR")

  

  rs.close()

  set rs = nothing

  set fso = nothing 


 


%>
This produces an iCal file similar to the following file: 
BEGIN:VCALENDAR

PRODID:MLearning

VERSION:2.0

CALSCALE:GREGORIAN

METHOD:PUBLISH

X-WR-CALNAME:Upcoming HREG and HFST courses 

X-WR-TIMEZONE:America/New_York

BEGIN:VTIMEZONE

TZID:America/New_York

X-LIC-LOCATION:America/New_York

BEGIN:DAYLIGHT

TZOFFSETFROM:-0500

TZOFFSETTO:-0400

TZNAME:EDT

DTSTART:19700308T020000

RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=2SU

END:DAYLIGHT

BEGIN:STANDARD

TZOFFSETFROM:-0400

TZOFFSETTO:-0500

TZNAME:EST

DTSTART:19701101T020000

RRULE:FREQ=YEARLY;BYMONTH=11;BYDAY=1SU

END:STANDARD

END:VTIMEZONE

TZNAME:EST

TZNAME:EST

BEGIN:VEVENT

DTSTART;TZID=America/New_York:20100713T090000

DTEND;TZID=America/New_York:20100713T120000

DTSTAMP:20100521T162341Z

UID:HFST-100020039

CREATED:20100523T175642Z

DESCRIPTION:HFST-10002-0039 Description goes here

LAST-MODIFIED:20100430T153745Z

LOCATION:address goes here

SUMMARY:HFST-10002-0039 Brio Query Basics\n

TRANSP:TRANSPARENT

SEQUENCE:2

URL:http://yourserver.com?11223

END:VEVENT 

BEGIN:VEVENT

DTSTART;TZID=America/New_York:20100629T123000

DTEND;TZID=America/New_York:20100629T153000

DTSTAMP:20100521T162341Z

UID:HREG-100270001

CREATED:20100523T175642Z

DESCRIPTION:HREG-10027-0001 DESCRIPTION: Description goes here

LAST-MODIFIED:20100430T153745Z

LOCATION:address goes here

SUMMARY:HREG-10027-0001 Title goes here

TRANSP:TRANSPARENT

SEQUENCE:2

URL:http://yourserver.com?29293

END:VEVENT

END:VCALENDAR

Ads by Google

2 Comments

Tonya | June 26, 2010 3:34 PM

Haha! This sounds familiar ;-)

Ellen replied to comment from Tonya | June 26, 2010 10:56 PM

LOL, don't I know you from somewhere?

 Ads by Google

Related Entries:


 RSS   |   Contact Me
Ads by Google