?
Solved

SQL Invalid Floating Point

Posted on 2012-03-28
6
Medium Priority
?
1,768 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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