Classic ASP Discussion

Print Page | Close Window

Array

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=12452
Printed Date: 29 March 2026 at 4:24am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Array

Posted By: Mattblack
Subject: Array
Date Posted: 05 November 2004 at 3:30pm

Back again.

Ive never used arrays, and dont really understand them too much. But i think i need to use them,

Scenario is.... got my post codes database, can now enter a postcode and it will throw up all the postcodes within a given mileage. I need to collect these postcodes and then use them in the search criteria in another table (Find all records where the postcode is "one of these").

Anyone offer any help? I dont know how to do it.

Replies:

Posted By: michael
Date Posted: 05 November 2004 at 4:30pm

Well first load all your give postcodes into an array like
array = recordset.GetRows( Rows, Start, Fields )
Now I don't know how you query so I give you the more complex form, you may be able to streamline it further if your query is simpler.
Dim i as integer = 0
For i=0 to ubound(array)
'Query here with the postcode like
Select whatever from yourtable where postcode='" & array(i) & "'"
Next

This method can like I said be streamlined to only do one database call instead of i calls

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: ljamal
Date Posted: 06 November 2004 at 9:35am

If you join postcodes table with the othe rtable you won't need the array.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: Mattblack
Date Posted: 06 November 2004 at 9:38am

how come? I would need to calculate the distance the user selects and run a search for all that are nearby surely?

Please tell me theres another way because it takes ages to process the page

Posted By: michael
Date Posted: 06 November 2004 at 11:00am

well ljamal is partly right, depending on the formula you can do it in sql server or even access, all depends on how complex your forumla is. Generally asp has more calculation options then either database. So if you show the forumla on how you process it we can tell you if it's possible in either db system.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Mattblack
Date Posted: 06 November 2004 at 12:10pm

Sounds good, so here goes. Its quite easy really...

x1 is the longitude value for the FROM post code
y1 is the latitude value for the FROM post code
x2 is the longitude value for the TO post code
y2 is the latitude value for the TO post code

Now, all I do is calculate the differences and stuff, like this....
xdif = 69.1 * (y2 - y1)
ydif = 69.1 * (x2 - x1) * cos(y1/57.3)

Where cos is the cosine function on the value in the brackets.

Then multiply x by itself and y by itself. Add the 2 values together and calculate the square root!
miles=SQR(xdif *xdif +ydif *ydif )

Simple! Thumbs Up

Posted By: michael
Date Posted: 06 November 2004 at 6:35pm

What database? Access or SQL?

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Mattblack
Date Posted: 07 November 2004 at 7:17am

appologies, its Access. Should have said.

Posted By: Mattblack
Date Posted: 07 November 2004 at 9:41am

and would the 2 tables have to be in the same database?

Posted By: ljamal
Date Posted: 07 November 2004 at 10:25am

Yes they would have to be in the same DB

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: Mattblack
Date Posted: 07 November 2004 at 11:21am

k, thats fine. But how do i do it?

Posted By: michael
Date Posted: 07 November 2004 at 1:08pm

Well the intial query to get the distance could be something like


Select SQR(

(

(69.1* (125.3 - 117.1))  * (69.1*(125.3 - 117.1))

)

+

(

(69.1 * (22.2 - 11.1)) * cos(117.1 / 57.3) * (69.1 * (22.2 - 11.1)) * cos(117.1 / 57.3))

)

(just used some values as example)

now I don't know how you want to proceed. You said you wanted to find every city (or zip) within x miles, that will get very complicated in access in one "swoosh" if not impossible. I am not that well versed in access but needless to say would be much simpler in sql server...

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: ljamal
Date Posted: 07 November 2004 at 1:18pm

Does Access allow user functions?
Or you could use ASP to figure out the value and then pass it to Access. I don't understand where the multiple callls to the database are for.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: Mattblack
Date Posted: 07 November 2004 at 2:19pm

im not very experienced with all this stuff, thats why i wrote it so that it runs through the whole table and calculates the distance to each postcode in the table. If the value is less than the specified mileage then it keeps the postcode for later (To query on the next table - the adverts table if it was a sales/wanteds board for example).

Dont understand access well enough to do this.

Posted By: michael
Date Posted: 07 November 2004 at 9:11pm

You can't do a UDF in access, you would have to write a module for that. The biggest problem you have is that loading all zip's into an array would slow the site down quite a bit.

