Solved

SQL Invalid Floating Point

Posted on 2012-03-28
6
1,437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Assisted Solution

by:fundacionrts
fundacionrts earned 167 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 333 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 16

Accepted Solution

by:
Peter Kwan earned 333 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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/…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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