November Happy Hour will be moved to Thursday December 5th.

SQL Error deleting Carts

Vote:
 

HI,

Since upgrading to the latest version of EPiServer and Commerce and FIND there is an issue when we try to delete a Basket/Cart. The error I get is:

Failed to process item(s). Error: The DELETE statement conflicted with the REFERENCE constraint "FK_OrderFormPaymentEx_SagePayPayment_Localization_OrderFormPayment". The conflict occurred in database "dbrcogshop", table "dbo.OrderFormPaymentEx_SagePayPayment_Localization", column 'ObjectId'. The DELETE statement conflicted with the REFERENCE constraint "FK_OrderFormPayment_OrderForm". The conflict occurred in database "dbrcogshop", table "dbo.OrderFormPayment", column 'OrderFormId'. The DELETE statement conflicted with the REFERENCE constraint "FK_OrderForm_OrderGroup". The conflict occurred in database "dbrcogshop", table "dbo.OrderForm", column 'OrderGroupId'. The statement has been terminated. The statement has been terminated. The statement has been terminated.

It seems like it puts duplicate entries into the OrderFormEx_Localization table - for example:

Id ObjectId ModifierId Modified Language RMANumber
1506 844 NULL 2014-08-19 16:30:02.287 en-gb NULL
1507 844 NULL 2014-08-19 16:31:59.380 en NULL

And adds duplicate entries into the LineItemEX_Localization table:

2179 1222 NULL 2014-08-19 16:30:02.287 en-gb
2180 1222 NULL 2014-08-19 16:31:59.380 en

If I manually go into the database and remove the orders from these tables it works until the next user adds something into their cart.

Is this an issue in Episerver or something that we havent set up correctly in the CMS?

Jon

#89572
Aug 20, 2014 10:36
Vote:
 

Hi,

This is a known problem to us and we're working on a fix. A fix will be delivered in upcoming releases.

Sorry for this inconvenient and thank you for your understanding.

In the mean while, you can try a workaround by running this script against your Commerce database:

ALTER TABLE dbo.OrderFormPaymentEx_SagePayPayment_Localization
DROP CONSTRAINT FK_OrderFormPaymentEx_SagePayPayment_Localization_OrderFormPayment
WITH (ONLINE = ON);
GO

ALTER TABLE [dbo].[OrderFormPaymentEx_SagePayPayment_Localization] WITH CHECK ADD CONSTRAINT [FK_OrderFormPaymentEx_SagePayPayment_Localization_OrderFormPayment] FOREIGN KEY([ObjectId])
REFERENCES [dbo].[OrderFormPayment] ([PaymentId])
ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[

OrderFormPaymentEx_SagePayPayment_Localization

] CHECK CONSTRAINT [

FK_OrderFormPaymentEx_SagePayPayment_Localization_OrderFormPayment

]

GO

Regards.

/Q

#89576
Edited, Aug 20, 2014 11:52
Vote:
 

This is great. Many thanks

#89580
Aug 20, 2014 12:44
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.