Solved

SQL Invalid Floating Point

Posted on 2012-03-28
6
1,338 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 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now