This is related to the Google Maps web part from an earlier post. I have latitude and longitude columns in a database table. Both are Float datatypes. I needed to build a URL string for the push-pin description box. That would seem straight-forward enough, however, I was receiving an error on converting a float value to a varchar. Below is my first take on the SQL to create my location list. This location list is connected to Sharepoint as an external content type and external BCS list.
INSERT INTO LocationsPins (Title, Description, Latitude, Longitude) Select Address as Title, ('URL: <a href="/sandbox/lists/locationdata/Read%20List.aspx?Lat=' + Latitude + '">Location Detail</a>') AS Description, Latitude, Longitude
From LocationData Where Latitude IS NOT NULL and Latitude > 0 AND Longitude IS NOT NULL AND Address IS NOT NULL AND City is NOT NULL AND State is NOT NULL AND Zip is NOT NULL GROUP BY Address, Latitude, Longitude
That by itself threw the error regarding converting a float to varchar. Using Convert or Cast was not getting what I needed as Latitude can be 6 or more decimal places and those functions would add trailing Zeroes.
So, I had to create a Function that would take this into account.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[FloatConvert] (@ParmValue Float) RETURNS VARCHAR(25) AS BEGIN DECLARE @RetValue VARCHAR(25) SET @RetValue = CAST(CAST(@ParmValue AS DECIMAL(15,7)) AS VARCHAR(20)) SET @RetValue = REVERSE(@RetValue) WHILE SUBSTRING(@RetValue,1,1) = 0 BEGIN SET @RetValue = SUBSTRING(@RetValue,2,25) END SET @RetValue = REVERSE(@RetValue) RETURN @RetValue END
The SQL query (description field) to build the list looks like this;
Select Address as Title, (‘URL: <a href=”/sandbox/lists/locationdata/Read%20List.aspx?Lat=’+ dbo.FloatConvert(Latitude) + ‘”>Location Detail</a>’) AS Description, Latitude, Longitude
Hope this helps someone as I’ve seen postings regarding this conversion during some Google searches.