CartHelper AddEntry INSERT statement conflicted with the FOREIGN

CartHelper AddEntry INSERT statement conflicted with the FOREIGN KEY constraint FK_OrderForm_OrderGroup in table dbo.OrderGroup column OrderGroupId

Gorm Eirik Svendsen

Vote:

Hello

In Episerver Commerce 10.3.0 when executing the following code:

var cartHelper = new CartHelper(Cart.DefaultName);
var entry = CatalogContext.Current.GetCatalogEntry(code);
cartHelper.AddEntry(entry, quantity, false);

we get an exception:

		Message	"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_OrderForm_OrderGroup\". The conflict occurred in database \"someEpiCommerceDb\", table \"dbo.OrderGroup\", column 'OrderGroupId'.
The statement has been terminated."	string
		Number	547	int
		Procedure	"ecf_OrderForm_Insert"	string
		Server	"tcp:someazuresqlservername.database.windows.net,1433"	string
		Source	".Net SqlClient Data Provider"	string
		StackTrace	"   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
   at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec(DataCommand command)
   at Mediachase.Commerce.Storage.MetaStorageBase.AcceptChanges(MetaDataContext context, Boolean saveSystem)
   at Mediachase.Commerce.Orders.OrderForm.AcceptChanges()
   at Mediachase.Commerce.Storage.MetaStorageCollectionBase`1.AcceptChanges()
   at Mediachase.Commerce.Orders.OrderGroup.AcceptChanges()
   at Mediachase.Commerce.Orders.Cart.AcceptChanges()
   at Mediachase.Commerce.Website.Helpers.CartHelper.AddEntry(Entry[] entries, Decimal[] quantity, Boolean fixedQuantity, String warehouseCode, CartHelper[] helpersToRemove)
   at Mediachase.Commerce.Website.Helpers.CartHelper.AddEntry(Entry entry, Decimal quantity, Boolean fixedQuantity, String warehouseCode, CartHelper[] helpersToRemove)
   at Mediachase.Commerce.Website.Helpers.CartHelper.AddEntry(Entry entry, Decimal quantity, Boolean fixedQuantity, CartHelper[] helpersToRemove)
   at SomeProjectName.Core.Services.CartService.AddToCart(String code, Decimal quantity) in C:\\source\\SomeProjectName\\src\\SomeProjectName.Core\\Services\\CartService.cs:line 67
   at SomeProjectName.Cms.Controllers.WishListPageController.Index(WishListPage currentPage, String code, Nullable`1 quantity) in C:\\source\\SomeProjectName\\src\\SomeProjectName.Cms\\Controllers\\WishListPageController.cs:line 43
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.b__3d()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f()"	string

There has not been added items to any carts and tables OrderGroup, OrderForm OrderGroupNote, LineItems or any other table having todo with orders or line items are empty except OrderNoteType and OrderStatus.

The entries/variants being added have valid price and inventory set.

The OrderGroupId and OrderFormId in cartHelper.OrderForm keep getting incremented every time we call AddEntry. The previouse line items that has been attempted to add earlier can be found in cartHelper.LineItems, but nothing is showing up in the db.

Is there some way to resolve this?

#176729

Edited, Mar 24, 2017 10:01

Quan Mai

Vote:

As this can't be reproduced in our standard environment, I suspect this your site-related. I'd suggest you to contact our developer support service so we can reproduce the issue and take further actions.

Regards,

#176777

Mar 27, 2017 10:04

Henning Klokkeråsen

Vote:

We solved this by modifying the stored procedure mdpsp_avto_LineItemEx_Update - by removing parameters @f1, @f2, etc, which seem to be orphaned after we removed some Meta Fields associations from the LineItemEx Meta Class in the Commerce Manager UI?

Are the stored procedures not updated when Meta Class / Field associations are updated from the Commerce Manager UI?

This might be something originating from the Commerce Starter Kit if it can not be reproduced in a clean Commerce site.

Steps to reproduce:

Extend LineItemEx with custom meta fields
In code, when adding something to cart (using CartHelper), populated the meta fields using something something similar to AddCustomProperties (see below)
Remove meta field associations
Add to cart (using CartHelper), now without setting the meta fields
Fails with exception where the mentioned stored procedure expects a parameter which is not supplied

The LineItem parameter is a custom class

Snippet

private void AddCustomProperties(LineItem lineItem, Cart cart)
        {
            var item = cart.OrderForms[0].LineItems.FindItemByCatalogEntryId(lineItem.Code);
 
            // Make sure we have all available data on the item before
            // we proceed
            lineItem.UpdateData(item);
 
            //TODO: Let specific model implementation populate these fields, we need to know too much about the model here
            item["SomeCustomMetaField"] = lineItem.SomeProperty;

            cart.AcceptChanges();
        }

#176810

Mar 28, 2017 11:53

Quan Mai

Vote:

Hi,

It should (update the stored procedures when you change the metaclasses). I would suggest you to check if [mdpsp_sys_CreateMetaClassProcedure] and [mdpsp_sys_CreateMetaClassProcedureAll] are correct (match with the same vanilla version), then run [mdpsp_sys_CreateMetaClassProcedureAll] to make sure all of metaclass stored procedures are update-to-date with their metaclasses

#176812

Mar 28, 2017 12:02

Henning Klokkeråsen

Vote:

I have installed a vanilla Commerce site, and comparing to our site, the procedures are identical (using WinMerge) I ran  [mdpsp_sys_CreateMetaClassProcedureAll], which gave return value = 0 Hope that solves it!

#176823

Mar 28, 2017 14:13