Skip to Content
0
Former Member
Mar 23, 2009 at 05:14 PM

duplicate record save

48 Views

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

'--


Invoice Details for Financial Income--


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