Conversion failed when converting the nvarchar value '/' to data type int.

J A asked on June 14, 2018 20:03

I upgraded a site from Kentico 8.1 all of the way up to Kentico 10. it is now showing the following error:

Message: [CMSAbstractTransformation.DataBind]:

SELECT *
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
WHERE [NodeAliasPath] = @NodeAliasPath AND [DocumentCulture] = @DocumentCulture

Caused exception: Conversion failed when converting the nvarchar value '/' to data type int.

Exception type: System.Exception

Message:

SELECT *
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
WHERE [NodeAliasPath] = @NodeAliasPath AND [DocumentCulture] = @DocumentCulture

Caused exception: Conversion failed when converting the nvarchar value '/' to data type int.

Exception type: System.Exception Stack trace: at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex) at CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) at CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query) at CMS.DataEngine.GeneralConnection.RunQueryWithRetry(QueryParameters query, Int32 retryCount) at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords) at CMS.DataEngine.DataQueryBase1.GetDataFromDBInternal() at CMS.DataEngine.DataQueryBase1.GetDataFromDB() at CMS.DataEngine.DataQueryBase1.GetData() at CMS.DataEngine.DataQueryBase1.get_Result() at CMS.DataEngine.ObjectQueryBase2.GetResults(IDataQuery query, Int32& totalRecords) at CMS.DataEngine.DataQueryBase1.GetDataFromDB() at CMS.DocumentEngine.DocumentQueryProperties.GetDataInternal(IDocumentQuery query, Func1 baseGetDataMethod, Action1 setTotalRecords) at CMS.DataEngine.DataQueryBase1.GetData() at CMS.DataEngine.ObjectQueryBase2.GetData() at CMS.DocumentEngine.DocumentQueryBase2.GetData() at CMS.DataEngine.DataQueryBase1.get_Result() at CMS.DataEngine.DataQueryBase1.GetResults(IDataQuery query, Int32& totalRecords) at CMS.DataEngine.DataQueryBase1.GetDataFromDB() at CMS.DocumentEngine.DocumentQueryProperties.GetDataInternal(IDocumentQuery query, Func1 baseGetDataMethod, Action1 setTotalRecords) at CMS.DataEngine.DataQueryBase1.GetData() at CMS.DataEngine.DataQueryBase1.get_Result() at CMS.DataEngine.MultiObjectQueryBase3.EnsureTypedResult() at CMS.DataEngine.MultiObjectQueryBase3.System.Collections.IEnumerable.GetEnumerator() at CMS.DataEngine.EnumerableWrapper1.-GetEnumerator>d__5.MoveNext() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source) at System.Linq.EnumerableExecutor1.ExecuteBoxed() at CMS.DataEngine.CMSQueryProvider1.Execute[TResult](Expression expression) at CMS.Controls.KTTransformations.pageCategory(Object NodeID) in E:\inetpub\wwwroot\ktintranet\CMS\App_Code\KTIntra\Classes\TransformationMethods.cs:line 80 at ASP.cmsvirtualfiles_transformations_kt_product_partssearchsuggestionsjson_ascx.__DataBind__control2(Object sender, EventArgs e) in E:\inetpub\wwwroot\ktintranet\CMS\CMSVirtualFiles\Transformations\KT.Product\PartsSearchSuggestionsJSON.ascx:line 2 at System.Web.UI.Control.OnDataBinding(EventArgs e) at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) at System.Web.UI.Control.DataBindChildren() at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) at CMS.DocumentEngine.Web.UI.CMSAbstractTransformation.DataBind()

Message: Conversion failed when converting the nvarchar value '/' to data type int.

Exception type: System.Data.SqlClient.SqlException Stack trace: 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.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) 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 CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)

Event URL: /Special-Pages/Parts-Search-Suggestions.aspx?searchtext=Bakery%20case%20cover&searchmode=anyword URL referrer: http://Ktintranet/Store-Engineering-Parts-Ordering/Frequently-Ordered-Parts.aspx User agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)

The contents of E:\inetpub\wwwroot\ktintranet\CMS\CMSVirtualFiles\Transformations\KT.Product\PartsSearchSuggestionsJSON.ascx:

`<%# If(DataItemIndex == 0, "[", "") %><%# If(DataItemIndex != 0, ",", "") %><%# "{\"id\":\""+GetSearchValue("DocumentID")+"\",\"label\":\""+CMS.Controls.KTTransformations.formatStringLength(GetSearchValue("SKUNumber").ToString(), 8, "", ' ')+"     "+CMS.Controls.KTTransformations.formatStringLength(GetSearchValue("SKUShortDescription").ToString(), 30, "...", ' ')+"     "+CMS.Controls.KTTransformations.formatStringLength(CMS.Controls.KTTransformations.pageCategory(GetSearchValue("NodeID")), 20, "...", ' ')+"\",\"value\":\""+GetDocumentUrl(GetSearchValue("DocumentID"))+"\"}" %><%# If(DataItemIndex == DataItemCount-1, "]", "") %>`

Any Ideas of what could be causing the error?

upgrade

Recent Answers

Peter Mogilnitski answered on June 15, 2018 01:35 (last edited on June 15, 2018 01:59)

The transformation and the query seem perfectly correct, but the error is pretty clear: Conversion failed when converting the nvarchar value '/' to data type int. It means that nvarchar value = '/' in COM_SKU or View_CMS_Tree_Joined (which consists of CMS_Class, CMS_Document, CMS_Tree). You can use use this solution to search your db for '/'. You can modify it i.e. SET @SearchStr2 = QUOTENAME(@SearchStr,'''') to search for exact much.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on June 15, 2018 10:20

Check the TransformationMethods.cs file. Debug your website locally and find out what row/record has the bad data and you should be able to get the data cleaned up. If the TransformationMethods.cs file is calling a view, make sure the view exists in the database because the specific page type views were removed in v9.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on June 19, 2018 10:40

What is the code of the kt.product.partssearchsuggestionsjson transformation? Looks like the error is coming from there. What transformation methods are used? What are the fields and the datatype of the fields? In case you are using multiple page types and they have fields with the same name, they need to be the same data type too.

0 votesVote for this answer Mark as a Correct answer