error geocode longitude in dialog (decimal separator is missing?)

[cptutrecht]

After a day troubleshooting i turn here for help

In a dialog i try to use the subscriber benefit builder for geoding addresses when saving a record.

If only latitude and location field is specified in the buiilder than there is no problem in geocoding and storing the record. But as soon as i also specify the longitude control i get the error message below:

:relaties_latitude (N)	52.1336113
:relaties_longitude (N)	51962229
:relaties_mutatiedoor (C)	[email protected]
:relaties_datumtijdmutatie (T)	02-07-2013 10:49:23 65 pm

Database API specific error

Your database has returned the following error code and description to Alpha Five.
Consult your database documentation for further information.

1264 - 'Out of range value for column 'Longitude' at row 1'

It seems that the longitude field is not generated properly, i should be 5.1962229 (the decimal separator is missing)
I doubled checked that in the mysql database the field definitions are the same for latitude and longitude (Decimal 11,8)

Any clue?

[iRadiate]

Re: error geocode longitude in dialog (decimal separator is missing?)

(Decimal 11.8)

Not using MySQL but I have a datatype of numeric(15,8) which works just fine in MSSQL.
That's not a typo is it (Decimal 11comma8)?

Stephen

[cptutrecht]

Re: error geocode longitude in dialog (decimal separator is missing?)

Hi Stephen, indeed this was a typo in my post, it must be: decimal 11,8 (i corrected that post now)

In the database it was correct.
And after another evening troubleshooting still have the same result / error.

Tried a way to bypass the builder for the latitude / longitude field, but no luck yet.

I tried storing the latitude / longitude values in the afterDialogValidate event:

dim pa a p
dim address as c

address = e.DataSubmitted.Relaties_straat_postbus + " " + e.DataSubmitted.Relaties_huis_postbus_num + " " + e.DataSubmitted.Relaties_woonplaats
pa = geocode_address("address","Google")

e._set.myvar_latitude = pa.lat
e._set.myvar_longitude = pa.lon

and call in the afterInsertRecord event of call the xbasic function

function afterRecordInsert as v (e as p)
dim cn as sql::Connection
dim sql as c
dim args as sql::Arguments


a5SQLConnectionOpen(cn,"::name::CPTDEMO", session)

args.add("primaryKey",e.primaryKeyOfInsertedRecord)

'below are state vars set in the afterDialogValidate event

args.add("lat",e._set.myvar_latitude)
args.add("lon",e._set.myvar_longitude)

sql = "UPDATE relaties SET relaties.latitude = :lat, relaties.longitude = :lon WHERE relaties.relaties_nummer = :primaryKey"

cn.Execute(sql,args)

cn.close()
end function

But no luck either thusfar

[iRadiate]

Re: error geocode longitude in dialog (decimal separator is missing?)

Hi Ger,

I'm no SQL expert but that error sure sounds like a type mismatch to me. But I could be wrong too .. so, this is how I have my dialog set up and working.

MS-SQL fields: Latitude numeric(15,8) and Longitude numeric(15,8) in a table called suppliers

Dialog has 2 equivalent textbox numeric controls called latitude and longitude. Controls are bound to the SQL fields in data binding.

Dialog.jpg

In the ServerSide Events: afterDialogValidate calling the ExecuteServerSideAction("Save Data::Save Data") action script

SaveEvent.jpg

In the Action Script settings Geocode record:

ServerSideAction.jpg

I've set
address controls as Address, City, State, Country
table alias as suppliers
latitude control = Latitude
longitude control - Longitude
Not using location field

GeocodeRecord.jpg

That's it. This just works for me! Don't know if this helps you at all but hopefully you might see something.

Cheers,
Stephen

[iRadiate]

Re: error geocode longitude in dialog (decimal separator is missing?)

And here's the lat and long fields populated:

Alpha.jpg

[cptutrecht]

Re: error geocode longitude in dialog (decimal separator is missing?)

Thanks Stephen for your clear examples.
I followed your steps in my dialog with still the same error, see my screenshot below.

Only difference perhaps
+ my dialog is in a modal popup called from an actionbutton on a grid
+ maybe we define the lat / long controls in the dialog different? I define them as textbox / Numeric, You too? (can't see that in your pictures)



errorscreenlatlong.png)

I know the problem is not in the database: in the GRID there is the editfunction for the adresses. And i use the code below in the after update record to update the lat long fields, which works like a charm:

if (e.DataSubmitted.relaties_straat_postbus <> e.OldDataSubmitted.relaties_straat_postbus .OR. e.DataSubmitted.relaties_huis_postbus_num <> e.OldDataSubmitted.relaties_huis_postbus_num .OR. e.DataSubmitted.relaties_woonplaats <> e.OldDataSubmitted.relaties_woonplaats) then

dim pa as p
dim address as c

dim cn as sql::Connection
dim args as sql::Arguments
dim flag as l
dim sql as c

address = e.DataSubmitted.relaties_straat_postbus + " " + e.DataSubmitted.relaties_huis_postbus_num + " " + e.DataSubmitted.relaties_woonplaats
pa = geocode_address(address,"Google")


args.add("primaryKey",e.DataSubmitted.relaties_nummer)
args.add("lat",pa.lat)
args.add("lon",pa.lon)


' We need to set cn.PortableSQLEnabled = .t. else function GeogCreateLocation(:lon, :lat) in sql update will fail

cn.PortableSQLEnabled = .t.

flag = a5SQLConnectionOpen(cn,"::name::CPTDEMO", session)

if flag = .t. then
sql = "UPDATE RELATIES SET latitude = :lat, longitude = :lon, location = GeogCreateLocation(:lon, :lat) WHERE relaties_nummer = :primaryKey"

flag = cn.execute(sql,args)

cn.close()
end if
end if

[iRadiate]

Re: error geocode longitude in dialog (decimal separator is missing?)

Only difference perhaps
+ my dialog is in a modal popup called from an actionbutton on a grid

Me too, I'm calling a modal popup from an action button on my grid:

Modal-Popup.jpg

+ maybe we define the lat / long controls in the dialog different? I define them as textbox / Numeric, You too? (can't see that in your pictures)

Yes, mine are textbox / numeric also:

LatControl.jpg

This is very strange that it geocodes correctly for your lat but not long. I would almost be tempted to create a new dialog from scratch just because I don't see anything you've done wrong. Extra work but it might rule out a corrupt dialog. I am very interested in knowing what this might turn out to be.

I'm curious if you were to change the database type of your long field to INT or BIGINT if it would accept it (of course I know it's still wrong). Just thinking out loud.

Stephen