Multiple sort criteria [Archive]

View Full Version : Multiple sort criteria

david_uk

06-14-2005, 08:36 PM

Hi Guys,

I'm wondering if you can help me with a simple addition to the script I already have, basilcally this is my code that will display a recordset based on strBandName, what ideally I would like to be able to do is before the user clicks submit is to ask them how they want the data displayed, and in this case by Price, either ascending/descending it doesn't matter.

<%
%>



<%
Dim strSQL 'sql string
Dim objRS ' ADO Recorset

If Request.Form("strBandName") <> "" Then
strSQL = "SELECT lngAlbumID, strBandName, strAlbumName, lngPrice, strGenre FROM tblProducts " & _
"WHERE strBandName Like '%" & Request.Form("strBandName") & "%'"

'create the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not ObjRS.EOF Then 'write a table of the recordset
Response.Write _
"<table border=""1"" cellspacing=""2"" cellpadding=""1"" width=""100%"">" & _
"<tr>" & _
"<th>Album ID</th>" & _
"<th>Band Name</th>" & _
"<th>Album Name</th>" & _
"<th>Price</th>" & _
"<th>Genre</th>" & _
"<th>Add to Cart</th>" & _
"</tr>"
Do While Not ObjRS.EOF
Response.Write _
"<tr align=left><td>" & ObjRS("lngAlbumID") & "</td><td>" & ObjRS("strBandName") & "</td><td>" & ObjRS("strAlbumName") & "</td><td>" & ObjRS("lngPrice") & "</td><td>" & ObjRS("strGenre") & "</td><td>"

Call AddToCartForm

Response.write "</td></tr>"

ObjRS.MoveNext

Loop

Response.Write "</table>"

Else

Response.Write("Sorry No Records Matched your search, please try again")

End If

objRS.Close
End If
%>

<table width="80%" border="0" cellspacing="3" cellpadding="6">
<tr>
<td>
<Form Name="getband" Action="search.asp?CartID=<=%lngCartID%>" Method="post">
Enter your artist to find:
<input type="Text" name="strBandName"><br><br>
</tr>
<tr>
<td><br><input type="submit" value="Find"><Input type="Reset" value="Clear"> </td>

<td> </td>
</tr>
</form>
</table>
oh I'm quite new to ASP so if you can bear this in mind when replying please I would be grateful.

Thanks

David. :thumbsup:

miranda

06-14-2005, 11:19 PM

Add the code in red, obviously you can pick a different field to sort by if you want to




<%
Dim strSQL 'sql string
Dim objRS ' ADO Recorset

If Request.Form("strBandName") <> "" Then
strSQL = "SELECT lngAlbumID, strBandName, strAlbumName, lngPrice, strGenre FROM tblProducts " & _
"WHERE strBandName Like '%" & Request.Form("strBandName") & "%' ORDER BY strAlbumName " & Request.Form("sort")

'create the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not ObjRS.EOF Then 'write a table of the recordset
Response.Write _
"<table border=""1"" cellspacing=""2"" cellpadding=""1"" width=""100%"">" & _
"<tr>" & _
"<th>Album ID</th>" & _
"<th>Band Name</th>" & _
"<th>Album Name</th>" & _
"<th>Price</th>" & _
"<th>Genre</th>" & _
"<th>Add to Cart</th>" & _
"</tr>"
Do While Not ObjRS.EOF
Response.Write _
"<tr align=left><td>" & ObjRS("lngAlbumID") & "</td><td>" & ObjRS("strBandName") & "</td><td>" & ObjRS("strAlbumName") & "</td><td>" & ObjRS("lngPrice") & "</td><td>" & ObjRS("strGenre") & "</td><td>"

Call AddToCartForm

Response.write "</td></tr>"

ObjRS.MoveNext

Loop

Response.Write "</table>"

Else

Response.Write("Sorry No Records Matched your search, please try again")

End If

objRS.Close
End If
%>
<Form Name="getband" Action="search.asp?CartID=<=%lngCartID%>" Method="post">
<table width="80%" border="0" cellspacing="3" cellpadding="6">
<tr>
<td>

Enter your artist to find:
<input type="Text" name="strBandName"><br></td>
</tr>
<tr>
<td><select name="sort">
<option value="asc">Sort Ascending</option>
<option value="desc">Sort Descending</option>
</select></td>
</tr> <tr>
<td><br><input type="submit" value="Find"><Input type="Reset" value="Clear"> </td>

<td> </td>
</tr>
</form>
</table>

david_uk

06-15-2005, 11:17 AM

Hi Miranda,

Thanks for your input, I shall of course let you know how I get on

Thanks again,

:thumbsup:

miranda

06-15-2005, 04:35 PM

It is only an addition in the SQL Select Query. you had to scroll to see it. but here it is again for you.

strSQL = "SELECT lngAlbumID, strBandName, strAlbumName, lngPrice, strGenre FROM tblProducts " & _
"WHERE strBandName Like '%" & Request.Form("strBandName") & "%' ORDER BY strAlbumName " & Request.Form("sort")

david_uk

06-16-2005, 08:46 PM

Thank you, I did see after I'd posted my reply then tried to edit it out before anyone saw it. Not to worry, thanks again.

David.