Error Sage.Common.ProgrammingByContract.PreconditionException: 'Contract Failure: A referential integrity exception on update has occured. This method should be overriden by the subclass.' when trying to add new financial currency on another compa

SUGGESTED

Hi all,

Lets say I have 3 companies of the same base currency, Company A, B, and C.

When I add new financial currency on Company A, I want to add to Company B and C as well. So I am using the code below, which I refer to this site https://my.sage.co.uk/Sage200SDKDocs/html/DOC0002_Example.html#ADDING

This code is executed after the form is saved. (using function _form_EndSave() )

foreach(FinancialCurrency _dirtyfc in _dirtyFinancialCurrencies)
{
	string ACTIVECONNECTIONDATA = "Sage.ObjectStore.ConnectionData.ActiveConnectionData";
	ConnectionData currentCompany = (ConnectionData)Sage.Common.Contexts.SessionContext.Context.GetData(ACTIVECONNECTIONDATA);
	ConnectionData connectionData = null;
	string company = "";
	string currency = "";
	try
	{
		Sage.Accounting.Application app = new Sage.Accounting.Application();
		foreach (Sage.Accounting.Company c in app.Companies)
		{
			company = c.Name;
			connectionData = new ConnectionData(DatabaseType.Sql, c.ConnectString);
			Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, connectionData);
			FlushPrimaryKeys(connectionData);
			// Get the base currency of other company
			FinancialCurrencies financialCurrencies = FinancialCurrenciesFactory.Factory.CreateNew();
			if (companySelected.Name == c.Name)
			{
				bool isNew = false;
				foreach (FinancialCurrency newcurrency in _newCurrencies)
				{
					if(_dirtyfc.CurrencyISOCode.Code == newcurrency.CurrencyISOCode.Code)
					{
						isNew = true;
					}
				}
				if (isNew)
				{
					StringBuilder sb = new StringBuilder("\n");
					foreach (FinancialCurrency cc in financialCurrencies)
					{
						if (_dirtyfc.CurrencyISOCode.Code == cc.CurrencyISOCode.Code)
						{
							isNew = false;
						}
					}
					if (isNew)
					{
						currency = _dirtyfc.Name;
						FinancialCurrency financialCurrency = (FinancialCurrency) FinancialCurrenciesFactory.Factory.CreateNew().AddNew();

						// Set some details
						financialCurrency.Name = _dirtyfc.Name;
						financialCurrency.Symbol = _dirtyfc.Symbol;
						financialCurrency.UseForNewAccounts = _dirtyfc.UseForNewAccounts;

						//Set the ISO code
						financialCurrency.CurrencyISOCode = _dirtyfc.CurrencyISOCode;

						// Set the amendability type
						financialCurrency.ExchangeRateAmendabilityType = _dirtyfc.ExchangeRateAmendabilityType;

						// Set the rate type used to period
						financialCurrency.ExchangeRateType = _dirtyfc.ExchangeRateType;
						if (financialCurrency.ExchangeRateType == Sage.Accounting.EnumExchangeRateType.ExchangeRateSingle)
						{
							financialCurrency.CoreCurrencyRate = _dirtyfc.CoreCurrencyRate;
							financialCurrency.EuroCurrencyRate = _dirtyfc.EuroCurrencyRate;
						}
						else if (financialCurrency.ExchangeRateType == Sage.Accounting.EnumExchangeRateType.ExchangeRatePeriod)
						{
							if (SystemSettingFactory.Factory.Fetch().UsePeriodExchangeRates)
							{
								foreach (PeriodExchangeRate per in _dirtyfc.PeriodExchangeRatesToBase)
								{
									PeriodExchangeRate periodExchangeRate = (PeriodExchangeRate) financialCurrency.PeriodExchangeRatesToBase.AddNew();

									periodExchangeRate.CurrencyType = per.CurrencyType;

									periodExchangeRate.OneUnitEquals = per.OneUnitEquals;

									periodExchangeRate.ExpiryDate = per.ExpiryDate;
								}

								foreach (PeriodExchangeRate per in _dirtyfc.PeriodExchangeRatesToEuro)
								{
									PeriodExchangeRate periodExchangeRate = (PeriodExchangeRate) financialCurrency.PeriodExchangeRatesToEuro.AddNew();

									periodExchangeRate.CurrencyType = per.CurrencyType;

									periodExchangeRate.OneUnitEquals = per.OneUnitEquals;

									periodExchangeRate.ExpiryDate = per.ExpiryDate;
								}
							}
						}
						else if (financialCurrency.ExchangeRateType == Sage.Accounting.EnumExchangeRateType.ExchangeRateBoth)
						{
							if (SystemSettingFactory.Factory.Fetch().UsePeriodExchangeRates)
							{
								foreach (PeriodExchangeRate per in _dirtyfc.PeriodExchangeRatesToBase)
								{
									PeriodExchangeRate periodExchangeRate = (PeriodExchangeRate) financialCurrency.PeriodExchangeRatesToBase.AddNew();

									periodExchangeRate.CurrencyType = per.CurrencyType;

									periodExchangeRate.OneUnitEquals = per.OneUnitEquals;

									periodExchangeRate.ExpiryDate = per.ExpiryDate;
								}

								foreach (PeriodExchangeRate per in _dirtyfc.PeriodExchangeRatesToEuro)
								{
									PeriodExchangeRate periodExchangeRate = (PeriodExchangeRate) financialCurrency.PeriodExchangeRatesToEuro.AddNew();

									periodExchangeRate.CurrencyType = per.CurrencyType;

									periodExchangeRate.OneUnitEquals = per.OneUnitEquals;

									periodExchangeRate.ExpiryDate = per.ExpiryDate;
								}
							}
							//add for single also
							financialCurrency.CoreCurrencyRate = _dirtyfc.CoreCurrencyRate;
							financialCurrency.EuroCurrencyRate = _dirtyfc.EuroCurrencyRate;
						}

						if (financialCurrency.ThisIsEuroCurrency)
						{
							// Update the financial currency and period exchange rates. The parameter passed
							// to the ConfirmChange method is to remove the period exchange rates.
							financialCurrency.ConfirmChange(false);
						}
						else
						{
							// Update financial currency and period exchange rates.
							// Note: If you just call the Update method on FinancialCurrency then the period 
							// history will not be updated.
							financialCurrency.ConfirmChange(financialCurrency.ExchangeRateType == Sage.Accounting.EnumExchangeRateType.ExchangeRateSingle); /*Error HERE!!!!*/
						}

						financialCurrency.Update();
					}
				}
				foreach(PeriodExchangeRate er in _newPeriodExchangeRates)
				{
					if (er.FinancialCurrency.CurrencyISOCode.Code == _dirtyfc.CurrencyISOCode.Code)
					{
						if(er.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyCoreCurrency)
						{
							foreach (FinancialCurrency cc in financialCurrencies)
							{
								if (_dirtyfc.CurrencyISOCode.Code == cc.CurrencyISOCode.Code)
								{
									PeriodExchangeRate per = (PeriodExchangeRate) cc.PeriodExchangeRatesToBase.AddNew();
									per.CurrencyType = er.CurrencyType;
									per.ExpiryDate = er.ExpiryDate;
									per.OneUnitEquals = er.OneUnitEquals;
									per.Update();
									cc.Update();
								}
							}
						}
						if (er.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyEuroCurrency)
						{
							foreach (FinancialCurrency cc in financialCurrencies)
							{
								if (_dirtyfc.CurrencyISOCode.Code == cc.CurrencyISOCode.Code)
								{
									PeriodExchangeRate per = (PeriodExchangeRate)cc.PeriodExchangeRatesToEuro.AddNew();
									per.CurrencyType = er.CurrencyType;
									per.ExpiryDate = er.ExpiryDate;
									per.OneUnitEquals = er.OneUnitEquals;
									per.Update();
									cc.Update();
								}
							}
						}
					}
				}

				//check for updated per
				foreach(CurrencyPERToUpdate cpu in currencyPERToUpdates)
				{
					FinancialCurrencies up_fc = FinancialCurrenciesFactory.Factory.CreateNew();
					up_fc.Query.Filters.Add(new Filter(FinancialCurrency.FIELD_SYSCURRENCYISOCODEOBJECT, cpu.currencyCode));
					up_fc.Find();
					if (up_fc.First != null)
					{
						foreach(PeriodExchangeRate per in cpu.periodExchangeRates)
						{
							if(per.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyCoreCurrency)
							{
								foreach(PeriodExchangeRate up_per in up_fc.First.PeriodExchangeRatesToBase)
								{
									if(per.ExpiryDate.Date == up_per.ExpiryDate.Date)
									{
										up_per.OneUnitEquals = per.OneUnitEquals;
										up_per.Update();
									}
								}
							}
							if (per.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyEuroCurrency)
							{
								foreach (PeriodExchangeRate up_per in up_fc.First.PeriodExchangeRatesToEuro)
								{
									if (per.ExpiryDate.Date == up_per.ExpiryDate.Date)
									{
										up_per.OneUnitEquals = per.OneUnitEquals;
										up_per.Update();
									}
								}
							}
						}
					}

				}
				//check for deleted per
				foreach (CurrencyPERToDelete cpd in currencyPERToDeletes)
				{
					FinancialCurrencies up_fc = FinancialCurrenciesFactory.Factory.CreateNew();
					up_fc.Query.Filters.Add(new Filter(FinancialCurrency.FIELD_SYSCURRENCYISOCODEOBJECT, cpd.currencyCode));
					up_fc.Find();
					if (up_fc.First != null)
					{
						foreach (PeriodExchangeRate per in cpd.periodExchangeRates)
						{
							if (per.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyCoreCurrency)
							{
								foreach (PeriodExchangeRate de_per in up_fc.First.PeriodExchangeRatesToBase)
								{
									if (per.ExpiryDate.Date == de_per.ExpiryDate.Date)
									{
										de_per.Delete();
									}
								}
							}
							if (per.CurrencyType == Sage.Accounting.EnumExchangeCurrencyType.ExchangeCurrencyEuroCurrency)
							{
								foreach (PeriodExchangeRate de_per in up_fc.First.PeriodExchangeRatesToEuro)
								{
									if (per.ExpiryDate.Date == de_per.ExpiryDate.Date)
									{
										de_per.Delete();
									}
								}
							}
						}
						up_fc.Update();
					}
				}
				
				if (isNew)
				{
					String codes = "EUR', 'GBP', 'USD";
					CurrencyISOCodes iSOCodes = CurrencyISOCodesFactory.Factory.CreateNew();
					iSOCodes.Query.FilterText = CurrencyISOCode.FIELD_CODE + " not in ('" + codes + "')";
					iSOCodes.Find();
					List<long> isocodeids = new List<long>();
					foreach (CurrencyISOCode isocode in iSOCodes)
					{
						isocodeids.Add(isocode.SYSCurrencyISOCode);
					}
					string isocodeids_s = string.Join(",", isocodeids);
					bool error = false;
					StringBuilder sb = new StringBuilder();
					sb.Append("Cannot save for the following Currency:\n");
					FinancialCurrencies FinancialCurrencies = FinancialCurrenciesFactory.Factory.CreateNew();
					FinancialCurrencies = FinancialCurrenciesFactory.Factory.CreateNew();
					FinancialCurrencies.Query.FilterText = FinancialCurrency.FIELD_SYSCURRENCYISOCODEOBJECT + "ID in (" + isocodeids_s + ")";
					FinancialCurrencies.Query.Sorts.Add(new Sage.ObjectStore.Sort(FinancialCurrency.FIELD_SYSCURRENCYISOCODEOBJECT, true));
					FinancialCurrencies.Find();
					int increment = 10;
					foreach (FinancialCurrency curr in FinancialCurrencies)
					{
						curr.Fields.FindItem("SortIndex").Value = increment;
						curr.Update();
						increment++;
					}
				}
					//check if core financialCurrency is updated
			}
		}
	}
	catch (Exception ex)
	{
		ImportRemark ir = new ImportRemark(company, ex.Message + ex.ToString());
		importRemarks.Add(ir);
		//MessageBox.Show(company + " (" + currency + "): " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
		Logger.WriteLog(company + " (" + currency + "): "+ ex.ToString());
		Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany);
		FlushPrimaryKeys(currentCompany);
	}
	finally
	{
		Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany);
		FlushPrimaryKeys(currentCompany);
	}
	Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, currentCompany);
	FlushPrimaryKeys(currentCompany);

}

