ORDER BY dynamic variable

Report · Sep 08, 2011

I have upgraded to CS5.5 and am now having trouble with the dynamic ORDER BY in the recordset dialog.

in the past I have been able to say: ORDER BY Oby and set Oby to Request.QueryString("Oby") and thereby pass the variable. I want to dynamically sort the page by first name, last name, or whatever. I cannot seem to response.write() the select statement as it is generated either since it seems to be fragmented so there is no variable to response.write.

The test with the default value is just fine. In the browser, however, I get this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

/Admin/EditionsExpressUpdate.asp, line 21

[Aside: when I searched for this issue in the forums, I asked for "order by" and there were NONE. However when I searched for order by (without the quotes) there were a number with the exact phrase order by. What kind of a search is that?????]

Report · Sep 09, 2011

The error message indicates that the value of the variable is not present and it is attempting to use the default value. Test the SQL syntax by entering a known column name as the default value. If that works, then it's a matter of why the URL variable is not getting through. Recheck the page and link that's sending it. If that checks out, check the SQL syntax - I believe the order by variable needs to be enclosed with single quotes (Order by 'Obj'). And the request statement still needs the ASP tags around it. Sorry if this seems obvious, just want to cover all bases.

Report · Sep 09, 2011

Actually I did use just the one column to begin with. Order by ArchiveName. It worked fine. It was when I used the Oby=Request.QueryString("Oby") and sent filename.asp?Oby=EditionLeft that it fell apart. For now I am just using the single sort. Much to the dismay of the customer. (or me, if I have to have three pages that are exactly the same except for the Order by column.)

Report · Sep 09, 2011

A few things come to mind....

1) If you are displaying tabular data, then you are better off sorting client side. It's a much better user experience.

2) If you need to sort on the database side, you can build the sql string dynamically

3) You can try using the ADO recordset 'sort' property. This may only be supported with client side cursors.

4) Without seeing your code, it's impossible to say what the best solution is. Are you specifying the column name or position in your sort order variable?