Android Question [Solved] Update Query problem

Discussion in 'Android Questions' started by makis_best, Jun 18, 2019.

  1. makis_best

    makis_best Active Member Licensed User

    Hello everybody....

    I have the following Sub


    Code:
    Sub ListItems_Update(GID1 As String, TS1 As StringAs 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
     
  2. sorex

    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?
     
  3. makis_best

    makis_best Active Member Licensed User

    There are 2 different tables
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice