?
Solved

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

Posted on 2004-07-30
15
Medium Priority
?
302 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Six Sigma Control Plans
Starting up a Project

850 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