Evaluate function

HCL Domino Domino Forum
1

Hello all - I need to replace any commas found in the Location and subject fields with a “~”.

When I run my code below I get a Type mismatch on the qry.SQL line. How should I be referencing the Location and subject fields after the evaluate statement

OR is there a better way to accomplish replacing the commas?

Thanks,

Pat

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



Set Session =  New NotesSession

Set db = session.CurrentDatabase

Set View = db.GetView("($Calendar)")

Set doc = view.GetFirstDocument



Const NotesMacro1$ = "@ReplaceSubString(Subject;"","";""~"")"

Const NotesMacro2$ = "@ReplaceSubString(Loation;"","";""~"")"

’ Const NotesMacro3$ = “@ReplaceSubString(Room;”“,”“;”“~”“)”

’ Const NotesMacro4$ = “@ReplaceSubString(Chair;”“,”“;”“~”“)”

If con.ConnectTo("RetirementPortal_Staging", "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)

		xxChair= nam.Common

		xxSubject = Evaluate(NotesMacro1$, doc)    'doc.Subject(0)

		xxLocation = Evaluate(NotesMacro2$, doc)     'doc.Location(0)

		

		' Insert Notes Calendar entried in to the MS SQL RetirementPortal Database CalendarView_Tbl Table

		qry.SQL = "Insert into CalendarView_Tbl (StartDateTime,EndDateTime,Subject,Room,Location,Chair) values ('" & doc.StartDateTime(0) &"','" & doc.EndDateTime(0)  &"','" & xxSubject  &"','" & xxRoom  &"','" & xxLocation  &"','" & xxChair & "')"  

		

		'Messagebox (qry.sql)      

		

		'fileNumber% = Freefile			

		

		Set result.Query = qry

		If Not result.Execute() Then

'			Open "C:\RetirementPortal.TXT" For Binary Access Write As fileNumber%

'			Put fileNumber%, 1, qry.SQL		

			Messagebox (result.GetErrorMessage(DB_LASTERROR) & qry.SQL)

			'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

2

Subject: Evaluate function

Evaluate returns a variant array, so reference the first member,eg xxSubject(0).

But why are you evaluating a formula anyway? There’s a perfectly good Replace method in lotusscript (which differs slightly from the formula in that it doesn’t do arrays, but you’re not looking for that anwyay)

Dan

3

Subject: Evaluate function

Check this lineConst NotesMacro2$ = “@ReplaceSubString(Loation;”“,”“;”“~”“)”

Loation…?.Because of which this macro may be null which might be causing mismatch error. Try correcting it and see if you get the same mismatch error.