
add a column to an array from recordset
Can anyone tell me how I would add a column to an array made up from a DB. I've looked at the get.rows() method shown on<BR><BR>http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=161<BR><BR>what I want to do is add a variable called Distance on as a column to the 'recordset' array.<BR><BR>Cheers for ANY help.<BR><BR><BR>P.S. Bill if you read this, I've reformatted the rest of my code but am stuck on creating the array, I've not really played around with them yet.

RE: add a column to an array from recordset
Just put a placeholder into your SELECT statement:<BR>SELECT FieldA, FieldB, FieldC, '' AS Distance FROM Table<BR><BR>Or, add the field to the recordset object BEFORE calling .GetRows().<BR><BR>That's about the 2 easiest ways. #1 being easier than #2.

RE: add a column to an array from recordset
I have to do a big calculation on the Distance varaible before I can get it. I'd probably use method 2, <BR><BR>and do this<BR><BR>1. SQL" "...get the fields and records I need<BR>2. Calculate the Distances and put them into an array<BR>3. SQL" ".. + distance array<BR>4. new array<BR><BR>How do I add a 1 column array to a recordset so I can complete step 3?<BR><BR>Thanks for the help so far<BR><BR>:)

No, go with method 1
Just put in an extra dummy field as a constant. Either zero or ''.<BR><BR>You can even name it, just for future maintenance reliability (so somebody who doesn't understand it doesn't remove it).<BR><BR>SELECT 0 AS actualDistanceToBeCalculated, field1, field2, ...<BR><BR><BR>This is by far the more efficient way.<BR><BR>

RE: No, go with method 1
Could you give an example Bill? I've tried a few things and I'm getting errors, my code looks like<BR><BR>SQL = "SELECT * FROM results WHERE results.lat="& desiredlat &" AND results.longi="& desiredlong _ <BR>& " OR results.lat BETWEEN " & minLat & " AND " & maxLat _ <BR>& " AND results.longi BETWEEN " & minLong & " AND " & maxLong <BR><BR>desiredlat=3.1415926*desiredlat/180<BR>desiredlong=3.1415926*desiredlong/180<BR><BR>Set RS = conn.Execute( SQL ) <BR><BR>Do Until RS.EOF<BR>matchedlat=3.1415926*RS("lat")/180<BR> matchedlong=3.1415926*RS("longi")/180<BR> <BR> IF strMeasurement="Mile" THEN <BR> Distance=ArcCos(Cos(desiredlat)*Cos(desiredlong)*C os(matchedlat)*Cos(matchedlong)+Cos(desiredlat)*Si n(desiredlong)*Cos(matchedlat)*Sin(matchedlong)+Si n(desiredlat)*Sin(matchedlat))*3963.19<BR> Distance=Round(Distance,1) <BR> ELSEIF strMeasurement="Kilometre" THEN <BR> Distance=ArcCos(Cos(desiredlat)*Cos(desiredlong)*C os(matchedlat)*Cos(matchedlong)+Cos(desiredlat)*Si n(desiredlong)*Cos(matchedlat)*Sin(longi2)+Sin(des iredlat)*Sin(matchedlat))*6378.137<BR> Distance=Round(Distance,1)<BR> END IF<BR>...<BR>...<BR>...<BR>RS.MoveNext <BR>Loop%><BR><BR>The actual fields I use from the DB are <BR><BR>city, area, property_town, property_postcode, fee, period, deposit, avail_date, FileName, needed_flatmates, contact, phone_number, ID<BR><BR>I know I'm a pain but I do appreciate the help!<BR><BR>Thanks :)<BR>

Well, for starters...
...you *MUST* initialize/calculate values *BEFORE* you use them.<BR><BR><%<BR>' ALL SIX of these values *MUST* be calculated...<BR>desiredlat=3.1415926*desiredlat/180<BR>desiredlong=3.1415926*desiredlong/180<BR><BR>minLat = desiredLat  deltaLat<BR>maxLat = desiredLat + deltaLat<BR>minLong = desiredLong  deltaLong<BR>maxLong = desiredLong + deltaLong<BR><BR>' *BEFORE* you generate the SQL!<BR>SQL = "SELECT 0 AS calcDistance, * FROM results " _<BR>& " WHERE results.lat BETWEEN " & minLat & " AND " & maxLat _ <BR>& " AND results.longi BETWEEN " & minLong & " AND " & maxLong <BR><BR>...<BR>%><BR><BR>And can I make one OHtooobvious change?<BR><BR><%<BR>' do this THE VERY FIRST THING in the ASP page!<BR>' (at a minimum, *before* the DO loop)<BR>IF strMeasurement="Mile" THEN multiplier = 3963.19 ELSE multiplier = 6378.137<BR>%><BR><BR>And then instead of repeating the ugly distance calc where the only difference is what is being multiplied by:<BR><BR>Distance = ArcCos( _<BR> Cos(desiredlat) * Cos(desiredlong) * Cos(matchedlat) * Cos(matchedlong) _<BR> + Cos(desiredlat) * Sin(desiredlong) * Cos(matchedlat) * Sin(matchedlong) _<BR> + Sin(desiredlat) * Sin(matchedlat) )<BR> ) * multiplier<BR><BR>Of course you could improve that a lot more by calculating the Sin and Cos of the desiredLat/desiredLong *BEFORE* the DO loop!<BR><BR>Why make the identical calculation every time through the loop??? Only the Sin/Cos of the "matched" values has to be calculated *in* the loop. No?<BR><BR>

