Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How can my users sort data in a table by a field they chose? URGENT!!!

    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

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Code:
    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
    ...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #6
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by raf; 07-08-2004 at 04:23 PM.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •