ORDER BY parameter in CS5.5

Report · Aug 08, 2011

I recently upgraded to CS5.5 from DW8.

I have always used the behavior panel to create simple queries, including a parameter set to Request.QueryString("sorton") or just Request("sorton") to resort the items on the page.

CS5.5 does it another way, but the pages are not re-sorting. I cannot find a way to response.write to the complete query to the page either. I can print the query up to, but not including the ORDER BY parameter.

Here is the recordset from CS5.5:

<%
Dim rsProperty__sorton
rsProperty__sorton = " SortOrder, Price"
If (Request.Querystring("sorton") <> "") Then
rsProperty__sorton = Request.Querystring("sorton")
End If
%>

<%
Dim rsProperty
Dim rsProperty_cmd
Dim rsProperty_numRows

Set rsProperty_cmd = Server.CreateObject ("ADODB.Command")
rsProperty_cmd.ActiveConnection = MM_PropertyDB_STRING
rsProperty_cmd.CommandText = "SELECT PropID, AddNumber, AddStreet, AddCity, AddZip, Price, BR, BA, YB, Headline, Area, p1, p1YN, p1size, AreaName, vturl, PropType, Status.Status, SortOrder FROM PropAll WHERE SortOrder < 5 ORDER BY ?"
rsProperty_cmd.Prepared = true
rsProperty_cmd.Parameters.Append rsProperty_cmd.CreateParameter("param1", 200, 1, 255, rsProperty__sorton) ' adVarChar

Set rsProperty = rsProperty_cmd.Execute
rsProperty_numRows = 0
%>

The link is:

http://72.41.83.57/real_estate/Listings/Real_estate_Listing_List.asp?sorton=AddStreet

I'm not even sure what the code says anymore.

Here is a sample of the old code:

<%
Dim rsSold__sorton
rsSold__sorton = "SoldDate DESC"
If (Request("sorton") <> "") Then
rsSold__sorton = Request("sorton")
End If
%>
<%
set rsSold = Server.CreateObject("ADODB.Recordset")
rsSold.ActiveConnection = MM_PropertyDB_STRING
rsSold.Source = "SELECT SortOrder, Status.Status, PropID, AddNumber, AddStreet, AddCity, Price, Property.Status, PropTypeID, Tract, pA, pAYN, pAsize, PropType, SoldDate, DisplayDate FROM PropAll WHERE ( SortOrder =10 or SortOrder = 6) and PropTypeID = 20 AND DropDate>Date() ORDER BY " + Replace(rsSold__sorton, "'", "''") + ""
rsSold.CursorType = 0
rsSold.CursorLocation = 2
rsSold.LockType = 3
rsSold.Open()
rsSold_numRows = 0
%>
Which still works and is easier to read. AND I could response.write(rsSold.Source) which I can no longer do, so I could see if it was right.

Report · Aug 13, 2011

DW started using the command object syntax after DW8, as it is much less prone to SQL injection. I do not see where the problem in the code is. Try printing the value of rsProperty__sorton after it is assinged to the query string value to make sure the evaluation is working correct. You'll need to post the entire page code for any more assistance.