Incorrect syntax near the keyword 'ASC'.

Incorrect syntax near the keyword 'ASC'.
Environment InformationeSpaceVer: 28 (Id=132, PubId=139, CompiledWith=10.0.702.0)
RequestUrl: (Method: GET)
AppDomain: /LM/W3SVC/50/ROOT/Divue_Core-104-131698576073182470
FilePath: C:\OutSystems\Sandboxes\QIPQ5G047\Platform Server\running\Divue_Core\Employees.aspx
Locale: en-US
DateFormat: yyyy-MM-dd
PID: 76544 ('w3wp', Started='4/17/2018 10:13:58 AM', Priv=571Mb, Virt=18048Mb)
TID: 215
Thread Name:
.NET: 4.0.30319.42000
Stack:Incorrect syntax near the keyword 'ASC'.
   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 OutSystems.HubEdition.DatabaseProvider.SqlServer.ExecutionService.ExecutionService.ExecuteReader(IDbCommand cmd)
   at #Hmb.#Qmb.ExecuteReader(String description, Boolean isApplication, Boolean transformParameters, Boolean skipLog)
   at OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd, GenericRecordList`1 rl, String description, Boolean transformParameters, Boolean skipLog)
   at OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd, GenericRecordList`1 rl, String description)
   at ssDivue_Core.Flows.FlowMainFlow.ScrnEmployees.FuncssPreparation.datasetGetEmps(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, String qpstList_SortColumn_GetOrderBy, String qpstEmps_SearchKeyword)
   at ssDivue_Core.Flows.FlowMainFlow.ScrnEmployees.Preparation(HeContext heContext)
   at ssDivue_Core.Flows.FlowMainFlow.ScrnEmployees.Page_Load(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Hi Jared,

In the Screen Employees you have, in the Preperation, a query called GetEmps that causes this error. Is it a SQL or an Aggregate? Given the nature of the error, there's something wrong with the sorting. Does your screen use dynamic sorting, and have you forgotten to supply the initial sorting attribute, so you have something like AttributeToSort + "ASC", and the former is empty?


I am using a Database on another Module called Database, but I haven't inputed any employee information yet in the Emps Entitiy on the Database Module.

Everything is the default SilkUI Dublin setup. I haven't setup any keywords or sorting yet. It did it automatically.


When I open up the aggregate GetEmps. Under Sorting this is what is written. 

SyntaxEditor Code Snippet

List_SortColumn_GetOrderBy(EmpTable.Id, "{Emps}.[FirstName]")

That's it. 

Have you tried debugging to see what happens? Checked the local variables used in the query etc.? Removed sorting and see if that helps? Etc.?

In the preperation, I only see ONE sorting item. That's the one shown in the above post, but every single one of these are sorting in the WebScreen page, but it's not showing it in the Sorting list. Could that be that issue.

I tried adding Dynamic Sorting to all of the above in the Sorting in the Preperation to no avail. I am going to delete the page and start over I think. 

Well, redid the whole page and everything is working perfectly. No clue what went down.

Good to hear. Must've been something falling over inadvertently...

I had this problem after messing with the dynamic sorting of an aggregate and found this answer. This happened to me after adding a static sort in addition to an existing dynamic sort. 

I didn't have to recreate the whole page. What worked for me was to:

  1. Cut the dynamic sorting expression from the aggregate and remove the dynamic sort
  2. Publish (No more error, but no dynamic sorting)
  3. Add a new dynamic sort to the aggregate, pasting in the same expression that I cut
  4. Publish again

Not sure if all of those steps were required, but it worked!