I think what Mattblack is trying to do is let a user enter a postal code and return values of i.e. products that are within x miles of that postal code. That said I almost believe the best bet is to use what you have even though it's slow. For an application like that I really recommend a SQL Server Database or look for a wsdl webservice that you can query, that way you offload the work to the other server, thus it may cost a fee.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: dpyers
Date Posted: 07 November 2004 at 10:53pm

Not sure what you're tyring to do here or how the postal codes are laid out in Britain, but you may want to think about prepopulating a table(s) that contain postal codes adjacent to a given postal code. That way, in real-time, you first calculate those postal codes, and then if no hit, you can calcucate all the rest. Sowls down some results, but speeds up the majority of them.

Other variations include all codes within 2 postal codes of a given code (geographically - not numerically). Or do the quick calcs on all codes within 10 numeric codes of your postal code. The object here is to do a smaller set of calcs that have a high chance of containing your results, and then do the entire set of calcs for the remainder.

Did something like this for a dispatch system once and one of the factors is the "average" distance between postal codes - in Western States, a code can cover 100's of square miles. In NYC, a couple of blocks.

-------------

Lead me not into temptation... I know the short cut, follow me.

Posted By: michael
Date Posted: 08 November 2004 at 9:16am

That would result in quite a big table though. If Britain for example has 1000 postal codes and you calculate the closest two for each one. Well you do the math.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Mattblack
Date Posted: 08 November 2004 at 11:59am

Post code table looks like this...

pc	lat	lon
AB12	57.101	-2.111
AB13	57.108	-2.237
AB14	57.101	-2.27
AB15	57.138	-2.164
AB16	57.161	-2.156
AB21	57.21	-2.2

3000+ entries in it. If you search for post codes within 20 miles u will get about 100/200 returns.

SQL isnt an option im affriad, only have access and cant afford to get sql as im only doing my websites for fun - not profit

Thanks so far guys. What are these modules then?

Posted By: michael
Date Posted: 09 November 2004 at 2:38pm

Modules are vba blocks that you can use to program in access or any office product for that effect.

Go ahead and send the table layout of all affected tables and what input fields the users has, maybe I can come up with a good way for you.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Mattblack
Date Posted: 09 November 2004 at 2:49pm

not fully designed it yet mate.

The previous example is the post code table. say the table is called tblPC

There will be maybe a table called tblAdverts and there will be a primary key of something like ad_ID and a field called postcode or PC.

Cheers again

Matt

Posted By: michael
Date Posted: 10 November 2004 at 11:29am

Well it actually does not seem as complicated after I took a peek at it.
Assuming you have a table structure like the following

Advertisements
AdID - Autonumber - PK
AdName - Text
PostalCode - Text

PostalCodes
PostalCode - Text(5) - PK
Lat - Number(Double)
Lon - Number(Double)

Then you could simple do a query as the following:


SELECT Advertisements.AdID, Advertisements.AdName, Advertisements.PostalCode

FROM Advertisements INNER JOIN PostalCodes ON Advertisements.PostalCode = PostalCodes.PostalCode

WHERE SQR

(



(

     (69.1 * (PostalCodes.Lat - (Select [PostalCodes].[Lon] From PostalCodes where PostalCodes.PostalCode=[PostCode]))) 

     *

      (69.1 * (PostalCodes.Lat - (Select [PostalCodes].[Lon] From PostalCodes where PostalCodes.PostalCode=[PostCode])))

)

    +

(

    (69.1 * (PostalCodes.Lon - (Select [PostalCodes].[Lat] From PostalCodes where PostalCodes.PostalCode=[PostCode]))  * cos((Select [PostalCodes].[Lon] From PostalCodes where PostalCodes.PostalCode=[PostCode]) / 57.3)) 

       *     ;� 

     (69.1 * (PostalCodes.Lon - (Select [PostalCodes].[Lat] From PostalCodes where PostalCodes.PostalCode=[PostCode])) * cos((Select [PostalCodes].[Lon] From PostalCodes where PostalCodes.PostalCode=[PostCode]) / 57.3)) 

)



) 

< 50;

and it theoretically should show all Ads within 50 miles. Just substitude [PostCode] in asp with something like: ...'" & strUserPostCode & "'...."

I am sure you need at least access 2000 for that but that should be a given.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker