How to give users ability to sort columns

[mgifford]

I have an interface that uses VBScript to write to an Access DB and displays records in a table on our intranet. They are records for PR events and observers we have for our medical helicopters. Each record has all the information for the event written to a database but the main summary page only shows basic info (i.e. Type, Date, Base Location, Name). I'd like to give the users the option to sort by the column header (i.e. Sort by Name, Sort by Date). I would also like to give them a sort feature so they can display only by specific parameters (i.e. Sort by Date from 01/01/09-01/31/09).

Can anyone point me in the right direction?

 

[vicvirk]

Since you are querying a db, you can pass a sort param by querystring (or a post method of a form) to see what kind of a sort you want to do.

Let's use querystring for simplicity sake.

If you have a table (tblUsers) with the following fields:

user_id
user_first_name
user_last_name

and your default query is:

sql = "select * from tblUsers"

create a link at the top of the user_first_name column on your page (myPage.asp) that goes here:

myPage.asp?sort_by=user_first_name

Before you query your db, check to see if "sort_by" is set, if it isn't your query stays the same as above, if it is set:

sql = "select * from tblUsers order by " & request.querystring("sort_by")


TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[Ferrian]

And to add in extra search criteria you can put in text boxes which the user can fill in and create and then have a submit button which passes them through, I have used querystring, but by using form tags you could pass it as a request.form("").

vDateFrom = request.querystring("datefrom")
vDateTo = request.querystring("dateto")
vSortBy = request.querystring("sortby")

sql = "Select * from Events "
sql = sql & "Where 1=1 "
if vDateFrom <> "" then
sql = sql & "And Date > " & vDateFrom 
end if
if vDateTo <> "" then
sql = sql & "And Date < " & vDateTo
end if
if vSortBy <> "" then
sql = sql & "Order By " & vSortBy
end if

The 'Where 1=1' acts as a buffer for the following Ands (and means you don't need multiple SQL statements, this one would handle all situations, if all or none of the if statements were true or false)

Validation would be required on the date fields.

 

[mgifford]

O.K., I have a start on the code for this function but am stuck. I am getting the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
/pr-obs/default_sort.asp, line 84

Here is the code for this. What am I missing?

'Create the server ADODB objects...
Set cnX = Server.CreateObject("ADODB.Connection")
Set rsPRObs = Server.CreateObject("ADODB.Recordset")
Set rsAccess = Server.CreateObject("ADODB.Recordset")

'Open the connection...
cnX.Open "PRObserverDSN"

nLevel = 0

sSQL="Select * from Access where [UserID] = '" & Session("UserID") & "'"

rsAccess.LockType=1
rsAccess.CursorType=1
rsAccess.CursorLocation=2
rsAccess.Open sSQL, cnX

If rsAccess.RecordCount > 0 Then
nLevel = rsAccess.Fields("Level")
End If

rsAccess.Close
Set rsAccess = Nothing

Dim SortBy
Dim WhereClause

SortBy = "Sort_by=Date"

if request.querystring("sort_by") <> "" then
SortBy = request.querystring("sort_by")
end If

WhereClause = ""

if cstr(request.Form("KeyWord1")) <> "" then
WhereClause = WhereClause & "[Name] = '" & cstr(request.Form("KeyWord1")) & "' and "
end if

if cstr(request.Form("Type1")) <> "" then
WhereClause = WhereClause & "[Type] = '" & cstr(request.Form("Type1")) & "' and "
end if

if cstr(request.Form("Base")) <> "" then
WhereClause = WhereClause & "[Base] = '" & cstr(request.Form("Base")) & "' and "
end if

if cstr(request.Form("Start_Date")) <> "" then
WhereClause = WhereClause & "[Date] >= " & cstr(request.Form("Start_Date")) & " and "
end if

if cstr(request.Form("End_Date")) <> "" then
WhereClause = WhereClause & "[Date] <= " & cstr(request.Form("End_Date")) & " and "
end if

WhereClause = "Where " & WhereClause & "[complete] = 'SELECT'"

'Build sSQL
sSQL="Select * from PRObs " & WhereClause & " order by " & SortBy

rsPRObs.LockType=1
rsPRObs.CursorType=1
rsPRObs.CursorLocation=2
rsPRObs.Open sSQL, cnX

%>

 

[dhookom]

I think this:

   SortBy = "Sort_by=Date"

should be

   SortBy = "[Date]"

Duane
Hook'D on Access
MS Access MVP