|
Group: Forum Members
Last Active: Mon 9 Sep 2019
Posts: 27,
Visits: 117
|
Hi,
I'm looking for some help with a database related problem which has just started happening for no obvious reason that I can see.
It centres around the 'Product Promotions', and only on some of the items on discount, not all of them. The problem occurs if a customer selects more than one of an item that is on discount ( i.e. two or more of the same product ), then when they checkout the site simply loops back to the checkout page rather than progressing to the payment gateway.
I've looked in the error logs and they seem to be indicating a duplicate key violation. I've been running these kind of promotions for years, with no problems, so I'm struggling to see what might have gone wrong?
Any ideas or advice would be most welcome.
This is the error log:
-----------------------------------------------------------------------------
>> OrdersBLL.Add
-----------------------------------------------------------------------------
>> System.Data.SqlClient.SqlException
>> 02/02/2017 11:49:49
>> Version:2.7001
>> CUSTOM MESSAGE:
Database Error: duplicate-key violation.
>> NUMBER:2627
>> MESSAGE:
Violation of PRIMARY KEY constraint 'PK_tblKartrisOrdersPromotions'. Cannot insert duplicate key in object 'dbo.tblKartrisOrdersPromotions'. The duplicate key value is (33634, 118).
The statement has been terminated.
>> STACK:
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)
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 OrdersBLL.Add(Int32 C_ID, String strUserEmailAddress, String strUserPassword, Address BillingAddress, Address ShippingAddress, Boolean blnSameShippingAsBilling, BasketBLL BasketObject, ArrayList BasketArray, String strOrderDetails, String strGatewayName, Int32 intLanguageID, Int32 intCurrencyID, Int32 intGatewayCurrencyID, Boolean blnOrderEmails, String strShippingMethod, Double numGatewayTotalPrice, String strEUVATNumber, String strPromotionDescription, String strPurchaseOrderNo, String strComments)
==================================================
|
|
Group: Administrators
Last Active: Fri 15 Sep 2023
Posts: 806,
Visits: 2,737
|
I am using the latest DEV code we have and I could not replicate the issue. I placed three different versions of the same product into the basket, and all three triggered the same promotion (so effectively that promotion x3). I checked out fine and when I check the tblKartrisOrderPromotions table, only one record was added to log the promotion for that order.
I am going to zip up a new build of Kartris; we've had a lot of minor tweaks and changes over last few months. I will send you a link, can you see if you install that whether you get the issue.
In the meantime, looking at the code (this is for your version before the basket refactoring), maybe the following might fix the issue without upgrading or installing a new version. In /App_Code/BLL/OrdersBLL.vb. Find this code around line 700 or so:
If objBasket.PromotionDiscount.IncTax < 0 Then
Dim objPromotions As New ArrayList
Dim objPromotionsDiscount As New ArrayList
objBasket.CalculatePromotions(objPromotions, objPromotionsDiscount, False)
For Each objPromotion As PromotionBasketModifier In objPromotionsDiscount
Dim cmdAddPromotionLinks As New SqlCommand("spKartrisOrdersPromotions_Add", sqlConn, savePoint)
With cmdAddPromotionLinks
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@OrderID", O_ID)
.Parameters.AddWithValue("@PromotionID", objPromotion.PromotionID)
.ExecuteNonQuery()
End With
Next
End If
Then add a try/catch in there, so if the subsequent record additions fail, it won't fail the whole order, but hopefully just skip adding those extra record:
If objBasket.PromotionDiscount.IncTax < 0 Then
Dim objPromotions As New ArrayList
Dim objPromotionsDiscount As New ArrayList
objBasket.CalculatePromotions(objPromotions, objPromotionsDiscount, False)
For Each objPromotion As PromotionBasketModifier In objPromotionsDiscount
Try
Dim cmdAddPromotionLinks As New SqlCommand("spKartrisOrdersPromotions_Add", sqlConn, savePoint)
With cmdAddPromotionLinks
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@OrderID", O_ID)
.Parameters.AddWithValue("@PromotionID", objPromotion.PromotionID)
.ExecuteNonQuery()
End With
Catch ex As Exception
'skip
End Try
Next
End If
--
If my post solves your issue, can you 'Mark as Answer' so it's easier for other users to find in future.
If you would like to be informed of new features, new releases, developments and occasional special bonuses, please sign up to our mailing list: http://bit.ly/19sKMZb
|