Error on product repositiry

Imar Spaanjaars

Posted on 21/02/2018 02:10:19

On 9.4 I am getting the following when getting recent orders in the customer center:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '('.
   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.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at 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)
   at 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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at Dynamicweb.Data.Database.CreateDataReader(IDbCommand command, CommandBehavior behavior)
   at Dynamicweb.Data.Database.CreateDataReader(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction transaction, CommandBehavior behavior, Int32 commandTimeout)
   at Dynamicweb.Data.Database.CreateDataReader(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction transaction, Int32 commandTimeout)
   at Dynamicweb.Ecommerce.Products.ProductRepository.GetBySql(CommandBuilder query, Boolean doRefactoring, Boolean bulkFill, Boolean useAssortments)
   at Dynamicweb.Ecommerce.Products.ProductRepository.GetProductsByProductIDsAndVariantIDs(List`1 idCombinations, String productLanguageId, Boolean doRefactoring, Boolean useAssortments)
   at Dynamicweb.Ecommerce.Products.ProductService.GetByProductIDsAndVariantIDs(List`1 idCombinations, String productLanguageId, Boolean doRefactoring, Boolean useAssortments)
   at Dynamicweb.Ecommerce.Orders.OrderLineCollection.LoadProducts(Boolean forceLoading)
   at Dynamicweb.Ecommerce.Orders.OrderLine.get_Product()
   at Dynamicweb.Ecommerce.Orders.Order.get_Volume()

What's weird is that it doesnt happen every time, just about a third of the hits. Is some caching going on here?

Something fisht is going on in GetProductsByProductIDsAndVariantIDs.

Should I open a support case?

Replies

Oleg Rodionov

Posted on 21/02/2018 04:30:55

Hi Imar,

I don't catch any exception in CC+backend on my test environment based on DW9.4.2, proof, or is it any extra custom coding?

BR, Oleg QA

Imar Spaanjaars

Posted on 21/02/2018 13:25:12

I'm only seeing DW code in the stack trace above. Could there be anything in or around GetProductsByProductIDsAndVariantIDs that builds up a potentially invalid SQL statement?

Imar Spaanjaars

Posted on 21/02/2018 13:50:38

I finally captured the faulty SQL statement:

exec sp_executesql N'SELECT * FROM EcomProducts WHERE ( (ProductID = @p0 AND ProductVariantID = @p1) (ProductID = @p0 AND ProductVariantID = @p1) OR (ProductID = @p2 AND ProductVariantID = @p3) OR (ProductID = @p4 AND ProductVariantID = @p3) ) AND ProductLanguageID = @p5',N'@p0 nvarchar(15),@p1 nvarchar(18),@p2 nvarchar(16),@p3 nvarchar(4000),@p4 nvarchar(7),@p5 nvarchar(5)',@p0=N'UL63u5ClubStand',@p1=N'VOGreyGold.VORight',@p2=N'ImportedPROD2821',@p3=N'',@p4=N'PROD102',@p5=N'LANG1'

Notice there's a missing OR leading to broken SQL. I get this when looping over the order lines from this query:

exec sp_executesql N'SELECT * FROM EcomOrderLines WHERE OrderLineOrderID = @p0 ORDER BY OrderLineID, OrderLineParentLineID',N'@p0 nvarchar(8)',@p0=N'ORDER395'

Could it be because of a mix between products with and without variants? Or maybe because the product refers to a variant that's no longer available? I think this code:

query.Add("SELECT * FROM EcomProducts WHERE (")
For Each combi As Tuple(Of String, String) In idCombinations
If idCombinations.IndexOf(combi) >= 1 Then
query.Add("OR ")
End If
query.Add("(ProductID = {0} AND ProductVariantID = {1}) ", combi.Item1, combi.Item2)

is trying to be a little too clever by deriving the need to add an OR based on the data for the variants, and not on the fact that the query already contains an existing where clause.

Can you take another look? Do you need the database?

Imar

Nicolai Pedersen

Posted on 21/02/2018 14:53:24

This post has been marked as an answer

Hi Imar

The issue is that you have 2 orderlines with the same productid and variantid - that usually does not happen often. Maybe because you have different orderline fields? I think that causes this to happen. The result is cached - hence the only sometimes exception.

TFS#46629

BR Nicolai

Votes for this answer: 1

Imar Spaanjaars

Posted on 21/02/2018 15:05:27

Good catch. Yes, that's the case; same product, different value for the order line XML.

Thanks!

Kristian Kirkholt

Posted on 27/02/2018 14:21:16

Hi Imar

The problem regarding #46629 "Exception occurs with multiple orderlines with the same productid and variantid but with different orderlinefield values" has now been resolved in Dynamicweb version 9.4.3

To upgrade please choose this version from backend or download from here:

http://doc.dynamicweb.com/releases-and-downloads/releases

Let me know if you need any more help regarding this

Kind Regards
Dynamicweb Support
Kristian Kirkholt

You must be logged in to post in the forum

Developer forum

Error on product repositiry

Replies