SQL Invalid Floating Point

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!
Nico2011Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fundacionrtsAdministrador de SistemasCommented:
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
Peter KwanAnalyst ProgrammerCommented:
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
Nico2011Author Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Peter KwanAnalyst ProgrammerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nico2011Author Commented:
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
Nico2011Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.