I have an agent in my database that is suppossed to take fields out of my data entry form and store them in a DB2 table. The agent also calls a string replace function to make data acceptable to DB2 table. Everything appears to going fine until I hit my db2Result.Execute line in my code then it goes to my error handler and tells me that I have an unexpected token, but I have combed this code and can find none. Also when I check the DB2 table none of the information is even making it over there. I have simplified the code somewhat, I know it is long, but there are many more fields on my activity form, more columns in my db2 table, and etc, but I am showing the “meaty” parts. Can anyone see what my problem may be. I am kind of new to Agents. Also, when I step it through the debugger and I get a drop down for my db2query and db2result, there is no value for the “connection”, should there be something there telling me that there is a connection? Thanks for any light that can be shed on this!
On Error Goto ErrorCleanUp
Const dataSource$ = "*******"
Const db2UserID$ = “*******”
Const db2UserPW$ = "*******"
Dim db2Con As New ODBCConnection
Dim db2Query As New ODBCQuery
Dim db2Result As New ODBCResultSet
Dim Session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim docCollection As NotesDocumentCollection
Dim doc As NotesDocument
Dim nextDoc As NotesDocument
Dim errorDoc As NotesDocument
'Dim activityTB As String
db2initFlag%= False
Set db = session.currentDatabase
Set view = db.getView( "($InsertDB2)" )
Set doc = view.getFirstDocument
Do While Not ( doc Is Nothing )
Set nextDoc = view.getNextDocument( doc )
If ( db2initFlag% = False ) Then
Call db2Con.ConnectTo(dataSource$, db2UserId$, db2UserPW$ )
If Not( db2Con.IsConnected ) Then
Goto ErrorCleanUp
End If
Set db2Query.Connection = db2Con
Set db2Result.Query = db2Query
db2InitFlag% = True
End If
db2Con.AutoCommit = False
'// define fields here
nField = doc.getItemValue("TrooperNumber")
If nField(0) = "" Then
troopernumber$ = 0000
Else
troopernumber$ = "'" & strReplace (Ucase(nField( 0 ) ),"'","'" ) & "'"
troopernumber$ = strReplace(Ucase(troopernumber$),"'","")
End If
'report year???
rptyr$ = Cstr(Year(Today()))
'rptyr$ = "2004"
nField = doc.getItemValue("Rank")
rank$ = "'" & strReplace (Ucase(nField( 0 ) ),"'","'" ) & "'"
If nField(0) = "" Then
rank$ = "UNK"
End If
'Set SYSADMA.SP_DAILY_ACT_TB Columns
activityTb$ = "SYSADMA.SP_DAILY_ACT_TB"
Column$ = "(DATA_NO,RPT_YR,RANK,ACT_DATE,REGION_CD,TROOP_CD,PARISH_CD_1,PARISH_CD_2,PARISH_CD_3,TEAM_CD"
Column$ = Column$ & ", ST_HOURS,ED_HOURS,TOTAL_HRS_WORKED"
Column$ = Column$ & ", SPECIAL_USE3,SP_DETAIL4,SPECIAL_USE4)"
'Insert into SYSADMA.SP_DAILY_ACT_TB
sqlinsert$ = "INSERT INTO SYSADMA.SP_DAILY_ACT_TB " & Column$ & " VALUES ( " & troopernumber$ & ", " & rptyr$ & ", " & rank$
sqlInsert$ = sqlInsert$ & ", " & actdate$ & ", " & region$ & ", " & section$ & ", " & parishone$ & ", " & parishtwo$ & ", " & parishthree$
sqlInsert$ = sqlInsert$ & ", " & specusedesc2$ & ", " & specuse3$ & ", " & specusedesc3$ & ", " & specuse4$ & ", " & specusedesc4$ & ")"
db2Query.SQL = Ucase( sqlInsert$ )
'Print sqlInsert$
db2Result.Execute
status = db2Con.CommitTransactions
'Get next doc
Set doc = nextDoc
Loop
Messagebox "The insert is complete.",,"Success:"