Hello All - I have an issue with a multivalued field.
I am pushing data from the mail Calendar view to a sql db.
My import works well except for the StartDateTime field. If there is a repeating entry for each week of the year, then it creates 52 entries that all start at first element of the StartDateTime field. How can I get each of the elements of the CalendarDateTime field to create seperate entries in the sql db?
Thanks
Pat
Here is my current code:
Sub Initialize
Dim Session As NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim xxAppointmentType As Variant
Dim TaskType As Variant
Dim xxLocation As Variant
Dim nam As NotesName
Dim xxChair As Variant
Dim xxRoom As Variant
Dim xxSubject As Variant
Dim ExpValue As Variant
Dim CalEntry As String
Set Session = New NotesSession
Set db = session.CurrentDatabase
Set View = db.GetView("($Calendar)")
Set doc = view.GetFirstDocument
If con.ConnectTo("RetirementPortal_Dev", "sids", "sidsfeed") Then
’ If con.ConnectTo(“RetirementPortal_Staging”, “sids”, “sidsfeed”) Then
’ If con.ConnectTo(“RetirementPortal_Production”, “sids”, “sidsfeed”) Then
Set qry.Connection = con
Do While Not(doc Is Nothing)
TaskType = doc.getItemValue("TaskType")
Set nam = New NotesName(doc.Chair(0))
xxAppointmentType = doc.AppointmentType(0)
xxRoom = doc.Room(0)
xxCalendarDateTime = Evaluate("@Explode(CalendarDateTime)", doc)
xxChair= nam.Common
xxSubject = doc.Subject(0)
If xxAppointmentType<>"3" Then
Goto flag
Else
xxRoom = "Room: " & doc.Room(0)
End If
If xxAppointmentType = "3" Then
xxChair = "Chair: " & xxChair
Elseif TaskType = "2" Then
xxChair = "Owner: " & xxChair
End If
flag:
If Not Isnull(doc.Location) Then
xxLocation = "Location: " & doc.Location(0)
Else
xxLocation = ""
End If
' Update Remedy Last Surveyed date field with the date document was saved
qry.SQL = "Insert into CalendarView_Tbl (StartDateTime,EndDateTime,Subject,Room,Location,Chair) values ('" & xxCalendarDateTime(0) &"','" & doc.EndDateTime(0) &"','" & xxSubject &"','" & xxRoom &"','" & xxLocation &"','" & xxChair & "')"
'Messagebox (qry.sql)
fileNumber% = Freefile
Set result.Query = qry
If Not result.Execute() Then
On Error Resume Next
End If
Set doc=view.getNextDocument(doc)
Loop
Status = con.Disconnect
Else
Messagebox("Could not connect to server")
End If
End Sub