?
Solved

I want to make my asp/sql output sortable and searchable. 500pts.

Posted on 2004-07-30
15
Medium Priority
?
300 Views
Last Modified: 2010-04-17
I am new at this so bear with me on the wording.
I have an asp page that builds a table from an sql query...as show below.  I want to add sortablility and searchability to it.
Also I am having a problem where about 1/2 of the people that go to the page see no content.

<form method="POST" action="ThisPage.asp?flag=1">

<%
Response.CacheControl = "no-store"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

              Set objConn = Server.CreateObject("ADODB.Connection")
              Set rs = Server.CreateObject("ADODB.Recordset")
              objConn.Open "DSN=DatabaseName;UID=UserID;PWD=xxxxxxx;DATABASE=DatabaseName"

    ' Build SQL String
strSQL = "SELECT * FROM table WHERE table.field1 Like '" & VARIABLE & "' ORDER BY table.field2 Desc, table.field3 Desc, table.field4 Desc"
     rs.Open strSQL, objConn, 3, 3          

'    I display the rusults in a normal table that has several of the fields either hyperlinked.
while not rs.eof
id = rs("ID")
Field1 = rs("Field1")
Field2 = rs("Field2")
Field3 = rs("Field3")
Field4 = rs("Field4")            
Field5 = "<a href='otherpage.asp?Field5=" & Field5 & "'> " & Field5 & " </a>"

   %>


<table border="1" width="950" height="21" >

      <tr>
         
          <td width="200" bgcolor="<%=cellcolor%>" align="center">
                  <font size="2" face="Arial Narrow">&nbsp;<% =Field1%></font></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
                  <font size="2" face="Arial Narrow"><% =Field2%></font></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
                  <font size="2" face="Arial Narrow"><% =Field3%></a></font></td>
          <td width="60" bgcolor="<%=cellcolor%>" align="center">
                  <font size="2" face="Arial Narrow"><% =Field4%></font></td>
          <td width="120" bgcolor="<%=cellcolor%>" align="center">
                  <font size="2" face="Arial Narrow"><% =Field5%></font></td>
 </tr>    


<%

Response.flush()
rs.movenext()
wend
rs.Close
set rs = nothing
objConn.Close
set objConn = nothing
%>
   
  </table>
 
0
Comment
Question by:brian_appliedcpu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 

Expert Comment

by:b_alghazi
ID: 11683189
What do you mean by sortability and searchability? as i can see your statement already has "ORDER BY" which will sort the results.
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11683568
I would like to have the option to either select a small icon ^ V or select the column header to sort ascending and then descending...and also the option to have a row of blanks that will filter the results (if possible).

I have seen things in Java and even some in asp that are fairly nice.
0
 
LVL 6

Expert Comment

by:jdfulton
ID: 11684758
Here is a start and should give you an idea.  I also changed your method of creating recordsets a little.  Much cleaner my way and you can add part of it to a function.asp page you can include and reuse on multiple pages.  What do you mean blanks?


<form method="POST" action="ThisPage.asp?flag=1">

<%
Response.CacheControl = "no-store"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

request("sortorder")

'Place in function page

  set db = Server.CreateObject("ADODB.Connection")
  db.open dbcon

function openrs(strsql)
  set rs=server.createobject("ADODB.Recordset")
  rs.open strsql, db, 3, 1
  set openrs=rs
end function

function closers()
  rs.close
  set rs=nothing
end function

'End place on function page


set rs=openrs("SELECT * FROM table WHERE field1='" & VARIABLE & "' ORDER BY " & sortorder & " DESC")

while not rs.eof
id = rs("ID")
Field1 = rs("Field1")
Field2 = rs("Field2")
Field3 = rs("Field3")
Field4 = rs("Field4")          
Field5 = "<a href='otherpage.asp?Field5=" & Field5 & "'> " & Field5 & " </a>"

     
%>


