I am writing code to export data from a Notesdatabse to a table in Microsoft SQL-server.I have set up the ODBC-connection succeccfully, for I am able to write new rows to the SQL-table.But I get an error when writing in a row which already exists.
I get this error: an error LS:DO-ODBC could not complete the requested operation. (720) has occurred in line 125 in Agent.
Line 125 is the result.UpdateRow.
I have sufficient rights, for I am able to create new rows in the same table.
My code:
Sub Initialize
Print “Exportagent is about to start running * * *”
On Error Goto Errhandler3
Dim con As New ODBCConnection 'Make ODBC Connection
If Not con.ConnectTo(“Prijslijsten”,“Export”,“exportlga”) Then
Print “Could not connect to prijslijsten”
Exit Sub
End If
Print “Connected to prijslijsten”
Dim qry As New ODBCQuery 'Define ODBC Query
Set qry.Connection = con
qry.SQL = “SELECT * FROM staffl”
Dim result As New ODBCResultSet 'Set Query property of ODBCResultSet
Dim RetCode As Integer
Dim waarde As String
Set result.Query = qry
result.Execute 'Execute the ODBC Query
result.LastRow
Dim s As New NotesSession
Dim db As NotesDatabase
Set db=s.CurrentDatabase
Dim dossierdb As New NotesDatabase(db.Server,“dossierbeheer.nsf”)
Dim plijstview As NotesView
Dim plijstcoll As NotesViewEntryCollection
Dim plijstentry As NotesViewEntry
Dim plijstdoc As NotesDocument
Dim prijslijst As String, tariefjaar As String, artcode As String
Set plijstview=dossierdb.GetView(“prijslijstenmodified”)
Set plijstcoll=plijstview.AllEntries
Set plijstentry=plijstcoll.GetFirstEntry
While Not (plijstentry Is Nothing)
Set plijstdoc=plijstentry.Document
If plijstdoc.form(0)="prijslijst" Then
prijslijst=plijstdoc.prijslijst(0)
tariefjaar=plijstdoc.tariefjaar(0)
artcode=plijstdoc.GetItemValue("artcode1")
qry.SQL = "SELECT * FROM staffl where prijslijst='"+prijslijst+"' AND artcode='"+artcode+"' AND YEAR(validfrom)='"+tariefjaar+"'"
Set result.Query = qry
result.Execute 'Execute the ODBC Query
result.FirstRow
Call result.SetValue("bedr1",plijstdoc.GetItemValue("bedr1_1")(0))
result.UpdateRow --> HERE IS THE ERROR
End If
Set plijstentry=plijstcoll.GetNextEntry(plijstentry) 'de volgende entry in de view
Wend
result.Close(DB_CLOSE)
con.Disconnect
Errhandler3:
Print "An Error " & Error$ & "( " & Err & ") has occurred on line " & Erl & " in ExportAgent. "
Exit Sub
End Sub
Koos van Harmelen