Nov 21, 2018 at 06:27 PM

DIAPI Field Update -- Returning Untouched Line as Error

204 Views

Hey Everyone,

Currently working with a bit of DIAPI code that started returning weird errors.

The code's purpose is to update PO Line fields (POR1.ShipDate for instance). This code worked very well for quite a while, then started acting up. After adding some error checking, I found I'm getting this message back:

 [POR1.LineTotal][line: 6] , 'Field cannot be updated (ODBC -1029)'

The thing that doesn't make sense is I'm not trying to update Line 6 at all. It makes sense that it can't be updated (because it's Closed), but I'm not even touching LineTotal in my code.

I've tried to remove variables one by one--changing lines I'm attempting to update, narrowing down to less fields as I run checks, even throwing popup boxes in to verify the data looks like I expect it to, but this error still occurs. To make my matters worse, I can't reproduce that error message in GUI.

I know this is excruciatingly vague, but I'm hoping someone might have run across a similar problem before. Updating fields, closed line is the cause of an error message.

And, my code. This is the stripped down version for testing purposes. I'm working with 9.3 SAP B1

'Create Documents Object
Dim oPO AS SAPbobsCOM.Documents


'Create Record Sets for all Data
Dim oRS AS SAPbobsCOM.Recordset
Dim oUpdateRS AS SAPbobsCOM.Recordset
Dim oRSLines AS SAPbobsCOM.Recordset


'Create Strings for SQL Queries
Dim HeaderSQL AS String
Dim LineSQL AS String
Dim UpdateSQL AS String
'Dim DetailLogSQL AS String
Dim Results AS Long
Dim ErrCode AS Long
Dim ErrMsg AS String


'Assign S/O Object and Record Sets
oPO = company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders)
oRS = company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
oRSLines = company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
oUpdateRS = company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)


'Assign SQL Code to Variables as Strings
HeaderSQL = "SELECT DISTINCT DocEntry FROM LBS_B1UP_POUpdates WHERE Updated = 'N' AND DocEntry = '2210' ORDER BY DocEntry"
LineSQL = "SELECT L.DocEntry, P.ItemCode, P.LineStatus, L.LineNum, L.ShipDate, L.U_VenShpDate, L.comments, P.VisOrder, L.UpdateDate FROM LBS_B1UP_POUpdates L JOIN POR1 P ON L.DocEntry = P.DocEntry AND L.LineNum = P.LineNum WHERE LineStatus = 'O' AND L.Updated = 'N' AND L.DocEntry = "
UpdateSQL = "UPDATE LBS_B1UP_POUpdates SET Updated = 'Y', B1Update = GetDate() WHERE Updated = 'N' AND DocEntry = "
Results = 0
ErrCode = 0
ErrMsg = ""


'Get all Distinct Header Records for Updating
oRS.DoQuery(HeaderSQL)
application.MessageBox("Recordset Created!  Record Count:" & oRS.RecordCount)


'Start loop until out of data
While oRS.EOF = False


'Fill S/O Object with existing data
oPO.GetByKey (oRS.Fields.Item("DocEntry").Value)
oPO.Lines.SetCurrentLine("5")
application.MessageBox(oPO.Lines.UserFields.Fields.Item("LineTotal").Value)


oPO.Lines.SetCurrentLine("2")
application.MessageBox(oPO.Lines.UserFields.Fields.Item("U_VenShpDate").Value)
oPO.Lines.UserFields.Fields.Item("U_VenShpDate").Value = "2019-01-28"
application.MessageBox(oPO.Lines.UserFields.Fields.Item("U_VenShpDate").Value)


oPO.Lines.SetCurrentLine("5")
application.MessageBox(oPO.Lines.UserFields.Fields.Item("LineTotal").Value)
application.MessageBox(oPO.Lines.UserFields.Fields.Item("LineStatus").Value)




'Update the P/O
Results = oPO.Update()


'Check Errors and Mark Lines Updated If None


IF Results <> 0 Then
    company.GetLastError(ErrCode, ErrMsg)
    application.MessageBox(ErrMsg)
    oUpdateRS.DoQuery("UPDATE LBS_B1UP_POUPdates SET Results = '" & ErrMsg.Replace("'","''") & "'WHERE Updated = 'N' AND DocEntry = " & oRS.Fields.Item("DocEntry").Value)


ELSE


oUpdateRS.DoQuery(UpdateSQL & oRS.Fields.Item("DocEntry").Value)
application.MessageBox("It worked! Allegedly")


END IF


oRS.MoveNext
End While