Hi to all
im doing a sql select into invoice document interface, my problem comes when im saving the last record from the record sets , the last details save 2 times , heres the code
Private Sub ProcessInvoiceDetail()
Try
Dim oRS As SAPbobsCOM.Recordset
Dim xcount, nLnDtls As Integer
Dim Fpass As Boolean = True
oRS = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
Dim oInvoice As SAPbobsCOM.Documents
oInvoice = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices)
oRS.DoQuery(SQLStr)
Dim RecCnt As Integer = oRS.RecordCount
xcount = 1
oRS.MoveFirst()
For nLnDtls = 1 To RecCnt
oInvoice.DocType = SAPbobsCOM.BoDocumentTypes.dDocument_Service
oInvoice.CardCode = oRS.Fields.Item("CardCode").Value
oInvoice.Comments = xcount.ToString & " Data Interface = " & oRS.Fields.Item("LineId").Value & " of " & oRS.Fields.Item("U_InstTerms").Value
oInvoice.NumAtCard = oRS.Fields.Item("U_AcctNo").Value
oInvoice.DocDate = oRS.Fields.Item("DueDate").Value
oInvoice.DocDueDate = oRS.Fields.Item("DueDate").Value
'--
oInvoice.Lines.ItemDescription = "Financial Income For: " & oRS.Fields.Item("DueDate").Value
oInvoice.Lines.AccountCode = "1132"
oInvoice.Lines.LineTotal = oRS.Fields.Item("PrinDue").Value
oInvoice.Lines.BaseLine = 1
oInvoice.Lines.Add()
'----
Invoice Details Deferred Income -
oInvoice.Lines.ItemDescription = "Deferred Income For : " & oRS.Fields.Item("DueDate").Value
oInvoice.Lines.AccountCode = "4111"
oInvoice.Lines.LineTotal = oRS.Fields.Item("IntrDue").Value
oInvoice.Lines.BaseLine = 2
oInvoice.Lines.Add()
' Dim oDocNum As String
If oInvoice.Add <> 0 Then
MsgBox("Error " + oCompany.GetLastErrorDescription())
Else
'oDocNum = oCompany.GetNewObjectKey()
'SndB1Msg(oDocNum, SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes, "New GRPPO")
'Insert2OLOA(vDocNum, vDocDate, oDocNum, Now.ToShortDateString, vCardCode, vCardName)
End If
oRS.MoveNext()
Next nLnDtls
oRS = Nothing
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
-
end code ---
i already verified the select staterment result from sql and i also check it base on the sample data , the select statement produce 2 records and the it should produce 2 invoice with 2 details each (expected result) , but on sbo the result produce 2 invoice ,the first produce 2 details and the other invoice produce 3 details, the first 2 details are the same.
Could pls somebody help me to find out why the last record produce 3 details with 2 same details
-
sql statements
SQLStr = "SELECT distinct [@CPI_OLOA].U_CardCode AS Cardcode,"
SQLStr = SQLStr & " OCRD.CardName, "
SQLStr = SQLStr & "[@CPI_OLOA].U_AcctNo,"
SQLStr = SQLStr & "[@CPI_LOA1].U_DueDate AS DueDate,"
SQLStr = SQLStr & "[@CPI_LOA1].U_IntrDue AS IntrDue, "
SQLStr = SQLStr & "[@CPI_LOA1].U_PrinDue AS PrinDue, "
SQLStr = SQLStr & "[@CPI_LOA1].U_GlAcct,"
SQLStr = SQLStr & "[@CPI_LOA1].LineId,"
SQLStr = SQLStr & "[@CPI_OLOA].U_InstTerms "
SQLStr = SQLStr & " FROM [@CPI_OLOA] INNER JOIN "
SQLStr = SQLStr & " OCRD ON [@CPI_OLOA].U_CardCode = OCRD.CardCode INNER JOIN "
SQLStr = SQLStr & "[@CPI_LOA1] ON [@CPI_OLOA].DocEntry = [@CPI_LOA1].DocEntry"
SQLStr = SQLStr & " WHERE [@CPI_LOA1].U_DueDate BETWEEN '" & Now.ToShortDateString & "' AND '" & Now.ToShortDateString & "'"
SQLStr = SQLStr & " ORDER BY [@CPI_LOA1].U_DueDate"
-
of on statement
thanks
Loren