Hello all:I have an agent that loops on a notes view to update its records from an oracle table ,every time I run this code it works fine for the first document and gives me an error “Field count mismatch” in the second document
here is my code,sorry if it is long
Sub Initialize
' This agent retrieves the OPS Header , Details , and Country Name Data.
Msgbox "Agent : Retrive OPS Tracking Data From Oracle --> START"
' Notes Definitions
Dim NSession As New NotesSession
Dim db As Notesdatabase
Dim agent As NotesAgent
Set agent = NSession.CurrentAgent
Set db=NSession.currentdatabase
Dim detaildoc As NotesDocument
Dim HeaderDoc As Notesdocument
Dim HeaderView As NotesView
Dim DetailView As NotesView
Dim settdoc As NotesDocument
Set settdoc = db.GetProfileDocument("Settings")
'lsxlc Definitions
Dim src As LCConnection
Dim Session As New LCSession
Dim FieldOut As New LCFieldList
Dim Field As LCField
Dim count As Integer
Dim SQLString As String
Dim flag
Dim i As Integer
i=0
session.ClearStatus
' Connect to oracle
Set src = New LCConnection ("oracle") 'Create Connection object
src.server = settdoc.DataSource(0)
src.Userid = settdoc.UserName(0)
src.Password = settdoc.Password(0)
src.Connect 'Connect to data source
'setting header and detail documents Lotus Notes wise
Set settdoc =db.GetProfileDocument("Settings")
Set headerview =db.GetView("HeaderSubmittedtoBMSSpecialist")
Set detailview=db.GetView("Details Submitted to BMS Specialist By UNID")
Set headerdoc=headerview.GetFirstDocument
While Not headerdoc Is Nothing
Set detaildoc=detailview.GetDocumentByKey(headerdoc.unid(0))
While Not detaildoc Is Nothing
Dim detdetailview As NotesView
Set detdetailview=db.GetView ("Web Tracking Product by detail ID")
Dim DelCol As NotesDocumentCollection
Set delcol=detdetailview.GetAllDocumentsByKey(detaildoc.DocUNID(0))
Dim shipping As String
Call delcol.RemoveAll(True)
SQLString = "SELECT SHIPDOC_AWB_NO,REQHDR_DIST_ETA,INVDTL_INVHDR_INV_NO,DOCS_DIST_REF,SUPPROD_LIST_NUM,SUPPLIER_CD,ORDHDR_OUR_REF,ORDHDR_EXCH_RATE,ORDHDR_ORD_DATE,ORDDTL_PACK_SIZE,ORD_STS_CODE,ORDHDR_SUPP_REF,CURRENCY_CD,ORDHDR_SUPP_ETD,ORDHDR_SUPP_ETA,ORDHDR_SHIPPING_MODE,BANK_NAME,ORDHDR_ACK_DATE,ORDHDR_ORDER_AMT,ORDDTL_PROD_QTY,ORDDTL_UNIT_PRICE,SUPPROD_DSCR,UNITMES_DESCR,INVDTL_INVDTL_SHPD_QTY,INVDTL_INVDTL_INVCD_AMT,INVHDR_INVHDR_EXCH_RATE FROM ORDTRACKING where DOCS_DIST_REF =' " & Detaildoc.Reference(0) & "' and DISTRIB_CD= '" &detaildoc.DistributerCode(0)& " 'and SUPPLIER_CD='"&detaildoc.Supplier(0)&"'and SUPPROD_LIST_NUM='"&detaildoc.Product(0)&"'"
Msgbox SQLString
count =src.Execute(SQLString,FieldOut) 'Execute the Query
Msgbox("after excuting SQLString")
count = src.Fetch(FieldOut)
Msgbox count
If count> 0 Then
While (count > 0)
Dim itemDoc As NotesDocument
Set itemDoc=db.CreateDocument
itemDoc.form="Order Tracking"
'fill item document from oracle
itemDoc.Order_Status=FieldOut.ORD_STS_CODE(0)
itemDoc.AWBNO= FieldOut.SHIPDOC_AWB_NO(0)
itemDoc.Track_Supplier= FieldOut.SUPPLIER_CD (0)
itemDoc.Track_Description= FieldOut.SUPPROD_DSCR (0)
itemDoc.Product_list= FieldOut.SUPPROD_LIST_NUM (0)
itemDoc.Pack_Size= FieldOut.ORDDTL_PACK_SIZE (0)
itemDoc.UOM= FieldOut.UNITMES_DESCR (0)
itemDoc.Unit_Price= FieldOut.ORDDTL_UNIT_PRICE (0)
itemDoc.Order_Ref= FieldOut.DOCS_DIST_REF (0)
itemDoc.Order_Cur= FieldOut.CURRENCY_CD (0)
itemDoc.BMS_Ref= FieldOut.ORDHDR_OUR_REF (0)
itemDoc.Bank= FieldOut.BANK_NAME (0)
itemDoc.Sup_Ref= FieldOut.ORDHDR_SUPP_REF (0)
If FieldOut.ORDHDR_SHIPPING_MODE (0)="O" Then
shipping="Ocean"
Elseif FieldOut.ORDHDR_SHIPPING_MODE (0)="A" Then
Shipping="Air"
Elseif FieldOut.ORDHDR_SHIPPING_MODE (0)="T" Then
Shipping="Truck"
End If
itemDoc.Shipping_Mode=shipping
itemDoc.Order_Date= FieldOut.ORDHDR_ORD_DATE (0)
itemDoc.Requested_ETD= FieldOut.REQHDR_DIST_ETA (0)
'**** itemDoc.BMS_Reciving_Date= FieldOut.SUPPLIER_CD (0)
'**** itemDoc.BMS_Order_Date= FieldOut.SUPPLIER_CD (0)
itemDoc.Sup_Ack_Date= FieldOut.ORDHDR_ACK_DATE (0)
itemDoc.Supplier_ETD= FieldOut.ORDHDR_SUPP_ETD (0)
itemDoc.Order_Dollar_Amount= FieldOut.ORDHDR_ORDER_AMT (0)* FieldOut.ORDHDR_EXCH_RATE (0)
itemDoc.Invoice_No= FieldOut.INVDTL_INVHDR_INV_NO (0)
itemDoc.Invoice_date= FieldOut.ORDHDR_SUPP_ETA (0)
itemDoc.Shipped_Qty= FieldOut.INVDTL_INVDTL_SHPD_QTY (0)
itemDoc.Recived_Qty= FieldOut.INVDTL_INVDTL_SHPD_QTY (0)
Msgbox("Count count>0")
If FieldOut.ORD_STS_CODE(0)="M" Then
'getting data from invoice
itemDoc.Order_Amount= FieldOut.INVDTL_INVDTL_INVCD_AMT (0)
itemDoc.Order_Qty= FieldOut.INVDTL_INVDTL_SHPD_QTY(0)
itemDoc.Exchange_Rate= FieldOut.INVHDR_INVHDR_EXCH_RATE (0)
Else
'no invoice getting data from order
itemDoc.Order_Amount= FieldOut.ORDHDR_ORDER_AMT (0)
itemDoc.Order_Qty= FieldOut.ORDDTL_PROD_QTY (0)
itemDoc.Exchange_Rate= FieldOut.ORDHDR_EXCH_RATE(0)
End If
'Copy all system information
itemDoc.DocUNID=detaildoc.DocUNID(0)
itemDoc.Creator=detaildoc.Creator(0)
itemDoc.Author=detaildoc.Author(0)
itemDoc.Location=detaildoc.Location(0)
itemDoc.Delegator=detaildoc.Delegator(0)
itemDoc.RequisitionDate=detaildoc.RequisitionDate(0)
itemDoc.Reader=detaildoc.Reader(0)
itemDoc.FReader=detaildoc.FReader(0)
itemDoc.CountryCode=detaildoc.CountryCode(0)
itemDoc.DistributerCode=detaildoc.DistributerCode(0)
itemDoc.Manufact=detaildoc.Manufact(0)
flag=itemDoc.computewithform(False,False)
Call itemDoc.Save( True,False )
count = src.Fetch(FieldOut)
Wend
End If
Set detaildoc=detailview.GetNextDocument(detaildoc)
Wend
Set headerdoc=headerview.GetNextDocument(headerdoc)
Wend
End Sub
thanks
Dalia