PDA

View Full Version : How do I sort Order?

ragol_67
04-11-2005, 08:02 PM
I have a function that works fine, but when I try to add an order command, it doesn't seem to work. Here is the original code:



<!-- #INCLUDE file="DataSource.asp" -->
<%
function ConstructSQL()
dim strDateLow, strDateHigh, strRunLow, strRunHigh
dim strDirector, strTitle
dim strSQL, strWhere

' first get form data
strDateLow = request.form("date_low")
strDateHigh = request.form("date_high")
strBoolean1 = request.form("boolean1")
strRunLow = request.form("run_low")
strRunHigh = request.form("run_high")
strBoolean2 = request.form("boolean2")
strDirector = request.form("director")
strBoolean3 = request.form("boolean3")
strTitle = request.form("title")

' construct first part of SQL statement. We are querying a query
strSQL = "Select * from qryMovies Where "
strWhere = ""

' now add user criteria
if strDateLow <> "" and strDateHigh <> "" then
strWhere = " (ReleaseDate >= #" & strDateLow & "# and ReleaseDate <= #" & strDateHigh & "#) "
end if
if strRunLow <> "" and strRunHigh <> "" then
' only add the And/Or if user entered Release Date criteria
if strWhere <> "" then
strWhere = strWhere & " " & strBoolean1
end if
strWhere = strWhere & " (RunTime >= " & strRunLow & " and RunTime <= " & strRunHigh & ") "
end if
if strDirector <> "" then
if strWhere <> "" then
strWhere = strWhere & " " & strBoolean2
end if
strWhere = strWhere & " (DirectorName Like '" & strDirector & "%')"
end if
if strTitle <> "" then
if strWhere <> "" then
strWhere = strWhere & " " & strBoolean3
end if
strWhere = strWhere & " (Title Like '" & strTitle & "%')"
end if

' check if user did not enter criteria
if strWhere = "" then
strWhere = "true"
end if

ConstructSQL = strSQL & strWhere
End function
%>

<HTML><HEAD><TITLE>Process Search</TITLE></HEAD>
<BODY>
<B>Search Results for:</B><BR><I>
<%
dim strSQL
strSQL = ConstructSQL()
response.write strSQL
%>
</I>
<TABLE BORDER=1>
<TR>
<TD><B>Title</B></TD>
<TD><B>Director</B></TD>
<TD><B>Release Date</B></TD>
<TD><B>Run Time</B></TD>
</TR>
<%
Set objRS = Server.CreateObject("ADODB.recordset")
objRS.open strSQL, strConnect
do while not objRS.eof
response.write "<tr>"
response.write "<td><a href=movie.asp?ID="
response.write objRS("MovieID") & ">"
response.write objRS("Title") & "</a></td>"
response.write "<td>" & objRS("DirectorName") & "</td>"
response.write "<td>" & objRS("ReleaseDate") & "</td>"
response.write "<td>" & objRS("RunTime") & "</td>"
response.write "</tr>"
objRS.MoveNext
Loop
objRS.close
%>
</TABLE>



</BODY></HTML>



It works fine, I can enter search criteria in, and it finds the correct criteria, but when I change all the request.form's, to request.querystring's, the search criteria no longer works. It says that I didn't enter any criteria, therefore it displays all results.



Thank you,
Nick!
miranda
04-12-2005, 12:20 AM
When I first looked at your page I saw that you were writing out the SQL statement to help you debug. Good going there. When I read your query though, there is no ORDER BY statement in it. So I am confused as to how you wanted it to work if it isn't there.
ragol_67
04-12-2005, 12:23 AM
Ya, I have been messing around with the code for the past few hours trying to figure it out. Which is why that wasn't there. I have added that back in, but it still isn't working. Could you please check my code that I have on their now?

Thanks,
Nick!



<!-- #INCLUDE file="DataSource.asp" -->
<%
function ConstructSQL()
dim strDateLow, strDateHigh, strRunLow, strRunHigh
dim strDirector, strTitle
dim strSQL, strWhere

