Android Question [Solved] Update Query problem

makis_best

Active Member
Licensed User
Hello everybody....

I have the following Sub


B4X:
Sub ListItems_Update(GID1 As String, TS1 As String) As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    'Log("2 - It is :" & Success)
    If Success Then
        Try
            Dim Qry As String
            Qry = $"SELECT Gid, Code, Description,
            AlternativeDescription, DetailDescription, Inactive, fSITaxCode,
            InternationalCode, AlternativeCode, BarCode,
            AssemblyType, ItemClass, ItemType, fMainSupplierGID, fManufacturerPersonGID,
            Manufacturer, fCountryOriginCode, fTariffCode, Price , RetailPrice ,
            MarkupOnPrice , MarkupOnRetailPrice , PriceIncludedVAT, RetailPriceIncludedVAT,
            fMainMUGID, fAltMUGID, fVolumeMUGID, fWeightMUGID, fVATCategoryCode,
            Discount , MaxDiscount , MinProfitMargin , MinSalesOrderQty , UsualPurchaseOrderQty ,
            fCommissionLevelCode, fItemFamilyCode, fItemGroupCode, fItemCategoryCode, fItemSubcategoryCode,
            fTaxesGroupGID, fChargesGroupGID, fBaseBOMGID, fDiscountGroupGID, StandardCost ,
            ValuationMethod, fItemControlProfileGID, IncludedTaxReports, SerialNumberMgmt,
            LotMgmt, ColorMgmt, SizeMgmt, StockDim1Mgmt, StockDim2Mgmt, fColorSetGID,
            fSizeSetGID, fStockDim1SetGID, fStockDim2SetGID, Comment, GLAccountCode, fCatalogueItemGID,
            fTableField1Code, fTableField2Code, fTableField3Code, fTableField4Code, fTableField5Code,
            fTableField6Code, fTableField7Code, fTableField8Code, fTableField9Code, fTableField10Code,
            StringField1, StringField2, StringField3, StringField4, StringField5, StringField6,
            StringField7, StringField8, StringField9, StringField10, DateField1, DateField2,
            DateField3, DateField4, DateField5, NumericField1 , NumericField2 , NumericField3 ,
            NumericField4 , NumericField5 , NumericField6 , NumericField7 , NumericField8 ,
            NumericField9 , NumericField10 , Flag1, Flag2, Flag3, Flag4,
            Flag5, Flag6, Flag7, Flag8, Flag9, Flag10, Flag11,
            Flag12, fBusinessUnitCode, fBusinessActivityCode, fDimension1Code, fDimension2Code,
            ESDCreated, ESUCreated, ESDModified, ESUModified, fCompanyCode, fGLSegCode,
            fItemPricingCategoryCode, fIntrastatCode, fCostElementTypeGID, BOMLevel, AssetType,
            MainCode, fDepreciationProfileGID, fAlternativeDepreciationProfileGID, fInformativeDepreciationProfileGID,
            OutOfUsage, fMainAssetGID, fAssetGLSegCode, fDepreciationGLSegCode, fDepreciatedGLSegCode,
            fBudgetItemGroupCode, fConveyanceCode, DateField6, DateField7, DateField8, DateField9,
            DateField10, Price1 , Price2 , Price3 , Price1IncludedVAT, Price2IncludedVAT,
            Price3IncludedVAT, StorageLocationCodes, SubType, fSeasonCode, VATCalculationValue,
            CostIncludedVAT, fItemNetProfitCodesGID, fPropertySetCode, fPropertySetGID, WEB,
            fDeductionGroupGID, fBonusGroupGID, ValuationMethodAnalysis, fWarrantyTermGID,
            ValuationPerPeriod, DocumentMessage, TS, ServiceMUType, fAllocationProfileGID,
            fItemAllocationProfileGID, Mobile, SelectInMobileOrder, ExpenseType, ServiceType,
            PerCentOfTaxExclusion , fTaxDifferencesAccountGID, ElementExportCategory, MarkupOnPrice1 ,
            MarkupOnPrice2 , MarkupOnPrice3 , OpeningPerFiscalYear, fItemControlPolicyGID,
            LotCharacteristicsMgmt, fValidStockDimensionsProfileGID, fBCSNValidationTypeCode
            FROM Item where ItemType in (0,12) And ItemClass = 1 AND Gid = '${GID1}' AND CONVERT(VARCHAR, TS) <> '${TS1}'"$
            Dim sf As Object = RemoteSQL.ExecQueryAsync("RemoteSQL", Qry, Null)
            Wait For (sf) RemoteSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
            'Log("3 - It is :" & Success)
            If Success Then
                'Dim i1 As Int = 1
                Do While Crsr.NextRow
                    Starter.LocalSQL.BeginTransaction
                    Try
                        Log("TS1:" & TS1 & " - " & Crsr.GetString("TS") & " -/- " & "GID1:" & GID1 & " - " & Crsr.GetString("GID"))
                        If GID1 = Crsr.GetString("GID") And TS1 <> Crsr.GetString("TS") Then
                            Starter.LocalSQL.ExecNonQuery2($"Update LOCAL_Item Set Code = ?, Description = ?, AlternativeDescription = ?, DetailDescription = ?, Inactive = ?, fSITaxCode = ?, InternationalCode = ?, AlternativeCode = ?, BarCode = ?,
                            AssemblyType = ?, ItemClass = ?, ItemType = ?, fMainSupplierGID = ?, fManufacturerPersonGID = ?, Manufacturer = ?, fCountryOriginCode = ?, fTariffCode = ?, Price = ?, RetailPrice = ?, MarkupOnPrice = ?, MarkupOnRetailPrice = ?,
                            PriceIncludedVAT = ?, RetailPriceIncludedVAT = ?, fMainMUGID = ?,fAltMUGID = ?, fVolumeMUGID = ?, fWeightMUGID = ?, fVATCategoryCode = ?, Discount = ?, MaxDiscount = ?, MinProfitMargin = ?, MinSalesOrderQty = ?, UsualPurchaseOrderQty = ?,
                            fCommissionLevelCode = ?, fItemFamilyCode = ?, fItemGroupCode = ?, fItemCategoryCode = ?, fItemSubcategoryCode = ?, fTaxesGroupGID = ?,fChargesGroupGID = ?,fBaseBOMGID = ?, fDiscountGroupGID = ?,StandardCost = ?,
                            ValuationMethod = ?, fItemControlProfileGID = ?, IncludedTaxReports = ?, SerialNumberMgmt = ?, LotMgmt = ?, ColorMgmt = ?, SizeMgmt = ?, StockDim1Mgmt = ?,  StockDim2Mgmt = ?, fColorSetGID = ?, fSizeSetGID = ?,
                            fStockDim1SetGID = ?, fStockDim2SetGID = ?, Comment = ?, GLAccountCode = ?, fCatalogueItemGID = ?, fTableField1Code = ?, fTableField2Code = ?, fTableField3Code = ?, fTableField4Code = ?, fTableField5Code = ?,
                            fTableField6Code = ?, fTableField7Code = ?, fTableField8Code = ?, fTableField9Code = ?, fTableField10Code = ?, StringField1 = ?, StringField2 = ?, StringField3 = ?, StringField4 = ?, StringField5 = ?,
                            StringField6 = ?, StringField7 = ?, StringField8 = ?, StringField9 = ?, StringField10 = ?, DateField1 = ?, DateField2 = ?, DateField3 = ?, DateField4 = ?, DateField5 = ?, NumericField1 = ?, NumericField2 = ?,
                            NumericField3 = ?, NumericField4 = ?, NumericField5 = ?, NumericField6 = ?, NumericField7 = ?, NumericField8 = ?, NumericField9 = ?, NumericField10 = ?, Flag1 = ?, Flag2 = ?,  Flag3 = ?,  Flag4 = ?, Flag5 = ?, Flag6 = ?,  Flag7 = ?,  Flag8 = ?,
                            Flag9 = ?, Flag10 = ?,  Flag11 = ?,  Flag12 = ?, fBusinessUnitCode = ?, fBusinessActivityCode = ?, fDimension1Code = ?, fDimension2Code = ?, ESDCreated = ?, ESUCreated = ?, ESDModified = ?, ESUModified = ?,
                            fCompanyCode = ?, fGLSegCode = ?, fItemPricingCategoryCode = ?, fIntrastatCode = ?, fCostElementTypeGID = ?, BOMLevel = ?, AssetType = ?, MainCode = ?, fDepreciationProfileGID = ?,
                            fAlternativeDepreciationProfileGID = ?,fInformativeDepreciationProfileGID = ?,  OutOfUsage = ?, fMainAssetGID = ?, fAssetGLSegCode = ?, fDepreciationGLSegCode = ?, fDepreciatedGLSegCode = ?,
                            fBudgetItemGroupCode = ?, fConveyanceCode = ?, DateField6 = ?,DateField7 = ?, DateField8 = ?, DateField9 = ?, DateField10 = ?, Price1 = ?, Price2 = ?, Price3 = ?, Price1IncludedVAT = ?, Price2IncludedVAT = ?, Price3IncludedVAT = ?,
                            StorageLocationCodes = ?, SubType = ?, fSeasonCode = ?, VATCalculationValue = ?, CostIncludedVAT = ?, fItemNetProfitCodesGID = ?, fPropertySetCode = ?, fPropertySetGID = ?, WEB = ?, fDeductionGroupGID = ?,fBonusGroupGID = ?,
                            ValuationMethodAnalysis = ?, fWarrantyTermGID = ?, ValuationPerPeriod = ?, DocumentMessage = ?, TS = ?, ServiceMUType = ?, fAllocationProfileGID = ?, fItemAllocationProfileGID = ?,
                            Mobile = ?, SelectInMobileOrder = ?, ExpenseType = ?, ServiceType = ?, PerCentOfTaxExclusion = ?, fTaxDifferencesAccountGID = ?, ElementExportCategory = ?, MarkupOnPrice1 = ?,
                            MarkupOnPrice2 = ?,MarkupOnPrice3 = ?, OpeningPerFiscalYear = ?, fItemControlPolicyGID = ?, LotCharacteristicsMgmt = ?, fValidStockDimensionsProfileGID = ?, fBCSNValidationTypeCode = ?
                            WHERE GID = ? AND TS <> ? "$, Array As Object(Crsr.GetString("Code"), Crsr.GetString("Description"), _
                            Crsr.GetString("AlternativeDescription"), Crsr.GetString("DetailDescription"), Crsr.GetInt("Inactive"), Crsr.GetString("fSITaxCode"), Crsr.GetString("InternationalCode"), _
                            Crsr.GetString("AlternativeCode"), Crsr.GetString("BarCode"), _
                            Crsr.GetInt("AssemblyType"), Crsr.GetInt("ItemClass"), Crsr.GetInt("ItemType"), Crsr.GetString("fMainSupplierGID"), _
                            Crsr.GetString("fManufacturerPersonGID"), Crsr.GetString("Manufacturer"), Crsr.GetString("fCountryOriginCode"), Crsr.GetString("fTariffCode"), _
                            Crsr.GetDouble("Price"), Crsr.GetDouble("RetailPrice"), Crsr.GetDouble("MarkupOnPrice"), Crsr.GetDouble("MarkupOnRetailPrice"), _
                            Crsr.GetInt("PriceIncludedVAT") , Crsr.GetInt("RetailPriceIncludedVAT"), Crsr.GetString("fMainMUGID"), Crsr.GetString("fAltMUGID"), _
                            Crsr.GetString("fVolumeMUGID") , Crsr.GetString("fWeightMUGID"), Crsr.GetInt("fVATCategoryCode"), Crsr.GetDouble("Discount"), _
                            Crsr.GetDouble("MaxDiscount") , Crsr.GetDouble("MinProfitMargin"), Crsr.GetDouble("MinSalesOrderQty"), Crsr.GetDouble("UsualPurchaseOrderQty"), _
                            Crsr.GetString("fCommissionLevelCode") , Crsr.GetString("fItemFamilyCode"), Crsr.GetString("fItemGroupCode"), Crsr.GetString("fItemCategoryCode"), _
                            Crsr.GetString("fItemSubcategoryCode") , Crsr.GetString("fTaxesGroupGID"), Crsr.GetString("fChargesGroupGID"), Crsr.GetString("fBaseBOMGID"), _
                            Crsr.GetString("fDiscountGroupGID") , Crsr.GetDouble("StandardCost"), Crsr.GetInt("ValuationMethod"), Crsr.GetString("fItemControlProfileGID"), _
                            Crsr.GetInt("IncludedTaxReports") , Crsr.GetInt("SerialNumberMgmt"), Crsr.GetInt("LotMgmt"), Crsr.GetInt("ColorMgmt"), _
                            Crsr.GetInt("SizeMgmt") , Crsr.GetInt("StockDim1Mgmt"), Crsr.GetInt("StockDim2Mgmt"), Crsr.GetString("fColorSetGID"), _
                            Crsr.GetString("fSizeSetGID") , Crsr.GetString("fStockDim1SetGID"), Crsr.GetString("fStockDim2SetGID"), Crsr.GetString("Comment"), _
                            Crsr.GetString("GLAccountCode"), Crsr.GetString("fCatalogueItemGID"), Crsr.GetString("fTableField1Code"), Crsr.GetString("fTableField2Code"), Crsr.GetString("fTableField3Code"), _
                            Crsr.GetString("fTableField4Code") , Crsr.GetString("fTableField5Code"), Crsr.GetString("fTableField6Code"), Crsr.GetString("fTableField7Code"), _
                            Crsr.GetString("fTableField8Code") , Crsr.GetString("fTableField9Code"), Crsr.GetString("fTableField10Code"), Crsr.GetString("StringField1"), _
                            Crsr.GetString("StringField2") , Crsr.GetString("StringField3"), Crsr.GetString("StringField4"), Crsr.GetString("StringField5"), _
                            Crsr.GetString("StringField6") , Crsr.GetString("StringField7"), Crsr.GetString("StringField8"), Crsr.GetString("StringField9"), _
                            Crsr.GetString("StringField10") , Crsr.GetString("DateField1"), Crsr.GetString("DateField2"), Crsr.GetString("DateField3"), _
                            Crsr.GetString("DateField4") , Crsr.GetString("DateField5"), Crsr.GetDouble("NumericField1"), Crsr.GetDouble("NumericField2"), _
                            Crsr.GetDouble("NumericField3") , Crsr.GetDouble("NumericField4"), Crsr.GetDouble("NumericField5"), Crsr.GetDouble("NumericField6"), _
                            Crsr.GetDouble("NumericField7") , Crsr.GetDouble("NumericField8"), Crsr.GetDouble("NumericField9"), Crsr.GetDouble("NumericField10"), _
                            Crsr.GetInt("Flag1"), Crsr.GetInt("Flag2"), Crsr.GetInt("Flag3"), Crsr.GetInt("Flag4"), _
                            Crsr.GetInt("Flag5"), Crsr.GetInt("Flag6"), Crsr.GetInt("Flag7"), Crsr.GetInt("Flag8"), _
                            Crsr.GetInt("Flag9"), Crsr.GetInt("Flag10"), Crsr.GetInt("Flag11"), Crsr.GetInt("Flag12"), _
                            Crsr.GetString("fBusinessUnitCode") , Crsr.GetString("fBusinessActivityCode"), Crsr.GetString("fDimension1Code"), Crsr.GetString("fDimension2Code"), _
                            Crsr.GetString("ESDCreated"), Crsr.GetString("ESUCreated"), Crsr.GetString("ESDModified"), Crsr.GetString("ESUModified"), _
                            Crsr.GetString("fCompanyCode"), Crsr.GetString("fGLSegCode"), Crsr.GetString("fItemPricingCategoryCode"), Crsr.GetString("fIntrastatCode"), _
                            Crsr.GetString("fCostElementTypeGID") , Crsr.GetInt("BOMLevel"), Crsr.GetInt("AssetType"), Crsr.GetInt("MainCode"), _
                            Crsr.GetString("fDepreciationProfileGID") , Crsr.GetString("fAlternativeDepreciationProfileGID"), Crsr.GetString("fInformativeDepreciationProfileGID"), Crsr.GetInt("OutOfUsage"), _
                            Crsr.GetString("fMainAssetGID"), Crsr.GetString("fAssetGLSegCode"), Crsr.GetString("fDepreciationGLSegCode"), Crsr.GetString("fDepreciatedGLSegCode"), _
                            Crsr.GetString("fBudgetItemGroupCode") , Crsr.GetString("fConveyanceCode"), Crsr.GetString("DateField6"), Crsr.GetString("DateField7"), _
                            Crsr.GetString("DateField8"), Crsr.GetString("DateField9"), Crsr.GetString("DateField10"), Crsr.GetDouble("Price1"), _
                            Crsr.GetDouble("Price2"), Crsr.GetDouble("Price3"), Crsr.GetInt("Price1IncludedVAT"), Crsr.GetInt("Price2IncludedVAT"), _
                            Crsr.GetInt("Price3IncludedVAT"), Crsr.GetString("StorageLocationCodes"), Crsr.GetInt("SubType"), Crsr.GetString("fSeasonCode"), _
                            Crsr.GetInt("VATCalculationValue"), Crsr.GetInt("CostIncludedVAT"), Crsr.GetString("fItemNetProfitCodesGID"), Crsr.GetString("fPropertySetCode"), _
                            Crsr.GetString("fPropertySetGID"), Crsr.GetInt("WEB"), Crsr.GetString("fDeductionGroupGID"), Crsr.GetString("fBonusGroupGID"), _
                            Crsr.GetInt("ValuationMethodAnalysis") , Crsr.GetString("fWarrantyTermGID"), Crsr.GetInt("ValuationPerPeriod"), Crsr.GetString("DocumentMessage"), _
                            Crsr.GetString("TS"), Crsr.GetInt("ServiceMUType"), Crsr.GetString("fAllocationProfileGID"), Crsr.GetString("fItemAllocationProfileGID"), _
                            Crsr.GetInt("Mobile"), Crsr.GetInt("SelectInMobileOrder"), Crsr.GetInt("ExpenseType"), Crsr.GetInt("ServiceType"), _
                            Crsr.GetDouble("PerCentOfTaxExclusion"), Crsr.GetString("fTaxDifferencesAccountGID"), Crsr.GetInt("ElementExportCategory"), Crsr.GetDouble("MarkupOnPrice1"), _
                            Crsr.GetDouble("MarkupOnPrice2"), Crsr.GetDouble("MarkupOnPrice3"), Crsr.GetInt("OpeningPerFiscalYear"), Crsr.GetString("fItemControlPolicyGID"), _
                            Crsr.GetInt("LotCharacteristicsMgmt"), Crsr.GetString("fValidStockDimensionsProfileGID"), Crsr.GetString("fBCSNValidationTypeCode"), GID1, TS1))
                            'Log(i1 & " - " & Crsr.GetString("Code") & " - " & Crsr.GetString("Description") & " - " & Crsr.GetString("TS") & " - " & GID1 & " - " & TS1)
                            'i1 = i1 + 1
                        End If
                        Starter.LocalSQL.TransactionSuccessful
                    Catch
                        Log(LastException.Message)
                    End Try
                    Starter.LocalSQL.EndTransaction
                Loop
                Crsr.Close
            End If
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub
As you can see sub do a very big update. 178 fields must be update...
But none of them does...
Not even one.
I get no errors no strange message and all Get return values...
Some of them are Null fields

Can anyone tell me why the update doesn't work?

Thank you
 

sorex

Expert
Licensed User
you fetch a record from the item table but then you update a record in the local_item table?

are there 2 different tables or just 1?
 
Top