Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Invalid Floating Point

Posted on 2012-03-28
6
Medium Priority
?
1,585 Views
Last Modified: 2012-04-06
Hello,

I have a page that runs this code:

Set RS = DB.Execute("Select * from Locations where lng IS NOT NULL and lat IS NOT NULL and hidden=0")

While NOT RS.EOF
lng = RS("lng")
lat = RS("lat")
if lng <> "" AND lat <> "" THEN
SQL = "Select * from (Select ID, Property_Name, Town, Region, Property_Type, bedrooms, thumb, lat, lng, ( 6371 * acos( cos( radians(" & lat & ") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" & lng & " ) ) + sin( radians(" & lat & " ) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas) q Order By distance"
      Set RS1 = DB.Execute(SQL)
      response.Write(SQL & " - " & RS("ID") & "<BR>")
      response.Flush()
      'DB.Execute("Update Locations Set Nearest = " & CInt(RS1("Distance")) & " where ID = " & RS("ID"))
      RS1.Close
      Set RS1 = Nothing
End If
RS.MoveNext
Wend
RS.Close
Set RS = Nothing
DB.Close
Set DB = Nothing

However at some point it falls over when it DB.Executes the second query.  My code prints out the SQL query beforehand, and I can't see anything wrong with the record.  If I exclude that record from the results, it still falls over - here's the point it falls over:

Select * from (Select ID, Property_Name, Town, Region, Property_Type, bedrooms, thumb, lat, lng, ( 6371 * acos( cos( radians(28.46809) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-13.86337 ) ) + sin( radians(28.46809 ) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas) q Order By distance - 284
Select * from (Select ID, Property_Name, Town, Region, Property_Type, bedrooms, thumb, lat, lng, ( 6371 * acos( cos( radians(28.13761) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-16.76533 ) ) + sin( radians(28.13761 ) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas) q Order By distance - 285
Select * from (Select ID, Property_Name, Town, Region, Property_Type, bedrooms, thumb, lat, lng, ( 6371 * acos( cos( radians(51.15265) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-2.72049 ) ) + sin( radians(51.15265 ) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas) q Order By distance - 334

Microsoft OLE DB Provider for SQL Server error '80040e14'

An invalid floating point operation occurred.

/gvs/test.asp, line 34

Line 34 is: Set RS1 = DB.Execute(SQL), which, as can be seen, works up to the point it falls down.

Thanks in advance for any help you can give!
0
Comment
Question by:Nico2011
  • 3
  • 2
6 Comments
 
LVL 8

Assisted Solution

by:fundacionrts
fundacionrts earned 501 total points
ID: 37780510
Can you check if all parameters of functions sin() and cos() are between -1 and 1? Try to force all values used in this functions to be float:
lng = CDbl(RS("lng"))
lat = CDbl(RS("lat"))

I hope this helps you.
0
 
LVL 16

Assisted Solution

by:Peter Kwan
Peter Kwan earned 999 total points
ID: 37780516
Please check whether your code:

cos( radians(51.15265) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-2.72049 ) ) + sin( radians(51.15265 ) ) * sin( radians( lat ) )

exceeds the range [-1.0, 1.0]
0
 

Author Comment

by:Nico2011
ID: 37810189
Thanks but neither suggestion works - the Cdbl(RS("lng") etc. doesn't make any difference (I still get the fault).  

The result of the formula is 0.841966, so within -1.0, 1.0.

The page which show the SQL statements can be seen at gorgeous-villas.com/test.asp

Thanks again for your help - and please contnue to help if you can!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Accepted Solution

by:
Peter Kwan earned 999 total points
ID: 37810239
Can you please add the following line before your DB.Execute(SQL) statement:

 response.Write(SQL & " - " & RS("ID") & "<BR>")

and calculate the result of the formula before the error occurs.
0
 

Author Comment

by:Nico2011
ID: 37810690
Thanks - I have done so - here's the result:

Select * from (Select ID, Property_Name, Town, Region, Property_Type, bedrooms, thumb, lat, lng, ( 6371 * acos( cos( radians(37.88244) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(23.75656 ) ) + sin( radians(37.88244 ) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas) q Order By distance

I have just run this in SQL editor (in program SQL Manager 2011) and indeed get the same 'floating point' error - there must be something wrong with the record being selected, but the lng and lat from the villas DB in the record being called look absolutley fine to me.  I have worked around he issue by switching that record, and one other, off, and it works, so it must be something *invisible* with the record!
0
 

Author Closing Comment

by:Nico2011
ID: 37815356
Neither actually helped, but the responses you gave would have been corrct if there hadn'tbeen some error with the record in the database, so I'm closing this and awarding the points
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question