' first get form data
strDateLow = request.querystring("date_low")
strDateHigh = request.querystring("date_high")
strBoolean1 = request.querystring("boolean1")
strRunLow = request.querystring("run_low")
strRunHigh = request.querystring("run_high")
strBoolean2 = request.querystring("boolean2")
strDirector = request.querystring("director")
strBoolean3 = request.querystring("boolean3")
strTitle = request.querystring("title")
strOrder = request.querystring("Order")

if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if
response.write "<HR>" & strSQL & "<HR>"

' construct first part of SQL statement. We are querying a query
strSQL = "Select * from qryMovies Where "
strWhere = ""

' now add user criteria
if strDateLow <> "" and strDateHigh <> "" then
strWhere = " (ReleaseDate >= #" & strDateLow & "# and ReleaseDate <= #" & strDateHigh & "#) "
end if
if strRunLow <> "" and strRunHigh <> "" then
' only add the And/Or if user entered Release Date criteria
if strWhere <> "" then
strWhere = strWhere & " " & strBoolean1
end if
strWhere = strWhere & " (RunTime >= " & strRunLow & " and RunTime <= " & strRunHigh & ") "
end if
if strDirector <> "" then
if strWhere <> "" then
strWhere = strWhere & " " & strBoolean2
end if
strWhere = strWhere & " (DirectorName Like '" & strDirector & "%')"
end if
if strTitle <> "" then
if strWhere <> "" then
strWhere = strWhere & " " & strBoolean3
end if
strWhere = strWhere & " (Title Like '" & strTitle & "%')"
end if

' check if user did not enter criteria
if strWhere = "" then
strWhere = "true"
end if

ConstructSQL = strSQL & strWhere
End function
%>

<HTML><HEAD><TITLE>Process Search</TITLE></HEAD>
<BODY>
<B>Search Results for:</B><BR><I>
<%
dim strSQL
strSQL = ConstructSQL()
response.write strSQL
%>
</I>
<TABLE BORDER=1>
<TR>
<TD><B>Title</B></TD>
<TD><B>Director</B></TD>
<TD><B>ReleaseDate</B></TD>
<TD><B>RunTime</B></TD>
</TR>
<%
Set objRS = Server.CreateObject("ADODB.recordset")
objRS.open strSQL, strConnect
do while not objRS.eof
response.write "<tr>"
response.write "<td><a href=movie.asp?ID="
response.write objRS("MovieId") & ">"
response.write objRS("Title") & "</a></td>"
response.write "<td>" & objRS("DirectorName") & "</td>"
response.write "<td>" & objRS("ReleaseDate") & "</td>"
response.write "<td>" & objRS("RunTime") & "</td>"
response.write "</tr>"
objRS.MoveNext
Loop
objRS.close
%>
</TABLE>

<%
strAction = "process_search.asp?"
strAction = strAction & strWhere & "Order="
%>
<a href=<% =strAction & "Title" %> >
Sort on Title</a><br>
<a href=<% =strAction & "ReleaseDate" %> >
Sort on Release Date</a><br>
<a href=<% =strAction & "RunTime" %> >
Sort on Run Time</a>

</BODY></HTML>
miranda
04-12-2005, 07:26 AM
try writing out the individual paramaters in the variable strAction instead of trying to use the where statement. Like so
strAction = "process_search.asp?"
strAction = strAction & "date_low=" & strDateLow
strAction = strAction & "&date_high=" & strDateHigh
strAction = strAction & "&boolean1=" & strBoolean1
strAction = strAction & "&run_low=" & strRunLow
strAction = strAction & "&run_high=" & strRunHigh
strAction = strAction & "&boolean2=" & strBoolean2
strAction = strAction & "&director=" & strDirector
strAction = strAction & "&boolean3=" & strBoolean3
strAction = strAction & "&title=" & strTitle
strAction = strAction & "&Order="


It should work for you now
ragol_67
04-13-2005, 06:03 PM
Sweet, that worked. Thank you! :D
miranda
04-13-2005, 06:33 PM
You're Welcome