<table border="1" width="950" height="21" >

      <tr>
         
          <td width="200" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field1">Field1</a></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field2">Field2</a></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field3">Field3</a></td>
          <td width="60" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field4">Field4</a></td>
          <td width="120" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field5">Field5</a></td>
 </tr>    


      <tr>
         
          <td width="200" bgcolor="<%=cellcolor%>" align="center">
               <font size="2" face="Arial Narrow">&nbsp;<% =Field1%></font></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
               <font size="2" face="Arial Narrow"><% =Field2%></font></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
               <font size="2" face="Arial Narrow"><% =Field3%></a></font></td>
          <td width="60" bgcolor="<%=cellcolor%>" align="center">
               <font size="2" face="Arial Narrow"><% =Field4%></font></td>
          <td width="120" bgcolor="<%=cellcolor%>" align="center">
               <font size="2" face="Arial Narrow"><% =Field5%></font></td>
 </tr>    


<%

Response.flush()
rs.movenext()
wend
closers()
%>
   
  </table>
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:b_alghazi
ID: 11684774
just a small note:
in order not to have the colomn headers with each record, it is better to remove the part

<table border="1" width="950" height="21" >

      <tr>
         
          <td width="200" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field1">Field1</a></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field2">Field2</a></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field3">Field3</a></td>
          <td width="60" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field4">Field4</a></td>
          <td width="120" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field5">Field5</a></td>
 </tr>    


outside the while loop
0
 
LVL 6

Expert Comment

by:jdfulton
ID: 11684788
yep missed that
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11686070
Two items to note...
'" & VARIABLE & "' ORDER BY " & sortorder & " DESC")
should be
'" & VARIABLE & "' ORDER BY ' " & sortorder & " ' DESC")

and there is something wrong with the
request("sortorder")

but I am not sure, but it always is sorting by the second field.

Is there a way to make it ascending/descending?
0
 
LVL 6

Expert Comment

by:jdfulton
ID: 11686180
Please paste your code again.
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11686210
I think I have an issue in the SQL server.
How do I turn sorting on?
0
 
LVL 6

Expert Comment

by:jdfulton
ID: 11686249
No it's your above code

'" & VARIABLE & "' ORDER BY ' " & sortorder & " ' DESC")

this is not correct sortorder has to be a column name in your database table.  No need for '' around it.

'" & VARIABLE & "' ORDER BY " & sortorder & "  DESC")
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11686259
If I take the ' ' out I get this

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

0
 
LVL 6

Expert Comment

by:jdfulton
ID: 11686280
oops sorry i found it.

add the following lines after the sortorder request

sortorder=request("sortorder")

if sortorder="" then
sortorder="field1"  'set the field to whatever default column you want to sort by
end if


0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11686304
OK that did it....without the ' '
Would you explain why I do not need the ' '?


I also had to make
 db.open dbcon
 db.open "DSN=DatabaseName;UID=UserID;PWD=xxxxxxx;DATABASE=DatabaseName"
for it to work.

Is there a way to toggle ascending / descending?
0
 
LVL 6

Accepted Solution

by:
jdfulton earned 2000 total points
ID: 11686332
You do not need the ' ' because it's not a record value.  its a column name in the table.

for ex:  "Select * from table ORDER BY ID DESC"

can be:  

idvalue="id"

"Select * from table ORDER BY " & idvalue & " DESC"

To change the asc/desc  you will have to do this.



orderby=request("orderby")  'add this

set rs=openrs("SELECT * FROM tblads ORDER BY " & sortorder & " " & orderby)   'change this

'add this after above SQL

if orderby="ASC" then  
orderby="DESC"
else
orderby="ASC"
end if

'your links need to be changed to this basically added &orderby=<%=orderby%>

      <tr>
         
          <td width="200" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=Field1&orderby=<%=orderby%>">Field1</a></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=Field2&orderby=<%=orderby%>">Field2</a></td>
          <td width="90" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=Field3&orderby=<%=orderby%>">Field3</a></td>
          <td width="60" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=Field4&orderby=<%=orderby%>">Field4</a></td>
          <td width="120" bgcolor="<%=cellcolor%>" align="center">
               <a href="ThisPage.asp?flag=1&sortorder=field5&orderby=<%=orderby%>">Field5</a></td>
 </tr>  

0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11686439
Cool...the points are all yours...
but thans also to b_alghazi  for his input.



0
 
LVL 6

Expert Comment

by:jdfulton
ID: 11686440
thank you.  good luck
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Introduction to Processes
Starting up a Project
Suggested Courses
Course of the Month8 days, 12 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question