Sorting Question

[SASRS]

Here is my code. I am trying to see if I can put a

%@ LANGUAGE="VBSCRIPT" %>

<%
FieldA = Request.QueryString("A")
FieldB = Request.QueryString("B")
FieldC = Request.QueryString("C")

response.write("<TABLE ALIGN=CENTER>")
response.write("<TR bgcolor=#990033>")
response.write("<TD align=center width=200><b><font color=white size=2
face=Tahoma>")
response.write("FieldA")
response.write("</TD></b>")
response.write("<TD align=center width=200><b><font color=white size=2
face=Tahoma>")
response.write("FieldB")
response.write("</TD></b>")
response.write("</TR>")
response.write("<TR>")
response.write("<TD align=center bgcolor=#f6f0e3 width=200><font size=2
face=Tahoma>")
response.write("FieldC")
response.write("</TD>")
response.write("</TR>")
response.write("</TABLE>")

Then my data is displayed from the QueryString info above.

 

[Dave Anderson]

FieldA = Request.QueryString("A")
FieldB = Request.QueryString("B")
FieldC = Request.QueryString("C")
...
I would like to make the header row sortable by clicking on
the field name. I have tried use A HREF and passing a variable
called sort, but it doesn't seem to work. Any help is much
appreciated.

Are you asking how to (a) conditionally ask the DB to sort, (b) sort the
data on the server once you get it back from the database, or (c) sort it on
the client side? Each requires a different approach.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[SASRS]

Sorting it on the client side

Are you asking how to (a) conditionally ask the DB to sort, (b) sort the
data on the server once you get it back from the database, or (c) sort it on
the client side? Each requires a different approach.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[ljb]

Here is my code. I am trying to see if I can put a

%@ LANGUAGE="VBSCRIPT" %>

<%
FieldA = Request.QueryString("A")
FieldB = Request.QueryString("B")
FieldC = Request.QueryString("C")

response.write("<TABLE ALIGN=CENTER>")
response.write("<TR bgcolor=#990033>")
response.write("<TD align=center width=200><b><font color=white size=2
face=Tahoma>")
response.write("FieldA")
response.write("</TD></b>")
response.write("<TD align=center width=200><b><font color=white size=2
face=Tahoma>")
response.write("FieldB")
response.write("</TD></b>")
response.write("</TR>")
response.write("<TR>")
response.write("<TD align=center bgcolor=#f6f0e3 width=200><font size=2
face=Tahoma>")
response.write("FieldC")
response.write("</TD>")
response.write("</TR>")
response.write("</TABLE>")

Then my data is displayed from the QueryString info above.
------

I would like to make the header row sortable by clicking on the field name.
I have tried use A HREF and passing a variable called sort, but it doesn't
seem to work. Any help is much appreciated.

http://www.romankoch.ch/capslock/tablesort.htm - Client side sorting of an
HTML table.

Sorting large tables client side may get somewhat slow but it doesn't
require anything of the server.

 

[SASRS]

It is actually server-side, not client side, but client-side might work for
me.

This is what I have so far, I am trying to re-pass the values back to the
page.

<a
href="http://s2.asp?sort=1?FieldA=<%=FieldA%>?FieldB=<%=FieldB%>?FieldC=<%=F
ieldC%>">

I see the values when I hover over the link, but when i hit the link, my
results are blank. I believe I am overwriting the values below. How can I
work around that.

FieldA = Request.QueryString("A")
FieldB = Request.QueryString("B")
FieldC = Request.QueryString("C")

 

[Adrienne]

It is actually server-side, not client side, but client-side might work
for me.

This is what I have so far, I am trying to re-pass the values back to
the page.

<a
href="http://s2.asp?sort=1?FieldA=<%=FieldA%>?FieldB=<%=FieldB%>?FieldC=
<%=F ieldC%>">

I see the values when I hover over the link, but when i hit the link,
my results are blank. I believe I am overwriting the values below.
How can I work around that.

FieldA = Request.QueryString("A")
FieldB = Request.QueryString("B")
FieldC = Request.QueryString("C")

Here's what I do:

dim sort
sort = request.querystring("sort")

select case sort
case 1
sort = " ORDER BY field "
case 2
sort = " ORDER BY anotherfield "
case else
sort = " ORDER by field "
end select

sql = "SELECT records FROM table WHERE clause " & sort

Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, connectionstring

if rs.EOF then
%>
<p>No records found</p>
<% else %>
<table summary="List of records">
<caption>List of Records</caption>
<thead>
<tr>
<th><a href="<%=request.servervariables("script_name")%>?sort=1">Field</a>
</th>
<th><a href="<%=request.servervariables("script_name")%>?sort=2">Another
Field</a></th>
</tr>
</thead>
<tbody>
<%
while not rs.EOF
field = trim(rs("field")
anotherfield = trim(rs("anotherfield"))
%>
<tr>
<td><%=field%></td><td><%=anotherfield%></td>
</tr>
<% rs.MoveNext
wend
rs.Close
set rs = nothing
set connectionstring = nothing
%>
</tbody>
</table>
<% end if%>

FYI, the TH element usually centers and bolds the content, so no need for
the depreciated B element. You might want to remove your presentational
markup and replace it with CSS, makes debugging so much easier.

 

[HartSA]

Thank you.

Here's what I do:

dim sort
sort = request.querystring("sort")

select case sort
case 1
sort = " ORDER BY field "
case 2
sort = " ORDER BY anotherfield "
case else
sort = " ORDER by field "
end select

sql = "SELECT records FROM table WHERE clause " & sort

Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, connectionstring

if rs.EOF then
%>
<p>No records found</p>
<% else %>
<table summary="List of records">
<caption>List of Records</caption>
<thead>
<tr>
<th><a href="<%=request.servervariables("script_name")%>?sort=1">Field</a>
</th>
<th><a href="<%=request.servervariables("script_name")%>?sort=2">Another
Field</a></th>
</tr>
</thead>
<tbody>
<%
while not rs.EOF
field = trim(rs("field")
anotherfield = trim(rs("anotherfield"))
%>
<tr>
<td><%=field%></td><td><%=anotherfield%></td>
</tr>
<% rs.MoveNext
wend
rs.Close
set rs = nothing
set connectionstring = nothing
%>
</tbody>
</table>
<% end if%>

FYI, the TH element usually centers and bolds the content, so no need for
the depreciated B element. You might want to remove your presentational
markup and replace it with CSS, makes debugging so much easier.

 

[SASRS]

Adrienne

I tried your example and it clears out my values. I need to repass my
values back to the server (I think). The value of "sort" works when the
page loads. For example I change the "else" to "Code" and it sorts by Code,
but the default I want is State.
This is what I have it seems to be losing the values of "rState, insName,
and occFilt"

dim sort
sort = request.QueryString("sort")

select case sort
case 1
sort = " ORDER BY State "
case 2
sort = " ORDER BY Code "
case 3
sort = " ORDER BY Occupation "
case 4
sort = " ORDER by PolicyNumber "
case else
sort = " ORDER by State "
end select

rState = Request.QueryString("state")
insName = Request.QueryString("ins")
occFilt = Request.QueryString("fo")

table border="0" align="center" bgcolor="#990033">
<tr>
<td align=center width=50><font color=white size=2 face="Tahoma"><b><a
href="<%=request.servervariables("script_name")%>?sort=1">State</a></b></td>
<td align=center width=80><font color=white size=2 face="Tahoma"><b><a
href="<%=request.servervariables("script_name")%>?sort=2">Code</a></b></td>
<td align=center width=250><font color=white size=2
face="Tahoma"><b>Occupation</a></b></td>
<td align=center width=100><font color=white size=2 face="Tahoma"><b>Policy
Number</a></b></td>
<td align=center width=80><font color=white size=2
face="Tahoma"><b>Effective Date</b></td>
<td align=center width=80><font color=white size=2
face="Tahoma"><b>Expiration Date</b></td>
</tr>
</table>

Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtest=dsntest & "DBQ=" & Server.MapPath("ncci.mdb")
Conn.Open DSNtest

Set rs2 = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM NCCI2 where Insured = " & sIn(insName) & sort

rs2.Open strSQL, conn, 3, 3

if not rs2.eof then
nr = rs2.recordcount

for i = 1 to nr

%>
<table align="center">
<tr bgcolor="#f6f0e3">
<td align=center width=50><font size=2 face="Tahoma"><% = rs2("State")
%></td>
<td align=center width=80><font size=2 face="Tahoma"><% = rs2("Code")
%></td>
<td align=center width=250><font size=2 face="Tahoma"><% =
rs2("Occupation") %></td>
<td align=center width=100><font size=2 face="Tahoma"><% =
rs2("PolicyNumber") %></td>
<td align=center width=80><font size=2 face="Tahoma"><% =
rs2("PolicyEffective") %></td>
<td align=center width=80><font size=2 face="Tahoma"><% =
rs2("PolicyExpiration") %></td>
</tr>
<tr>
</table>

Please advise

 

[Adrienne]

Adrienne

I tried your example and it clears out my values. I need to repass my
values back to the server (I think). The value of "sort" works when
the page loads. For example I change the "else" to "Code" and it sorts
by Code, but the default I want is State.
This is what I have it seems to be losing the values of "rState,
insName, and occFilt"

dim sort
sort = request.QueryString("sort")

select case sort
case 1
sort = " ORDER BY State "
case 2
sort = " ORDER BY Code "
case 3
sort = " ORDER BY Occupation "
case 4
sort = " ORDER by PolicyNumber "
case else
sort = " ORDER by State "
end select

rState = Request.QueryString("state")
insName = Request.QueryString("ins")
occFilt = Request.QueryString("fo")

No... you're not getting it. Sort is request.querystring("sort"). If you
need the values of rState, insName and occFilt, then you will have to pass
those in the same querystring:

<a href="<%=request.servervariables("script_name")%>?sort=1&amp;rstate=<%
=rstate%>&amp;insName=<%=insName%>&amp;occFilt=<%=occFilt%>"> .

Please, for your sake and sanity, please take presenational attributes out
of your markup. Use CSS, it's a lot faster and easier to debug:

<style type="text/css">
th {font-family: tahoma, arial, sans-serif; color:#fff; font-size:80%}
</style>

If you use an external stylesheet, you can change ALL the td's and th's at
the same time! So you can use a red background and white text in February,
and green background in March, and NOT have to go into every single cell to
change it...Wow! How fashionable!

<td align=center width=50><font color=white size=2 face="Tahoma"><b>

Please advise

HTH