"…and not for five minutes will I be distracted from the wonder…"

Create iCal file with ASP and MS SQL Server

Uncategorized — d-ashes on October 1, 2004 at 10:12 am

I’ve been wanting to port Lemuria’s events database (SQL Server) into iCal (a standard calendar file format that Apple originated and has been gaining ground in other OS’s since its inclusion in Mozilla’s Calendar Plugin and Sunbird)
for a while now, but never ran across an ASP script that wrote the data
into the iCal format. In case there’s really not one out there I’m
posting mine. Working on a PHP/MySQL version that I should have up in a
couple of days. Feel free to drop an email if you have trouble with it or questions.

The script:

<%

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'@ yet ANOTHER ASHESANDWATER.COM travesty:
'@ --------------------------------
'@ this is a quick and dirty script to take a
'@ mssql server table that stores events and
'@ to output it into an ical file...i make no
'@ guarantees, save that it worked for me...
'@ use it, abuse it, make it better... you
'@ don't have to mention that you borrowed it
'@ from me...one 'small' issue is that the
'@ calendar writing portion does not do
'@ 'folding' (keeping lines shorter than
'@ 75 octets)...it still works but is
'@ not within the rules defined by the
'@ standard (RFC 2445)...not too hard to fix
'@ but i was in a hurry...if you think about it
'@ do let me know of any improvements that
'@ you make on it or any problems with the
'@ code i posted...
'@ wjord --->davec(at)ashesandwater.com
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

'this function converts mssql server's default date/time format to the ical standard (YYYYMMDDTHHMMSS)
function icalDateTime(theDateTime)
theYear=datePart("yyyy",theDateTime)
theMonth=datePart("m",theDateTime)
'this adds leading zeroes to theMonth if needed
if len(theMonth)<2 then theMonth= "0" & theMonth
theDay=datePart("d",theDateTime)
'this adds leading zeroes to theDay if needed
if len(theDay)<2 then theDay= "0" & theDay
'converts the time to shortTime format (24 hour)
theTime=formatDateTime(theDateTime,4)
'this gets rid of :'s between time parts and adds seconds to the end
theTime=replace(theTime,":","") & "00"
'this puts it all together
icalDateTime=theYear & theMonth & theDay & "T" & theTime
end function

'this function is a quick fix to escape commas...update this to escape any other characters you come across...i didn't explore this very thorougly

function escaper(theString)
theString=replace(theString,",","\'")
escaper=theString
end function

'instantiate recordset, set db settings, and set sql to get events from the db
Set rsEvents = Server.CreateObject("ADODB.Recordset")
rsEvents.ActiveConnection = //your connection string//
rsEvents.CursorType = 0
rsEvents.CursorLocation = 2
rsEvents.LockType = 1
'edit this for your table and field names...i use now() to get only upcoming events
rsEvents.source="SELECT * FROM events WHERE eventDateStart>='" & now() & "';"


'begin building the ical file output into the calendar variable...i just copied the header from the one the mozilla calendar plugin uses...
calendar="BEGIN:VCALENDAR" & vbcrlf
calendar=calendar & "METHOD:PUBLISH" & vbcrlf
calendar=calendar & "VERSION:2.0" & vbcrlf
calendar=calendar & "PRODID:-//Mozilla.org/NONSGML Mozilla alendar V1.0//EN" & vbcrlf

'open the recordset
rsEvents.open()

'loop through each event, adding to the calendar variable for each of them
do while not rsEvents.eof
'begin the event
calendar=calendar & "BEGIN:VEVENT" & vbcrlf
'i rednecked this, i think...i just put guid's into a field in the events table for each record and wrote them here...seems to work fine...for now
calendar=calendar & "UID:" & stripChars(rsEvents("guid")) & vbcrlf
'from here on out just switch out whatever db fields you want to use for each data field in the calendar
calendar=calendar & "SUMMARY:" & escaper(rsEvents("eventTitle"),0) & vbcrlf
calendar=calendar & "URL:" & "http://www.server.com/events.asp?eventID=" & rsEvents("eventID") & vbcrlf
'if you don't need a particular field (besides those required), then just comment out that section and move on like i did below
'calendar=calendar & "DESCRIPTION:" & vbcrl
calendar=calendar & "LOCATION:" & rsEvents("location") & vbcrlf
'i haven't looked into priority values, i just went with what mozilla used by default
calendar=calendar & "PRIORITY:5" & vbcrlf
'same thing here
calendar=calendar & "CLASS:PUBLIC" & vbcrlf
'this may be mozilla calendar plugin specific (???)...it states the event is not recurring...
calendar=calendar & "X-MOZILLA-RECUR-DEFAULT-INTERVAL:0" & vbcrlf
'use the above icalDateTime function to get the date time into the appropriate ical format
calendar=calendar & "DTSTART:" & icalDateTime(rsEvents("eventDateTimeStart")) & vbcrlf
calendar=calendar & "DTEND:" & icalDateTime(rsEvents("eventDateTimeEnd")) & vbcrlf
'i'm not sure what this really does, but i set it to the dateTime that the event was added to the database
calendar=calendar & "DTSTAMP:" & icalDateTime(rsEvents("dateTimeAdded")) & vbcrlf
'same thing here...neither this or the above are probably necessary...maybe..
calendar=calendar & "LAST-MODIFIED:" & icalDateTime(rsEvents("dateTimeAdded")) & vbcrlf
'end the event
calendar=calendar & "END:VEVENT" & vbcrlf
'move to the next record of the db
rsEvents.movenext
loop
'end the calendar
calendar=calendar & "END:VCALENDAR" & vbcrlf
'close the recordset
rsEvents.close()
'be tidy and destroy the recordset too!
set rsEvents=nothing
'write the calendar
response.write(calendar)
%>

0 Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License. | Ashes & Water