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

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>
 
LVL 2
brian_appliedcpuAsked:
Who is Participating?
 
jdfultonConnect With a Mentor Commented:
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
 
b_alghaziCommented:
What do you mean by sortability and searchability? as i can see your statement already has "ORDER BY" which will sort the results.
0
 
brian_appliedcpuAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jdfultonCommented:
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
 
b_alghaziCommented:
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
 
jdfultonCommented:
yep missed that
0
 
brian_appliedcpuAuthor Commented:
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
 
jdfultonCommented:
Please paste your code again.
0
 
brian_appliedcpuAuthor Commented:
I think I have an issue in the SQL server.
How do I turn sorting on?
0
 
jdfultonCommented:
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
 
brian_appliedcpuAuthor Commented:
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
 
jdfultonCommented:
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
 
brian_appliedcpuAuthor Commented:
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
 
brian_appliedcpuAuthor Commented:
Cool...the points are all yours...
but thans also to b_alghazi  for his input.



0
 
jdfultonCommented:
thank you.  good luck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.