[SOLVED] ORDER BY clause is invalid unless TOP or FOR XML is also specified

ORDER BY clause is invalid unless TOP or FOR XML is also specified

I get "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified." error with the following code. I initially had two tables, ADSAREAS & CATEGORIES. I started receiving this error when I removed CATEGORIES table.

    Select Case SIDX  
     Case "ID" : SQLCONT1 = " AdsAreasID"
     Case "Page" : SQLCONT1 = " AdsAreasName"
     Case Else : SQLCONT1 = " AdsAreasID"  
End Select   
Select Case SORD  
     Case "asc" : SQLCONT2 = " ASC"
     Case "desc" : SQLCONT2 = " DESC"
     Case Else : SQLCONT2 = " ASC"  
End Select   
''# search feature --->
Select Case SEARCHFIELD  
     Case "ID" : SQLSFIELD = "AND AdsAreasID"
     Case "Ads Areas" : SQLSFIELD = "AND AdsAreasName"
     Case Else : SQLSFIELD = ""  
End Select  
Select Case SEARCHOPER  
     Case "eq" : SQLSOPER = " = " & SEARCHSTRING
     Case "ne" : SQLSOPER = " <> " & SEARCHSTRING
     Case "lt" : SQLSOPER = " <" & SEARCHSTRING
     Case "le" : SQLSOPER = " <= " & SEARCHSTRING    
     Case "gt" : SQLSOPER = " >" & SEARCHSTRING
     Case "ge" : SQLSOPER = " >= " & SEARCHSTRING
     Case "bw" : SQLSOPER = " LIKE '" & SEARCHSTRING & "%' "
     Case "ew" : SQLSOPER = " LIKE '%" & SEARCHSTRING & "' "
     Case "cn" : SQLSOPER = " LIKE '%" & SEARCHSTRING & "%' "
     Case Else : SQLSOPER = ""  
End Select  
''# search feature --->

SQL = "SELECT * FROM ( SELECT A.AdsAreasID, A.AdsAreasName, ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row"
SQL = SQL & " FROM ADSAREAS A"
SQL = SQL & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &") ORDER BY" & SQLCONT1 & SQLCONT2
Set objXML = objConn.Execute(SQL)

Solution

You moved the ORDER BY clause to the inner query in rewriting it. Add a parentheses (and identifier) after the WHERE clause so that ORDER BY applies to the outer SELECT instead.

SQL = "SELECT * FROM ( SELECT A.AdsAreasID, A.AdsAreasName, ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row"
SQL = SQL & " FROM ADSAREAS A"
SQL = SQL & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &")) inner ORDER BY" & SQLCONT1 & SQLCONT2