...

View Full Version : How can my users sort data in a table by a field they chose? URGENT!!!



startbar
07-07-2004, 07:48 PM
hi, i have a access database for courses.

http://www.startbar.co.uk/coursemanager/courses.asp

i have lists of data in fields i.e coursename, course date, course venue etc..

at the moment all data is sorted by coursedate ASC but i want to know how i can allow users to sort the data by a field they choose. ie to sort the data by course venue by clicking venue.

help would be very much appriciated! i am a newbie! thanks

raf
07-07-2004, 08:17 PM
welcome here !

this is actualy quite easy. Just display the columnnames as links

like
<a href="http://www.startbar.co.uk/coursemanager/courses.asp?sortby=id" title="Sort the results by id"> id </a>

then, when you select the records, you dynamically build the selectquery, using the request.querystring("sortby") in the ORDER BY clause. Make sure to have a defaultvalue for the first load

you an also use an up and down arrow, and place links on them with a 'sortby' and a 'sortorder' querystringvalue.


This is the basic setup. The downside is that you disclose your tablestructue thriugh the links. So is security is an issue, you'd better set up an array that contains the label you add to the querystringvalue, and the corresponding tablecolumn.

startbar
07-07-2004, 08:24 PM
strSQL="SELECT * FROM Courses WHERE CourseStatus=1 ORDER BY CourseStartDate ASC"
' Open the Database
objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
IF NOT objRS.EOF THEN
'objRS.Filter=objRS("CourseStatus")=1
%>

<tr><td><b><img src="coursename.gif"> <a href="courses.asp?sortby=CourseTitle"> Course Title</b></a></td>

what else do i have to do? it does not work! (sorry im a newbie)

raf
07-07-2004, 10:40 PM
:confused:
I don't see any dynamic querybuilding.

If the links look like
<a href="http://www.startbar.co.uk/coursemanager/courses.asp?sortby=id&sortorder=ASC" title="Sort the results by id"> id (ascending)</a>
(see previous post) Your code could look like


dim ordervariable, sortorder
ordervariable = "CourseStartDate" 'default column to sort on
sortorder = "ASC" 'default sortorder

if (len(request.querystring("sortby")) >= 1) then
ordervariable = request.querystring("sortby")
end if
if (len(request.querystring("sortorder")) >= 1) then
ordervariable = request.querystring("sortorder")
end if
strSQL="SELECT * FROM Courses WHERE CourseStatus=1 ORDER BY " & ordervariable & " " & sortorder
...

startbar
07-08-2004, 02:57 PM
k it does work except the sortorder part

dim ordervariable, sortorder
ordervariable = "CourseStartDate" 'default column to sort on
sortorder = "ASC" 'default sortorder

if (len(request.querystring("sortby")) >= 1) then
ordervariable = request.querystring("sortby")
end if
if (len(request.querystring("sortorder")) >= 1) then
ordervariable = request.querystring("sortorder")
end if
strSQL="SELECT * FROM Courses WHERE CourseStatus=1 ORDER BY " & ordervariable & " " & sortorder

' Open the Database
objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
IF NOT objRS.EOF THEN
'objRS.Filter=objRS("CourseStatus")=1
%>

<tr><td><b><img src="coursename.gif"> <a href="courses.asp?sortby=CourseTitle&sortorder=ASC">Course Name</b></a></td>

when i use the sortorder is just doesnt load??

thanks

startbar
07-08-2004, 02:59 PM
and how would i make it so an arrow picture for the accending or decending appears next to the field title when the data is sorted by that field?

raf
07-08-2004, 03:18 PM
print out the composed query to see what statement is sent to the db. like this

strSQL="SELECT * FROM Courses WHERE CourseStatus=1 ORDER BY " & ordervariable & " " & sortorder
response.write strSQL
<edit>Change
if (len(request.querystring("sortorder")) >= 1) then
ordervariable = request.querystring("sortorder")
end if

into

if (len(request.querystring("sortorder")) >= 1) then
sortorder = request.querystring("sortorder")
end if

(i made a copy-past error)
</edit>



about the pictures. Just put a picture between the <a></a> tags.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum