I’m writing a web app that needs to use an agent on the server to sync data between a Lotus Notes db and a db2 db on a remort iSeries.
Anyway, i’m using the LC Connector classes to get to both the Notes db and iSeries db. The problem I’m having is that because it’s not a 1-to-1 relationship, I have to do some string manipulation to make certain fields work.
I continually get a type mismatch when trying to get 2 sides of a string. The string in question is built like this, “401 - CRJ”. I’m trying to use the Left and Right functions to get the first and second part of the string.
Here’s a scaled down version of the code…HELP!
Thanks ahead of time…
Andy
Sub MovetoAS400
On Error Goto errTrap
Dim extCon As New LCConnection("db2")
extCon.database = "ROADRUNR"
extCon.userid = "ID"
extCon.password = "PWD"
extCon.Metadata = "ANDYMONTAL.IRREGRPT"
extCon.MapByName = True
extCon.NoJournal = True
extCon.Connect
Dim extFlds As New LCFieldList
Call extCon.Select(Nothing, 1, extFlds)
Dim ses As New NotesSession
Dim db As NotesDatabase
Set db = ses.CurrentDatabase
Dim notesCon As New LCConnection("notes")
notesCon.Server = db.Server ' create LC connection to current database.
notesCon.Database = db.FilePath
notesCon.Metadata = "AdminIrreg"
notesCon.MapByName = True
notesCon.Connect
' Select all Notes documents that use the Irreg form
Dim notesFlds As New LCFieldList
Call notesCon.Select(Nothing, 1, notesFlds)
Dim srcFileNo As LCField
Dim srcFltNo As LCField
Dim srcAcType As LCField
Set srcFileNo = notesFlds.Lookup("File_Number")
Set srcFltNo = notesFlds.Lookup("Fleet_Number")
Call extFlds.MapName(notesFlds, "File_Number", "IRRFILENO")
' Designate FileNo as a key field (for the Update command).
srcFileNo.Flags = srcFileNo.Flags Or LCFIELDF_KEY
Dim destFltNo As LCField
Dim destAcType As LCField
Set destFltNo = extFlds.Append("IRRFLTNO", LCTYPE_TEXT)
Set destAcType = extFlds.Append("IRRACTYPE", LCTYPE_TEXT)
Dim count As Long
On Error Goto errTrap
’ Loop thru all the Notes records, copying them to the DB2 database.
Do While notesCon.Fetch(notesFlds, 1, 1)
destFltNo.Text = Left(srcFltNo, 3)
destAcType.Text = Right(srcFltNo, 3)
’ Update DB2 database. If Update fails, the record must not yet exist, so Insert instead.
If extCon.Update(extFlds, 1, 1) = 0 Then
Call extCon.Insert(extFlds, 1, 1)
Print srcFileNo.Value(0) & ": inserted"
Else
Print srcFileNo.Value(0) & ": updated"
End If
Loop
Exit Sub
errTrap:
Msgbox "error " & Err & " line " & Erl & ": " & Error
Exit Sub
End Sub