Search Page > Results Page > Then Sort Columns ASP / Frontpage / SQL Server (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

ddonaldson -> Search Page > Results Page > Then Sort Columns ASP / Frontpage / SQL Server (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)


  • I have tried the sorttable.js file, but it will not work because of the way I format the results.
  • I have tried the various solutions easily found here, but they assume the results are on the same page as the search, or say to use: 

    page.asp?=<%Request.response("form_name")

  • I have tried SpiderWeb Woman's javascript link method (which works, but only if the form and search results are on the same page)
  • I have tried response.write (QueryString) into the url of the link
  • I have tried response.write (fp_sQry) into the url of the link


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



TexasWebDevelopers -> RE: Search Page > Results Page > Then Sort Columns ASP / Frontpage / SQL Server (5/6/2009 18:16:34)

<%
DIM xorder
xorder=request.querystring("xorder")
If request.querystring("xorder")="" then
xorder="LName"
end if
%>

sql = "SELECT * FROM [tablename] ORDER BY " & xorder & " ASC"

<a href="page.asp?xorder=Fname">Order by</a>
<a href="page.asp?xorder=Lname">Order by</a>
<a href="page.asp?xorder=Address">Order by</a>

Display the data in a table. Place the hyperlinks at the top of each column.



ddonaldson -> RE: Search Page > Results Page > Then Sort Columns ASP / Frontpage / SQL Server (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 -> RE: Search Page > Results Page > Then Sort Columns ASP / Frontpage / SQL Server (5/7/2009 12:36:33)

I just wanted to add that I have reviewed all these links as well:

http://www.frontpagewebmaster.com/fb.asp?m=235246&key=sort%2Csearch%2Cresults

http://www.frontpagewebmaster.com/fb.asp?m=355988&key=sort%2Csearch%2Cresults

And I see where the solution is to put the search criteria value into the link. Is this basically the only way to accomplish sorting with ASP, Frontpage DBW and SQL?



TexasWebDevelopers -> RE: Search Page > Results Page > Then Sort Columns ASP / Frontpage / SQL Server (5/7/2009 15:07:32)

quote:

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 supposed that would work ....then the "sort by" would be a form button instead of a hyperlink at the top of the column

---sort by city button-------


<form action="resultspage.asp" method="post">
<input name="city" id="city" type="hidden" value="<%=city%>">
<input name="name" id="name" type="hidden" value="<%=name%>"> ... and so on...

<input name="sortBy" id="sortBy" type="hidden" value="city">
<input name="submit" type="submit" id="sort by" value="order by">
</form>

---sort by name button-------

<form action="resultspage.asp" method="post">
<input name="city" id="city" type="hidden" value="<%=city%>">
<input name="name" id="name" type="hidden" value="<%=name%>"> ... and so on...

<input name="sortBy" id="sortBy" type="hidden" value="name">
<input name="submit" type="submit" id="sort by" value="order by">
</form>

Declare and capture all of your search field variables at the top of the results page e.g.
DIM sortBy, name, address, city, and so on...
sortBy=request.form("sortBy")
If request.form("sortBy")="" then
sortBy="city"
end if
name=request.form("name")
address=request.form("address")
city=request.form("city")
and so on....



ddonaldson -> RE: Search Page > Results Page > Then Sort Columns ASP / Frontpage / SQL Server (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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625