Solved

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

Posted on 2004-07-30
15
294 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
withoutTen challenge 14 88
Fix45 challenge 15 83
mapBully challenge 6 88
create an incrementing variable name AutoHotKey 5 56
This article will show, step by step, how to integrate R code into a R Sweave document
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

708 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

13 Experts available now in Live!

Get 1:1 Help Now