Developer forum

Forum » Development » Error when running Price.SavePrices();

Error when running Price.SavePrices();

E-mail notifications
Lars Sørensen
Reply
Posted on 13/06/2014 15:43:16

Hi guys,

I'm getting this SQL error when updating prices:

Incorrect syntax near ')'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at Dynamicweb.eCommerce.Prices.Price.SavePrices(IEnumerable`1 prices)

 

Anything I can do here?

BR.

Lars


Replies

 
Imar Spaanjaars Dynamicweb Employee
Imar Spaanjaars
Posted on 13/06/2014 15:48:49
Reply

>> Anything I can do here?

Post your code maybe?

Imar

 
Lars Sørensen
Posted on 16/06/2014 09:31:19
Reply

Good point;-=

 

Price.SavePrices(Prices);

Where Prices is a collection of Price.Price objects:

var price = new Price()
                    {
                        ProductID = product.ID,
                        VariantID = product.VariantID,
                        LanguageID = product.LanguageID,
                        Quantity = salesprice.SalesQty,
                        ValidFrom = salesprice.ValidFrom,
                        ValidTo = (salesprice.ValidTo.Year==1900) ? Dynamicweb.Dates.MaxDate() : salesprice.ValidTo,
                        Amount = salesprice.SalesPrice ?? 0,
                    };

 
Hans-Henrik Stefansen
Posted on 23/06/2014 14:21:23
Reply

Hi Lars,

Do you set the ID on any of the Price objects?

One of the first things the method do, is filling a dataset with existing prices.
And in order to do so, it creates a comma delimited string of the ID's, where each ID is surrounded by single-quotes.

Like so: SELECT * FROM EcomPrices WHERE PriceID IN ( '','PRICE1','PRICE10' )

If the ID of one of the Price objects contains a single-quote, this could cause the SQL-exception.

 
Imar Spaanjaars Dynamicweb Employee
Imar Spaanjaars
Posted on 23/06/2014 16:44:39
Reply

Doesn't the data access code correctly handle this scenario?

I think it's a very bad idea to have a quote in an ID field, so I think this is theoretical issue anyway...

Imar

 
Lars Sørensen
Posted on 24/06/2014 13:53:00
Reply

Hi Hans-Henrik,

 

No, we're using the Price object exactly as written above, so no IDs. We create all the prices and run a clean-up in old prices afterwards, but the clean-up happens after this error uccurs.

But we just upgraded to latest hotfix, and the problem seems to be gone nog.

BR.

Lars

 
Hans-Henrik Stefansen
Posted on 24/06/2014 16:33:36
Reply

Hi Lars,

I'm happy to hear that upgrading fixed the issue.
However, it is not very reassuring, since no changes has been to that part of the code lately.

But I've made some changes to the method to prevent the previous described issue with the PriceID, which could trigger the exception you've posted.
And I also discovered that the method infact did not support saving both new and existing prices simultaneously.

Both issues will be fixed with the next hotfix for 8.4.1.

@Imar:
No, the data access code did not handle it in anyway, which also was the basis for my theory.

Hans-Henrik

 
Imar Spaanjaars Dynamicweb Employee
Imar Spaanjaars
Posted on 24/06/2014 19:21:06
Reply

I see. Thanks for clearing that up.

 

Imar

 

You must be logged in to post in the forum