Solved

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

Posted on 2004-07-30
15
295 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

910 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now