Try something like this...
Obviously untested, and I dunno if we can play this trick on the recordset like this or not, though I think we can.<BR><BR><% <BR>IF strMeasurement="Mile" THEN multiplier = 3963.19 ELSE multiplier = 6378.137 <BR><BR>' ALL SIX of these values *MUST* be calculated... <BR>desiredlat=3.1415926*desiredlat/180 <BR>desiredlong=3.1415926*desiredlong/180 <BR><BR>minLat = desiredLat  deltaLat <BR>maxLat = desiredLat + deltaLat <BR>minLong = desiredLong  deltaLong <BR>maxLong = desiredLong + deltaLong <BR><BR>CosDLat = Cos(desiredlat)<BR>CosDLong = Cos(desiredlong)<BR>SinDLong = Sin(desiredlong)<BR>SinDLat = Sin(desiredlat)<BR><BR>SQL = "SELECT 0 AS Distance, * FROM results " _ <BR>& " WHERE results.lat BETWEEN " & minLat & " AND " & maxLat _ <BR>& " AND results.longi BETWEEN " & minLong & " AND " & maxLong <BR><BR>Set RS = Server.CreateObject("ADODB.Recordset")<BR>RS.Open SQL, conn, adOpenStatic, adLockOptimistic<BR><BR>Do Until RS.EOF <BR> matchedlat=3.1415926*RS("lat")/180 <BR> matchedlong=3.1415926*RS("longi")/180 <BR> <BR> CosMLat = Cos(matchedlat) ' used twice, so precalc it<BR> Distance = ArcCos( _ <BR> CosDLat * CosDLong * CosMLat * Cos(matchedlong) _ <BR> + CosDLat * SinDLong * CosMLat * Sin(matchedlong) _ <BR> + SinDLat) * Sin(matchedlat) ) <BR> ) * multiplier <BR><BR> RS("Distance") = Round( Distance )<BR> RS.MoveNext <BR>Loop<BR><BR>RS.Sort = "Distance ASC"<BR>RS.MoveFirst<BR>Do Until RS.EOF OR RS("Distance") > desiredDistance<BR> ... show one record ...<BR> RS.MoveNext<BR>Loop<BR>RS.Close ' do *NOT* update!<BR>%> <BR><BR>

RE: Well, for starters...
Yes I agree, the reason I have <BR><BR>desiredlat=3.1415926*desiredlat/180<BR>desiredlong=3.1415926*desiredlong/180<BR><BR>after the SQL statement is that I need to covert them to radians instead of degrees as the 'ugly' calulation, that it definately is, works in radians. <BR><BR>So my code now looks like<BR><BR>SQL = "SELECT lat, long FROM postcodeToLatLong WHERE postcode='" & desiredPC & "'" <BR>Set RS = conn.Execute( SQL ) <BR>IF RS.EOF THEN<BR>postvalid="No"<BR>ELSE<BR>desiredLat = RS("lat") <BR>desiredLong = RS("long") <BR>END IF<BR>IF strMeasurement="Kilometre" THEN 'based on ave 54 degrees<BR>deltaLat = 1.01 * ( iradius / 111.305 ) <BR>deltaLong = 1.01 * ( iradius / 65.576 ) <BR>ELSEIF strMeasurement="Mile" THEN<BR>deltaLat = 1.01 * ( iradius / 69.16174242424242 ) <BR>deltaLong = 1.01 * ( iradius / 40.7469696969697 ) <BR>END IF<BR><BR>' now get the minimum and maximum allowable lat/long <BR>minLat = desiredLat  deltaLat <BR>maxLat = desiredLat + deltaLat <BR>minLong = desiredLong  deltaLong <BR>maxLong = desiredLong + deltaLong <BR><BR><BR><BR>' and now use those to do a rough selection on the table... <BR>' but JOIN to the desired properties at the same time: <BR>' <BR>SQL = "SELECT * FROM results WHERE results.lat="& desiredlat &" AND results.longi="& desiredlong _ <BR>& " OR results.lat BETWEEN " & minLat & " AND " & maxLat _ <BR>& " AND results.longi BETWEEN " & minLong & " AND " & maxLong <BR> <BR>desiredlat=3.1415926*desiredlat/180<BR>desiredlong=3.1415926*desiredlong/180<BR>Dim cosdesiredlat, sindesiredlat, cosdesiredlong, sindesiredlong<BR>cosdesiredlat=cos(desiredlat)<BR >sindesiredlat=sin(desiredlat)<BR>cosdesiredlong=c os(desiredlong)<BR>sindesiredlong=sin(desiredlong) <BR>Set RS = conn.Execute( SQL ) <BR>Do Until RS.EOF<BR>matchedlat=3.1415926*RS("lat")/180<BR> matchedlong=3.1415926*RS("longi")/180<BR> <BR> Distance=ArcCos(Cosdesiredlat*Cosdesiredlong*Cos(m atchedlat)*Cos(matchedlong)+Cosdesiredlat*Sindesir edlong*Cos(matchedlat)*Sin(matchedlong)+Sindesired lat*Sin(matchedlat))*multiplier<BR> Distance=Round(Distance,1)%><BR>..<BR>..<BR>& #060;%RS.MoveNext <BR>Loop%> <BR>

Oh, I see...
...and the data in the DB is *not* in radians.<BR><BR>Okay, apologies. Personally, I would have used new variable names to avoid confusion:<BR> desiredLatDeg<BR> desiredLatRad<BR>or something along those lines. But anyway ignore that part of my answer.<BR><BR>************<BR><BR>Yah, that code's looking pretty good now.<BR><BR>

Yes it is a lot faster now, can I..
..take the Distance variable that I calculated and add it to another (the same) SQL statement as a dummy array too (like the method 1 idea)?<BR><BR>Distance is a variable just now but I would like to make it an array e.g. arrDistance(500) and then put it into the SQL statement as a dummy field so I can do a sort by on it. Does this make sense, ideally I'd like to be able to sort by distance as well so I can have the nearest ad come up first.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