However, I am getting this error on line 131:

Sage.Common.ProgrammingByContract.PreconditionException: 'Contract Failure: A referential integrity exception on update has occured.  This method should be overriden by the subclass.'

Which I don't understand which column is giving this error.

Anyone can help me? Thank you

Parents
  • 0

    I can't replicate this.

    Probably your best bet is to set up a new Extended Events session to capture error events. If you capture the t-sql text then it'll probably give you some clues.

     Try something like this:

    CREATE EVENT SESSION [Referential Integrity Errors] 
    ON SERVER 
    ADD EVENT sqlserver.error_reported(
        ACTION(sqlserver.sql_text,sqlserver.tsql_stack)
        WHERE ([severity]>(10)))
    ADD TARGET package0.event_file(SET filename=N'Referential Integrity Errors')
    WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
    )
    GO

Reply
  • 0

    I can't replicate this.

    Probably your best bet is to set up a new Extended Events session to capture error events. If you capture the t-sql text then it'll probably give you some clues.

     Try something like this:

    CREATE EVENT SESSION [Referential Integrity Errors] 
    ON SERVER 
    ADD EVENT sqlserver.error_reported(
        ACTION(sqlserver.sql_text,sqlserver.tsql_stack)
        WHERE ([severity]>(10)))
    ADD TARGET package0.event_file(SET filename=N'Referential Integrity Errors')
    WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
    )
    GO

Children
  • 0 in reply to Chris Burke

    Thank you. I found out the problem is cause by line 50. I have created new ISO Code such as Bitcoin and Ethereum, which has different unique id on each company. on line 50 I set the financialCurrency.CurrencyISOCode = _dirtyfc.CurrencyISOCode directly.

    Where financialCurrency.CurrencyISOCode is for Company B and _dirtyfc.CurrencyISOCode is from Company A.

    Then I change to match by Code and then set the CurrencyISOCode financialCurrency.CurrencyISOCode , and that solve the problem.

    //FIND THE CURRENCY IS CODE THAT IS SAME AS DIRTYFC
    CurrencyISOCodes isocodes = CurrencyISOCodesFactory.Factory.CreateNew();
    isocodes.Query.Filters.Add(new Filter(CurrencyISOCode.FIELD_CODE, _dirtyfc.CurrencyISOCode.Code));
    if (isocodes.First == null)
    {
        throw new Exception(_dirtyfc.CurrencyISOCode.Code + " code not found in " + c.Name);
    }
    FinancialCurrency financialCurrency = (FinancialCurrency) FinancialCurrenciesFactory.Factory.CreateNew().AddNew();
    
    //Set the ISO code
    financialCurrency.CurrencyISOCode = isocodes.First;