|
Last Post 2/9/2009 9:54:40 AM By lukezy.
11 replies.
2/3/2009 6:29:19 AM |
quarterhorses
Posts: 27
Joined: 8/23/2008
|
Update Error
|
I have come across an update error problem. When I press the update button on certain products it throws the error:
Conversion failed when converting the nvarchar value 'LT500R 87-90 (#CR176)' to data type int.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value 'LT500R 87-90 (#CR176)' to data type int.
It is an attribute that is causing the problem. I don't see anything that is different in the attributes. I am not sure how to debug this because the update code looks like it is in the core DLL. Any ideas how to solve this one? I have many products with similar attributes and they are working fine. Any ideas?
|
2/3/2009 8:18:15 AM |
lukezy
Posts: 2109
Joined: 6/12/2007
Location:WA, US
|
Re: Update Error
|
Please post the full exception stack.
DotShoppingCart Staff
|
2/3/2009 9:17:50 AM |
quarterhorses
Posts: 27
Joined: 8/23/2008
|
Re: Update Error
|
The frustrating thing about this is something happens and the problem will disappear. The ATV crankshaft example above wasn't working yesterday, or this morning. Now it is working! My windshield example still isn't working so I will paste the entire error in here.
Conversion failed when converting the nvarchar value 'NU' to data type int.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value 'NU' to data type int.
Source Error:
<table width="100%" bgcolor="#ffffcc">
<tbody>
<tr>
<td>An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. </td>
</tr>
</tbody>
</table>
Stack Trace:
<table width="100%" bgcolor="#ffffcc">
<tbody>
<tr>
<td>
[SqlException (0x80131904): Conversion failed when converting the nvarchar value 'NU' to data type int.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) +178
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) +63
DotShoppingCart.Commercial.Core.Product.Update(String host, Int32 productId, String productName, String sku, String shortDescription, Nullable`1 quantity, Decimal price, Nullable`1 retailPrice, Nullable`1 salePrice, Nullable`1 cost, Int32 manufacturerId, String manufacturerSKU, Int32 statusId, Int32 productTypeId, Int32 shipTypeId, Int32 shipEstimateId, Int32 taxTypeId, Nullable`1 weight, Nullable`1 weightMeasureId, Nullable`1 length, Nullable`1 height, Nullable`1 width, Nullable`1 dimensionMeasureId, Int32 sortOrder, Nullable`1 timeCreated, Nullable`1 showIfOutofStock, String categoriesValues, Nullable`1 defaultImageId, String imageIds, String imageTitleValues, String descriptorTitleValues, String descriptorDescriptionValues, String isBulletedListValues, String attributeValues, Nullable`1 billingCycleId, Nullable`1 recurringCount, String crossSellProductIds, String downloadBinaryIds, String title, String keywords, String extraHeadData, String customFields) +1073
DotShoppingCart.Commercial.Core.Product.Update(String host, Int32 productId, String productName, String sku, String shortDescription, String quantity, Decimal price, String retailPrice, String salePrice, String cost, Int32 manufacturerId, String manufacturerSKU, Int32 statusId, Int32 productTypeId, Int32 shipTypeId, Int32 shipEstimateId, Int32 taxTypeId, String weight, Nullable`1 weightMeasureId, String length, String height, String width, Nullable`1 dimensionMeasureId, String sortOrder, String timeCreated, String showIfOutofStock, ICollection`1 categories, ProductImage[] images, List`1 descriptors, List`1 attributes, Nullable`1 billingCycleId, Nullable`1 recurringCount, List`1 crossSellProductIds, List`1 productDownloads, String title, String keywords, String extraHeadData, List`1 customFields) +867
DotShoppingCart.Commercial.Core.Product.Update(Int32 productId, String productName, String sku, String shortDescription, String quantity, Decimal price, String retailPrice, String salePrice, String cost, Int32 manufacturerId, String manufacturerSKU, Int32 statusId, Int32 productTypeId, Int32 shipTypeId, Int32 shipEstimateId, Int32 taxTypeId, String weight, Nullable`1 weightMeasureId, String length, String height, String width, Nullable`1 dimensionMeasureId, String sortOrder, String timeCreated, String showIfOutofStock, ICollection`1 categories, ProductImage[] images, List`1 descriptors, List`1 attributes, Nullable`1 billingCycleId, Nullable`1 recurringCount, List`1 crossSellProductIds, List`1 productDownloads, String title, String keywords, String extraHeadData, List`1 customFields) +227
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +71
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +261
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +488
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method) +39
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +1030
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +835
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +509
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +113
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
</td>
</tr>
</tbody>
</table>
|
2/3/2009 9:27:20 AM |
quarterhorses
Posts: 27
Joined: 8/23/2008
|
Re: Update Error
|
I ran across another one part that throws an error:
Cannot insert the value NULL into column 'priceAdjustment', table 'Monkeybutt.dbo.DSC_Product_Attribute_Value'; column does not allow nulls. INSERT fails.
Cannot convert a char value to money. The char value has incorrect syntax.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'priceAdjustment', table 'Monkeybutt.dbo.DSC_Product_Attribute_Value'; column does not allow nulls. INSERT fails.
Cannot convert a char value to money. The char value has incorrect syntax.
The statement has been terminated.
Source Error:
<table width="100%" bgcolor="#ffffcc">
<tbody>
<tr>
<td>An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. </td>
</tr>
</tbody>
</table>
Stack Trace:
<table width="100%" bgcolor="#ffffcc">
<tbody>
<tr>
<td>
[SqlException (0x80131904): Cannot insert the value NULL into column 'priceAdjustment', table 'Monkeybutt.dbo.DSC_Product_Attribute_Value'; column does not allow nulls. INSERT fails.
Cannot convert a char value to money. The char value has incorrect syntax.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) +178
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) +63
DotShoppingCart.Commercial.Core.Product.Update(String host, Int32 productId, String productName, String sku, String shortDescription, Nullable`1 quantity, Decimal price, Nullable`1 retailPrice, Nullable`1 salePrice, Nullable`1 cost, Int32 manufacturerId, String manufacturerSKU, Int32 statusId, Int32 productTypeId, Int32 shipTypeId, Int32 shipEstimateId, Int32 taxTypeId, Nullable`1 weight, Nullable`1 weightMeasureId, Nullable`1 length, Nullable`1 height, Nullable`1 width, Nullable`1 dimensionMeasureId, Int32 sortOrder, Nullable`1 timeCreated, Nullable`1 showIfOutofStock, String categoriesValues, Nullable`1 defaultImageId, String imageIds, String imageTitleValues, String descriptorTitleValues, String descriptorDescriptionValues, String isBulletedListValues, String attributeValues, Nullable`1 billingCycleId, Nullable`1 recurringCount, String crossSellProductIds, String downloadBinaryIds, String title, String keywords, String extraHeadData, String customFields) +1073
DotShoppingCart.Commercial.Core.Product.Update(String host, Int32 productId, String productName, String sku, String shortDescription, String quantity, Decimal price, String retailPrice, String salePrice, String cost, Int32 manufacturerId, String manufacturerSKU, Int32 statusId, Int32 productTypeId, Int32 shipTypeId, Int32 shipEstimateId, Int32 taxTypeId, String weight, Nullable`1 weightMeasureId, String length, String height, String width, Nullable`1 dimensionMeasureId, String sortOrder, String timeCreated, String showIfOutofStock, ICollection`1 categories, ProductImage[] images, List`1 descriptors, List`1 attributes, Nullable`1 billingCycleId, Nullable`1 recurringCount, List`1 crossSellProductIds, List`1 productDownloads, String title, String keywords, String extraHeadData, List`1 customFields) +867
DotShoppingCart.Commercial.Core.Product.Update(Int32 productId, String productName, String sku, String shortDescription, String quantity, Decimal price, String retailPrice, String salePrice, String cost, Int32 manufacturerId, String manufacturerSKU, Int32 statusId, Int32 productTypeId, Int32 shipTypeId, Int32 shipEstimateId, Int32 taxTypeId, String weight, Nullable`1 weightMeasureId, String length, String height, String width, Nullable`1 dimensionMeasureId, String sortOrder, String timeCreated, String showIfOutofStock, ICollection`1 categories, ProductImage[] images, List`1 descriptors, List`1 attributes, Nullable`1 billingCycleId, Nullable`1 recurringCount, List`1 crossSellProductIds, List`1 productDownloads, String title, String keywords, String extraHeadData, List`1 customFields) +227
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +71
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +261
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +488
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method) +39
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +1030
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +835
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +509
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +113
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
</td>
</tr>
</tbody>
</table>
|
2/3/2009 10:58:52 AM |
lukezy
Posts: 2109
Joined: 6/12/2007
Location:WA, US
|
Re: Update Error
|
I think I know what happened. It probably reaches the maximum characters supported by DB schema. Which version are you using? Is it V3.0?
How many different attribute values do you have?
DotShoppingCart Staff
|
2/3/2009 11:35:58 AM |
quarterhorses
Posts: 27
Joined: 8/23/2008
|
Re: Update Error
|
In the first error example the product has 118 attributes. I am running v3.0 with some changes. I increased the field Value in DSC_Product_Attribute_Value to 100 nvarchars (50 is to short). Is there a way to change the DB to support more characters?
|
2/4/2009 10:14:15 AM |
lukezy
Posts: 2109
Joined: 6/12/2007
Location:WA, US
|
Re: Update Error
|
Yes, it's probably somewhere in the store procedure getting value truncated. Can you run SQL profile to catch the calls to DSC_Product_Add or DSC_Product_Update and then post back the store procedure call along with all the parameters? I will take a look and figure out where needs to be changed to make it work for you.
DotShoppingCart Staff
|
2/5/2009 9:03:14 PM |
lukezy
Posts: 2109
Joined: 6/12/2007
Location:WA, US
|
Re: Update Error
|
Ok, here is the fix.
In store procedure DSC_Product_Add_To_Product_Attribute and DSC_Parse_Values, find NVARCHAR(4000) and replace it with NVARCHAR(MAX)
Let me know if it fixes the issue for you.
DotShoppingCart Staff
|
2/6/2009 10:10:24 AM |
quarterhorses
Posts: 27
Joined: 8/23/2008
|
Re: Update Error
|
I also updated DSC_Product_Update_Product_Attribute to have @AttributeValues NVARCHAR(MAX).
Using the profile example I sent you, I am still getting the error:
Msg 245, Level 16, State 1, Procedure DSC_Product_Update_Product_Attribute, Line 112
Conversion failed when converting the nvarchar value 'NU' to data type int.
Using the example that I sent you, are you receiving the error?
|
2/6/2009 10:21:31 AM |
lukezy
Posts: 2109
Joined: 6/12/2007
Location:WA, US
|
Re: Update Error
|
There are several NVARCHAR(4000) and you need to change them all. Make sure you do the same thing to the function dbo.DSC_Parse_Values as well.
DotShoppingCart Staff
|
2/8/2009 3:06:13 PM |
quarterhorses
Posts: 27
Joined: 8/23/2008
|
Re: Update Error
|
I got some extra time today to look at this problem some more. To fix this problem I had to change the stored procedures:
DSC_Product_Update_Product_Attribute
DSC_Product_Add_To_Product_Attribute
The Table value function:
DSC_Parse_Values
and the missing link, the Scalar valued function:
DSC_Escape
Now the update is working properly.
|
| |