The INSERT statement conflicted with the FOREIGN KEY constraint "FK_COM_OrderItem_OrderItemOrderID_

Brandon Prudent asked on May 30, 2018 23:11

Having some trouble creating an order from our cart. Here's the relevant code:

        if (Cart.Validate().CheckFailed)
        {
            Cart.Evaluate();
            Errors.Add("Cart invalid");
            return false;
        }

        // set kentico customer info
        Cart.Customer = new Customer
        {
            Email = Info.Email,
            FirstName = Info.FirstName,
            LastName = Info.LastName,
            Company = Info.Company,
            PhoneNumber = Info.Phone
        };

        Cart.BillingAddress = new CustomerAddress
        {
            City = Info.City,
            CountryID = Info.Country,
            StateID = Info.State,
            Line1 = Info.Address,
            Line2 = Info.Address2,
            PostalCode = Info.Zip,
            PersonalName = Info.DisplayName
        };

        // save to kentico
        new ShoppingService().CreateOrder(Cart).SetPaymentResult(new PaymentResultInfo
        {
            PaymentDate = DateTime.UtcNow,
            PaymentIsCompleted = true,
            PaymentMethodName = Info.IsPaymentPO ? "PO" : "Credit Card",
            PaymentTransactionID = TransactionId,
        });

This is resulting in the following stack trace:

[SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_COM_OrderItem_OrderItemOrderID_COM_Order". The conflict occurred in database "Kentico", table "dbo.COM_Order", column 'OrderID'.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3302300
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +736
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4061
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +90
   System.Data.SqlClient.SqlDataReader.get_MetaData() +99
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +604
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +3303
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +667
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +83
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +301
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +201
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +178
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +122
   CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Database\Abstract\AbstractDataConnection.cs:421

[Exception: 
[DataConnection.HandleError]: 

Query: 
INSERT INTO COM_OrderItem ([OrderItemOrderID], [OrderItemSKUID], [OrderItemSKUName], [OrderItemUnitPrice], [OrderItemUnitCount], [OrderItemCustomData], [OrderItemGuid], [OrderItemParentGuid], [OrderItemLastModified], [OrderItemValidTo], [OrderItemBundleGUID], [OrderItemTotalPriceInMainCurrency], [OrderItemSendNotification], [OrderItemText], [OrderItemProductDiscounts], [OrderItemDiscountSummary], [OrderItemTotalPrice]) 
VALUES (@OrderItemOrderID, @OrderItemSKUID, @OrderItemSKUName, @OrderItemUnitPrice, @OrderItemUnitCount, @OrderItemCustomData, @OrderItemGuid, @OrderItemParentGuid, @OrderItemLastModified, @OrderItemValidTo, @OrderItemBundleGUID, @OrderItemTotalPriceInMainCurrency, @OrderItemSendNotification, @OrderItemText, @OrderItemProductDiscounts, @OrderItemDiscountSummary, @OrderItemTotalPrice);

SELECT SCOPE_IDENTITY() AS [ID]

Caused exception: 
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_COM_OrderItem_OrderItemOrderID_COM_Order". The conflict occurred in database "Kentico", table "dbo.COM_Order", column 'OrderID'.
The statement has been terminated.
]
   CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex) in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Database\Abstract\AbstractDataConnection.cs:1136
   CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Database\Abstract\AbstractDataConnection.cs:442
   CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query) in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Database\Connections\GeneralConnection.cs:368
   CMS.DataEngine.GeneralConnection.RunQueryWithRetry(QueryParameters query, Int32 retryCount) in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Database\Connections\GeneralConnection.cs:297
   CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords) in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Database\Connections\GeneralConnection.cs:314
   CMS.DataEngine.DataQueryBase`1.GetDataFromDBInternal() in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Query\Data\DataQueryBase.cs:1749
   CMS.DataEngine.DataQueryBase`1.GetDataFromDB() in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Query\Data\DataQueryBase.cs:1740
   CMS.DataEngine.DataQueryBase`1.GetData() in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Query\Data\DataQueryBase.cs:1678
   CMS.DataEngine.SimpleDataClass.Insert(Boolean initId) in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Data\DataClass\SimpleDataClass.cs:805
   CMS.DataEngine.AbstractInfoBase`1.InsertData() in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Data\Info\Abstract\AbstractInfoBase.cs:629
   CMS.DataEngine.AbstractInfoProvider`3.SetInfo(TInfo info) in D:\CMS\PRODUCTION\11.0\CMSSolution\DataEngine\Abstract\Providers\AbstractInfoProvider_3.cs:950
   CMS.Ecommerce.ShoppingCartInfoProvider.SetOrderItemsInternal(ShoppingCartInfo cart) in D:\CMS\PRODUCTION\11.0\CMSSolution\Ecommerce\Orders\ShoppingCart\ShoppingCartInfoProvider.cs:827
   CMS.Ecommerce.ShoppingCartInfoProvider.SetOrderInternal(ShoppingCartInfo cart, Boolean generateInvoice) in D:\CMS\PRODUCTION\11.0\CMSSolution\Ecommerce\Orders\ShoppingCart\ShoppingCartInfoProvider.cs:710
   Kentico.Ecommerce.ShoppingService.CreateOrder(ShoppingCart cart) +69

Any thoughts?

Recent Answers

Peter Mogilnitski answered on May 31, 2018 13:49 (last edited on May 31, 2018 14:00)

Well it looks like you have a foreign key issue. The way the foreign key works is you can not have a value that is not a primary key. i.e. in your case you trying to insert a row in COM_OrderItem table and putting a value in OrderItemOrderID that doesn't not exist in COM_Order.OrderID column. Normally at first you would insert row in COM_Order, then you insert corresponding rows in COM_OrderItem that referencing newly COM_Order row via COM_OrderItem.OrderItemOrderID -> COM_Order.OrderID.

Most likely when you call your ShoppingService().CreateOrder(Cart)... Your Cart object is missing some information and when you try to create an order based on it, it crushes...

0 votesVote for this answer Mark as a Correct answer

Brandon Prudent answered on May 31, 2018 18:31

I'm certain you're correct - the million dollar question is what am I missing? Since that lib is a black box it seems I'm out of luck until someone can explain the internals. Seems like I shouldn't be able to blow up an API like that, yeah? I'm tempted to just insert these records myself .. it just kind of defeats the point of Kentico in the first place doesn't it?

0 votesVote for this answer Mark as a Correct answer

Brandon Prudent answered on May 31, 2018 20:02

So it turns out the fix was quite simple ... Cart.Validate() needs to come after setting customer & billing info and before placing the order.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on May 31, 2018 20:59

This is the time when start decompiling Kentico dlls with re-sharper. If you don't have re-sharper you can use dotPeek. It is free and part of re-sharper ultimate. This way you can see what methods it calls inside the DLL and what checks it does.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.