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




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	",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?

Edited, Mar 24, 2017 10:01

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.



Mar 27, 2017 10:04

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


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
            //TODO: Let specific model implementation populate these fields, we need to know too much about the model here
            item["SomeCustomMetaField"= lineItem.SomeProperty;


Mar 28, 2017 11:53


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

Mar 28, 2017 12:02
<p>I have installed a vanilla Commerce site, and comparing to our site, the procedures are identical (using WinMerge)</p> <p>I ran&nbsp;<span>&nbsp;[mdpsp_sys_CreateMetaClassProcedureAll], which gave return value = 0</span></p> <p><span>Hope that solves it!</span></p>
Mar 28, 2017 14:13
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.