brian_appliedcpu
asked on
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 .Connectio n")
Set rs = Server.CreateObject("ADODB .Recordset ")
objConn.Open "DSN=DatabaseName;UID=User ID;PWD=xxx xxxx;DATAB ASE=Databa seName"
' 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"> <% =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>
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=
<%
Response.CacheControl = "no-store"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
Set objConn = Server.CreateObject("ADODB
Set rs = Server.CreateObject("ADODB
objConn.Open "DSN=DatabaseName;UID=User
' 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
%>
<table border="1" width="950" height="21" >
<tr>
<td width="200" bgcolor="<%=cellcolor%>" align="center">
<font size="2" face="Arial Narrow"> <% =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>
What do you mean by sortability and searchability? as i can see your statement already has "ORDER BY" which will sort the results.
ASKER
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.
I have seen things in Java and even some in asp that are fairly nice.
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 .Connectio n")
db.open dbcon
function openrs(strsql)
set rs=server.createobject("AD ODB.Record set")
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">Fi eld1</a></ td>
<td width="90" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1& sortorder= field2">Fi eld2</a></ td>
<td width="90" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1& sortorder= field3">Fi eld3</a></ td>
<td width="60" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1& sortorder= field4">Fi eld4</a></ td>
<td width="120" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1& sortorder= field5">Fi eld5</a></ td>
</tr>
<tr>
<td width="200" bgcolor="<%=cellcolor%>" align="center">
<font size="2" face="Arial Narrow"> <% =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>
<form method="POST" action="ThisPage.asp?flag=
<%
Response.CacheControl = "no-store"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
request("sortorder")
'Place in function page
set db = Server.CreateObject("ADODB
db.open dbcon
function openrs(strsql)
set rs=server.createobject("AD
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
%>
<table border="1" width="950" height="21" >
<tr>
<td width="200" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
<td width="90" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
<td width="90" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
<td width="60" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
<td width="120" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
</tr>
<tr>
<td width="200" bgcolor="<%=cellcolor%>" align="center">
<font size="2" face="Arial Narrow"> <% =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>
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">Fi eld1</a></ td>
<td width="90" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1& sortorder= field2">Fi eld2</a></ td>
<td width="90" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1& sortorder= field3">Fi eld3</a></ td>
<td width="60" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1& sortorder= field4">Fi eld4</a></ td>
<td width="120" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1& sortorder= field5">Fi eld5</a></ td>
</tr>
outside the while loop
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&
<td width="90" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
<td width="90" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
<td width="60" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
<td width="120" bgcolor="<%=cellcolor%>" align="center">
<a href="ThisPage.asp?flag=1&
</tr>
outside the while loop
yep missed that
ASKER
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?
'" & 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?
Please paste your code again.
ASKER
I think I have an issue in the SQL server.
How do I turn sorting on?
How do I turn sorting on?
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")
'" & 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")
ASKER
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'.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.
oops sorry i found it.
add the following lines after the sortorder request
sortorder=request("sortord er")
if sortorder="" then
sortorder="field1" 'set the field to whatever default column you want to sort by
end if
add the following lines after the sortorder request
sortorder=request("sortord
if sortorder="" then
sortorder="field1" 'set the field to whatever default column you want to sort by
end if
ASKER
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=User ID;PWD=xxx xxxx;DATAB ASE=Databa seName"
for it to work.
Is there a way to toggle ascending / descending?
Would you explain why I do not need the ' '?
I also had to make
db.open dbcon
db.open "DSN=DatabaseName;UID=User
for it to work.
Is there a way to toggle ascending / descending?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool...the points are all yours...
but thans also to b_alghazi for his input.
but thans also to b_alghazi for his input.
thank you. good luck