|
| |
Search Page > Results Page > Then Sort Columns ASP / Frontpage / SQL Server
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
ddonaldson
Posts: 6
Joined: 3/20/2009
Status: offline
 |
Search Page > Results Page > Then Sort Columns AS... - 5/6/2009 17:45:02
What little hair i have left is vanishing..
8 hours now, looking for a simple, clean way to sort results off the dbrw (using SQL 2003 db)
Let me set the scene:
- I consider myself an extremely advanced frontpage/access/sql user (mostly thanks to you guys!). My asp skills are barely mediocre.
- I have a search criteria page page with 15 form fields called "lead_search.asp". Complex, but works a beaut.
- I have a search results page called "search_results.asp" that runs off a SQL2003 box. Complex, but works a beaut.
- I cannot combine the "lead_search.asp" page and search_results.asp page.
- All of my queries work perfectly. There are no errors of any kind.
Now, I want users to be able to sort by one of 7 different fields, but clicking a link/arrow/button. I understand the concept. When the user clicks, a script runs to re-run the query with a different "SORT_BY" parameter in the query string, and posts itself to the same page (search_results.asp)
Using JavaScript, asp, dhtml, or whatever, I want the user to be able to sort several of the columns on the search results page, without having to inject the entire, massive, 1300+ character query string and search parameters into each url (due to personal pride and security issues)
There has got to be a clean, way to do this.
Can I insert the query string and the Sort By variable into the URL by JavaScript? Is there a way to hide it?
Or should I pass all the variables into the a hidden form on the search_results.asp page that just takes all the variables from the lead_search.asp page?
I am at my wits end.
Any hints or links that I can read or research before resorting to the "please post your code" method? I want to take up as little of your time as possible with this.
Many thanks,
d
|
|
|
|
ddonaldson
Posts: 6
Joined: 3/20/2009
Status: offline
|
RE: Search Page > Results Page > Then Sort Column... - 5/7/2009 12:12:39
TexasWebDevelopers!
Thank you so very much for your help. Now the sort is working, but the query does not keep the existing results, or insert any of the user-entered values into the query.
Here's the working query string:
SELECT [results].dns, [results].company_name, [results].address, [results].city, [results].state, [results].zip, [results].sic_description, [results].sic_number, [results].phone, [results].executive1, [results].ct_income_level, [results].ct_minority_pct, [results].annual_sales, Max([contact].contact_date) AS Maxcontact_date, '$' + convert(varchar,cast([results].[annual_sales] as money),1) as [Annual Sales] FROM contact INNER JOIN results ON [contact].dns=[results].dns WHERE (results.dns LIKE '%%%' AND Results.company_name LIKE '%Auto Zone%' AND Results.city LIKE '%%%' AND Results.state LIKE '%%%' AND Results.zip LIKE '%%%' AND Results.zip LIKE '%%%' AND Results.zip LIKE '%%%' AND Results.sic_number LIKE '%%%' AND Results.annual_sales >= 0 AND Results.annual_sales <= 9999999999 AND Results.business_decription LIKE '%%%' AND Results.executive1 LIKE '%%%' AND Results.ct_minority_pct >= 0 AND Results.ct_income_level LIKE '%%%') GROUP BY [results].dns, [results].company_name, [results].address, [results].city, [results].state, [results].zip, [results].sic_description, [results].sic_number, [results].phone, [results].executive1, [results].ct_income_level, [results].ct_minority_pct, [results].annual_sales ORDER BY results.company_name ASC;
As you can see, the parameter:
Results.company_name LIKE '%Auto Zone%'
When you click the SortBy link, it vanishes and substitute the original default value of %:
Results.company_name LIKE '%%%'
Does it matter if this goes in the header, top of page, or right by the frontpage generated code or the "search_results.asp" page?
<%
DIM SortBy
SortBy=request.querystring("SortBy")
If request.querystring("SortBy")="" then
SortBy="city"
end if
%>
Do I need to dim all of the form field values in the above code? ie,
DIM company_name
company_name=request.querystring("company_name")
Thanks again for your help!
d
| |
|
|
|
ddonaldson
Posts: 6
Joined: 3/20/2009
Status: offline
|
RE: Search Page > Results Page > Then Sort Column... - 5/7/2009 18:02:59
Texas,
Woooohoo! It works!
I'm not sure how/why because I left out the DIMS statement at the beginning for all the field values... But, I did call for the field values (by ACCIDENT) using this bit of ASP that seems to work ok:
<input type="HIDDEN" name="city" value="<%=Server.HtmlEncode(Request("city"))%>">
Summary (for easy searching)
Search page, results page, sort by reposting the form field values
In the header of your search results page, put this:
<%
DIM SortBy
SortBy=request.querystring("SortBy")
If request.querystring("SortBy")="" then
SortBy="results.city"
end if
%>
Create your search results using the Frontpage Database Results Wizard
Create a mini form that saves the values the user entered in the search page form, and add it to the header link:
To sort by City, Ascending:
<form method="POST" action="search_results.asp">
<input type="HIDDEN" name="company_name" value="<%=Server.HtmlEncode(Request("company_name"))%>">
<input type="HIDDEN" name="dns" value="<%=Server.HtmlEncode(Request("dns"))%>">
<input type="HIDDEN" name="address" value="<%=Server.HtmlEncode(Request("address"))%>">
<input type="HIDDEN" name="city" value="<%=Server.HtmlEncode(Request("city"))%>">
<input type="HIDDEN" name="state" value="<%=Server.HtmlEncode(Request("state"))%>">
<input type="HIDDEN" name="zip1" value="<%=Server.HtmlEncode(Request("zip1"))%>">
<input type="HIDDEN" name="zip2" value="<%=Server.HtmlEncode(Request("zip2"))%>">
<input type="HIDDEN" name="zip3" value="<%=Server.HtmlEncode(Request("zip3"))%>">
<input type="HIDDEN" name="sic_number" value="<%=Server.HtmlEncode(Request("sic_number"))%>">
<input type="HIDDEN" name="business_description" value="<%=Server.HtmlEncode(Request("business_decription"))%>">
<input type="HIDDEN" name="annual_sales1" value="<%=Server.HtmlEncode(Request("annual_sales1"))%>">
<input type="HIDDEN" name="annual_sales2" value="<%=Server.HtmlEncode(Request("annual_sales2"))%>">
<input type="HIDDEN" name="ct_minority_pct" value="<%=Server.HtmlEncode(Request("ct_minority_pct"))%>">
<input type="HIDDEN" name="ct_income_level" value="<%=Server.HtmlEncode(Request("ct_income_level"))%>">
<input type="HIDDEN" name="executive1" value="<%=Server.HtmlEncode(Request("executive1"))%>">
<input type="HIDDEN" name="SortBy" value="results.city">City
<input type="submit" value="↓" style="font-size: 10px; font-family: Arial Black; font-weight: bold"></td>
To sort by State, Ascending:
(same form, but change the second to last line)
<input type="HIDDEN" name="SortBy" value="results.state">City
Then, just repeat for as many columns you wan to sort by.
Important: Don't name the form! name="botid2" is bad. You don't even need it.
Amazing.
Thank you Texas, I appreciate it.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
