Database Search by Relative Zip Code (Full Version)

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



Message

WinlitGroup -> Database Search by Relative Zip Code (6/10/2004 15:57:43)

I have a database of about 1500 retail stores that my company ships to sorted by ZIP. I'd like to be able to find all the stores within 100 points of the zip.

for example I type in 10450 and it will search everything >10400 and <10500,
making a relatively simple version of a store locator.

Is it possible? How can it be done? Thanks.



BeTheBall -> RE: Database Search by Relative Zip Code (6/10/2004 16:07:47)

Welcome to OutFront. Yes that is relatively simple. Is the ZIP stored in a text field or numeric? Are you using FrontPage or pure ASP?



WinlitGroup -> RE: Database Search by Relative Zip Code (6/10/2004 16:11:46)

Thank you, I feel more comfortable knowing that there are people willing to help me!

I'm using front page, and I have the db as a spreadsheet and it's part of an access file (I recently started using access).

I'm new to ASP, but I'm a quick learner when it comes to computers and programming, so any help would be greatly appriciated!

Thank you



BeTheBall -> RE: Database Search by Relative Zip Code (6/10/2004 16:14:53)

So, do you know whether the ZIP field is datatype numeric or text? What is your FrontPage experience? Have you used the database results wizard before?



WinlitGroup -> RE: Database Search by Relative Zip Code (6/10/2004 18:03:39)

I can make it either...

numeric would be good to do within 100 zips... text would be good to take the leftmost 3 digits... I guess I would rather go with the numeric +or- 50 deal though....

the results wizard is a first to me, but doesn't seem like it could be that complicated...



BeTheBall -> RE: Database Search by Relative Zip Code (6/10/2004 18:22:27)

Assuming you already have a connection to your database, open a new page in FrontPage and then click "Insert" - Datatbase Results. This will launch the Wizard. In step 1, choose your connection. Step 2, choose the table you wish to query. In step 3, choose "More Options" and then criteria. Click "Add" and choose your ZIP code field from the form field list. Unfortunately, FP will not let you choose Between at this stage, so choose "Equals" as the comparison and choose the zip field again in value. Click OK. Then click the default option and enter something like 25 as a default value. Then click OK 3 times and then Next to move to step 4. Click next again to move to Step 5. Make sure the "Add Search Form" field is checked and finish. Save the page.

You will then have to switch to HTML view and in the gray code find the line that begins with, s-criteria= edit that line so that instead of ZIP=::ZIP it reads ZIP BETWEEN ::ZIP::-50 AND ::ZIP::+50. Then save the page.

That should work.



tailgt -> RE: Database Search by Relative Zip Code (2/18/2005 15:58:46)

I'm attempting to do the same exact thing, but can't get the code to work. This is the code in my custom query:

SELECT * FROM Final WHERE (Customer_Zip = '::Customer_Zip::')

'Final' is the name of my table and 'Customer_Zip' is the name of my zip code field. What should the exact statement look like if I want it to pull a +/- range of 50 codes like above?

I appreciate it,
Tom



BeTheBall -> RE: Database Search by Relative Zip Code (2/18/2005 21:56:32)

Assuming you have the ZIP stored as a numeric value, try this:

SELECT * FROM Final WHERE (Customer_Zip BETWEEN (::Customer_Zip::+50 AND ::Customer_Zip::-50)



tailgt -> RE: Database Search by Relative Zip Code (2/21/2005 15:25:36)

Duane

Thanks, it works fine now. One other question: when the page initially loads, the message "No Records Returned" can be seen before a zip code is even entered. That's supposed to be the message that's displayed if no zip codes match the search - how can I hide that until after the search?

Thanks again-
Tom



BeTheBall -> RE: Database Search by Relative Zip Code (2/21/2005 17:03:13)

In step 3 of the wizard, choose the more options button. There you will see the no records message. Change it to something that makes more sense, say, "Please enter a ZIP code in the above form."



dzirkelb1 -> RE: Database Search by Relative Zip Code (2/22/2005 10:23:46)

Won't that show up if someone enters a zip code that isn't found though? meaning, they search for 66999, and, for a result they get "please enter a zip code in the above form"?

I think he is looking for that message to display initially, and, then to have the no records returned if there aren't any records.

If that is the case, then I suggest doing something like:

<%If FP_FieldVal(fp_rs,"ZipCode") = "" then%>
Please Enter a Zip Code
<%else%>
the rest of the table that will display the data
<%end if%>



BeTheBall -> RE: Database Search by Relative Zip Code (2/22/2005 12:27:56)

quote:

Won't that show up if someone enters a zip code that isn't found though? meaning, they search for 66999, and, for a result they get "please enter a zip code in the above form"?


You are correct. What I usually do, is hide the whole table until the form is submitted.

<%IF Request.Form("SearchField") <> "" Then%>
<table> etc.

Then after </table>

<%End If%>



tailgt -> RE: Database Search by Relative Zip Code (2/23/2005 17:43:14)

Thanks for your solutions, so far so good. Two other issues I've run into are:

1)When I make the zip code field numeric, the zero at the beginning of each code (for those beginning with a zero, i.e 02566) is dropped in my results. The zip code will display as 2566. Any way to work around this if the field is numeric (if I make the field text, typing in "99999" for some reason lists every customer in my table...).

2)I have two customer address fields - the second one may not always have an address in it. How can I surpress the db results if that field is empty so that a blank line doesn't appear on the results page?

I appreciate your help,
Tom



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125