U4-650 - Member section, the "Other" node does not work on SQL CE

Created by Sebastiaan Janssen 19 Aug 2012, 14:55:17 Updated by Sebastiaan Janssen 30 Aug 2012, 12:48:03

Subtask of: U4-136

The query that's used contains the ASCII conversion, which is not available in SQL CE:

{"Error running Reader: SQL Statement: select umbracoNode.id, umbracoNode.uniqueId, umbracoNode.level, umbracoNode.parentId, umbracoNode.path, umbracoNode.sortOrder, umbracoNode.createDate, umbracoNode.nodeUser, umbracoNode.text, cmsMember.Email, cmsMember.LoginName, cmsMember.Password from umbracoNode inner join cmsContent on cmsContent.nodeId = umbracoNode.id inner join cmsMember on cmsMember.nodeId = cmsContent.nodeId where umbracoNode.nodeObjectType = @nodeObjectType AND (ASCII(SUBSTRING(text, 1, 1)) NOT BETWEEN ASCII('a') AND ASCII('z')) AND (ASCII(SUBSTRING(text, 1, 1)) NOT BETWEEN ASCII('A') AND ASCII('Z')) order by umbracoNode.text

Exception: System.Data.SqlServerCe.SqlCeException: The function is not recognized by SQL Server Compact. [ Name of function = ASCII,Data type (if known) = ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior) at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlCeParameter[] commandParameters)"}

''Originally created on CodePlex by [sebastiaan|http://www.codeplex.com/site/users/view/sebastiaan]'' on 8/11/2012 11:48:36 AM [Codeplex ID: 30996 - Codeplex Votes: 1]


Sebastiaan Janssen 30 Aug 2012, 12:47:56

Fixed in changeset: http://umbraco.codeplex.com/SourceControl/changeset/7d416f8e5e39 Ammended in changeset http://umbraco.codeplex.com/SourceControl/changeset/2d5656a603c1 as initial fix was wrong ... and again in http://umbraco.codeplex.com/SourceControl/changeset/be223b8567ab because I was being silly

Priority: Normal

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 4.8.0, 4.9.0

Due in version: 4.9.0


Story Points: