|
By SamC - Thu 2 Feb 2017
|
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)
==================================================
|
|
By Tiggywiggler - Thu 2 Feb 2017
|
So the primary key on that table is OrderID, PromotionID which means that the order in question already has that promotion added to it. In this case the same promotion is trying to be added twice.
BasketBLL.CalculatePromotions(objBasket, objPromotions, objPromotionsDiscount, False)
at line 865 of OrdersBLL.vb.
I suspect that this is where the problem is occurring, because this is the method that creates the list of promotion objects, and it is a duplicate entry here that I suspect is causing your issue.
BasketBLL.CalculatePromotions() appears at line 1694 of the current version of BasketBLL.vb, but I notice that you are using an older version of Kartris so you may find it on a different line.
This method then calls AddPromotion() and IT IS IN HERE that the comparison is done to ensure that the same promotion is not added twice. It is going to be here that the error is produced.
To resolve this, we would need to break point the code on a demo database, try adding two items with the same promotion (as you are doing here) and then check line-by-line what the system was doing.
The problem is that this will take ages, and as you can appreciate this is an open source project so it is not anyone's job (we manage this alongside day jobs). The only person who works on this full time is Paul, but there is a cost associated with contracting his services.
So, you can either try to find a solution to this issue yourself by looking at the code mentioned above in Visual Studio (like I said, BasketBLL.AddPromotion() is where the problem exists), wait until one of us has the time to look at this for you (I'm busy for the next 4-5 weeks) or speak to Paul directly and ask for a quote to have an immediate resolution.
Sorry I cannot be more help, but this time of year is a bit of a challenge. Hopefully the digging I have done about will get you started.
|
|
By SamC - Fri 3 Feb 2017
|
Thanks for that, much appreciated.
I'm struggling to see a pattern as to why it works fine for one product but not for another, but thanks for pointing me in the right direction, I'll have a look where you suggest and try not to break anything further 
If I manage to get anywhere with it I'll post it on here, if not I might be in contact with Paul.
Thanks.
|
|
By Tiggywiggler - Fri 3 Feb 2017
|
|
I see that you are getting it on some products and not some, and that will mean that any diagnostics work run against this problem may take longer than normal as we have to reproduce the issue as you are experiencing it, and that may mean that we have to try many different combination of issues until we stumble upon the problem.
|
|
By Tiggywiggler - Fri 3 Feb 2017
|
|
I see that you are getting it on some products and not some, and that will mean that any diagnostics work run against this problem may take longer than normal as we have to reproduce the issue as you are experiencing it, and that may mean that we have to try many different combination of issues until we stumble upon the problem.
|
|
By SamC - Fri 3 Feb 2017
|
I've been looking at this issue with the promotions a bit more to try and find out why it seems to happen inconsistently between products, and have found that its actually not inconsistent. The problem seems to occur on every occasion when multiple versions of the same product ( i.e. version A and version B of Product X ) are added to the database as part of the promotion. So I can order multiples of only Version A on promotion fine, but not multiples of Version A and Version B on promotion.
I have a clean unused install of this version of Kartris on one of my sites and on Monday I'll see if I can replicate this problem on that install, which will at least show whether its a problem with this particular database or a broader problem.
|
|
By Tiggywiggler - Sat 4 Feb 2017
|
If you CAN reproduce the fault on that version, can you also run up a version of the latest copy of Kartris and try the same please?
If you get the problem on both then we need to update the current build, if you can reproduce it on the old one but not the new one then we need to copy over whatever code is different to fix your problem.
I know that doubles your work, but it is the best way to identify the cause. It also means that if the fault appears in both versions then fixing it is quicker and easier as the development team can just work on the current version to find the fix, rather than digging out the old version and winding up a clean version etc.
|
|
By SamC - Mon 6 Feb 2017
|
Okay so I've managed to replicate the problem on a clean install of v2.7001.
It has exactly the same issue, the database won't add multiple versions of the same product when its on promotion. I've tried a number of different promotion options but the same problem occurs each time.
I'll try and set up the latest version on a test server as soon as I can and I'll let you know whether I can replicate the problem on the latest version also.
|
|
By SamC - Mon 6 Feb 2017
|
|
Oh and by the way the error log recorded exactly the same error message on the clean install as it did on my live version.
|
|
By SamC - Wed 8 Feb 2017
|
I've just run the same test on the latest version of Kartris ( just downloaded and installed ) and have found exactly the same problem occurring.
Is this something that the development team might be able to look at soon?
Maybe we're unusual in having customers ordering promotions in this way, but it does certainly cause quite a problem for us and our customers. I suppose that it does at least explain the mystery of why we've had many customers not being able to complete their orders successfully in the past.
Thanks.
|
|
By Tiggywiggler - Thu 9 Feb 2017
|
Thank you for doing the diagnostics on this one Sam, it looks like this is a long term bug.
My comments about people's availability remains the same and so you would need to act based on that advice.
|
|
By SamC - Thu 9 Feb 2017
|
No problem, its good in someways to know that its not something that's gone wrong only with my database or install!
Thanks for the time and availability advice, I'll decide how best to proceed with this issue based on those constraints.
Will this bug have come to the attention of Paul and the development team or do I need to flag it up elsewhere and if there is a resolution to this bug sometime in the future, will it be flagged up on here or elsewhere?
Thanks.
|
|
By Tiggywiggler - Thu 9 Feb 2017
|
As you can see, when I add two versions of a product, and I put promotions against each of these two versions, I do not get the error.
In my case the process goes through without an issue.
Can you more clearly explain how you have set up your products so that I can reproduce your issue please?
Here are my two promotions:
|
|
By SamC - Fri 10 Feb 2017
|
The image above is the test promotion that I created.
I think that the difference between our tests might be that I created a single promotion for a product - in this case Test Product No1. Test Product Number 1 has two versions, but its Product Number One itself that is on promotion ( not specifically its versions ).
So when I'm shopping I see that Test Product No1 is on promotion and from within the category section I add 10 x Version 1 (ProdV1) and 4 x Version 2 (ProdV2). This all appears fine in the basket and the checkout, where all applicable discounts are applied.
However, the site will never progress beyond the Checkout stage to payment stages, and produces the database error as mentioned before.
Thanks.
|
|
By Tiggywiggler - Tue 14 Feb 2017
|
Thank you for this mate. I understand the problem more fully, but it is going to require more time than I have free at the moment. I think your only option is to either fix it yourself, try to get a quote from one of the development partners (there is a link in the signature of all of my posts) or speak to the Kartris team to get it expedited.
If none of these are real options to you it will simply be a matter of being very patient and waiting until another coder on the forum has some spare time to help out.
|
|
By SamC - Mon 20 Feb 2017
|
|
Okay, thanks for that. I'll perhaps try and contact the Kartris team and see how I go with that. Thanks for you help and advice with this.
|
|
By Paul - Fri 10 Mar 2017
|
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
|
|
By SamC - Tue 14 Mar 2017
|
|
That's great Paul, thanks a lot for your help with this.
|
|