“Exception thrown: 'System.FormatException' in Microsoft.SqlServer.Types.dll ” in ASP.NET console application C#

Question

I am trying to insert a Sqlgeography column from latitude, longitude points to SQL server 2016 but I am getting

Exception thrown: 'System.FormatException' in Microsoft.SqlServer.Types.dll

I have already installed the Microsoft System CLR Types for SQL Server 2016 on the machine. What is the correct approach to resolve this issue.

SqlCommand addpoint = new SqlCommand("INSERT INTO " + Helper.TABLE_ALLINDEXPOINTS + "values (@id, @latitude, @longitude, @streetname, @longlat", sqlConnection);
addpoint.Parameters.AddWithValue("@id", id);
addpoint.Parameters.AddWithValue("@latitude",Convert.ToDouble(snappedPoints[j]["latitude"]));
addpoint.Parameters.AddWithValue("@longitude",Convert.ToDouble(snappedPoints[j]["longitude"]));
addpoint.Parameters.AddWithValue("@streetname", snappedPoints[j]["name"].ToString());
addpoint.Parameters.AddWithValue("@longlat", SqlGeography.STGeomFromText(new SqlChars(string.Format("POINT({0} {1})", snappedPoints[j]["latitude"], snappedPoints[j]["longitude"])), Helper.SRID));
addpoint.ExecuteNonQuery();

Error:

'ConsoleApp2.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Users\ssindhu\source\repos\ConsoleApp2\ConsoleApp2\bin\Debug\ConsoleApp2.exe'. Symbols loaded. 'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Net.Http\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Net.Http.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Users\ssindhu\source\repos\ConsoleApp2\ConsoleApp2\bin\Debug\Newtonsoft.Json.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'. Cannot find or open the PDB file.
'ConsoleApp2.exe' (CLR v4.0.30319: ConsoleApp2.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll'. Cannot find or open the PDB file.

This is the table schema:

create table all_index_points
(
    id varchar(450) not null primary key,
    latitude float,
    longitude float,
    streetname nvarchar(max),
    longlat geography
);

Please include the details of the exception - in particular which method is throwing it (I'd expect STGeomFromText or the SqlChars constructor) and the error message. — Jon Skeet
have you tried adding space before Values word? like " values not "values — maulik kansara
@Sree error indicates that you have enabled the option "Just My Code" (Tools -> Options -> Debugging -> General). This will skip loading symbol files. disable it and try. — maulik kansara
And in particular, that still doesn't contain any of the details of the exception. Catch the exception, or look at it in the debugger, and you should see the stack trace and the method. All the output about loading assemblies isn't useful - but the exception details would be. — Jon Skeet

Tayyab Tayyab · Accepted Answer · 2019-04-30T06:27:23

Following are some of the things that needed correction in your code:

SqlCommand addpoint = new SqlCommand("INSERT INTO " + Helper.TABLE_ALLINDEXPOINTS + " values (@id, @latitude, @longitude, @streetname, @longlat)", sqlConnection);

Note the space before values. Also missing a closing bracket.

addpoint.Parameters.AddWithValue("@id", id);
addpoint.Parameters.AddWithValue("@latitude",Convert.ToDouble(snappedPoints[j]["latitude"]));
addpoint.Parameters.AddWithValue("@longitude",Convert.ToDouble(snappedPoints[j]["longitude"]));
addpoint.Parameters.AddWithValue("@streetname", snappedPoints[j]["name"].ToString());
addpoint.Parameters.AddWithValue("@longlat", SqlGeography.STGeomFromText(new SqlChars(string.Format("POINT({0} {1})", snappedPoints[j]["latitude"], snappedPoints[j]["longitude"])), Helper.SRID));
addpoint.ExecuteNonQuery();

Also do share the details of your exception along with the possible datatypes of your table fields in case the above does not solve your problem.

EDIT

Beside all the above mentioned problems the actual problem that you are facing will be solved by appending the following line before you call ExecuteQuery:

addpoint.Parameters[4].UdtTypeName = "Geography";

As SQL Server requires you to explicitly set UdtTypeName for parameter of type SqlGeography (and some other types as well, which I am not mentioning here).

So your updated code should look something like:

addpoint.Parameters.AddWithValue("@id", id);
addpoint.Parameters.AddWithValue("@latitude",Convert.ToDouble(snappedPoints[j]["latitude"]));
addpoint.Parameters.AddWithValue("@longitude",Convert.ToDouble(snappedPoints[j]["longitude"]));
addpoint.Parameters.AddWithValue("@streetname", snappedPoints[j]["name"].ToString());
addpoint.Parameters.AddWithValue("@longlat", SqlGeography.STGeomFromText(new SqlChars(string.Format("POINT({0} {1})", snappedPoints[j]["latitude"], snappedPoints[j]["longitude"])), Helper.SRID));
addpoint.Parameters[4].UdtTypeName = "Geography";
addpoint.ExecuteNonQuery();

Hope this solves all your problems.

Yet another side note is to use Helper.SRID = 4326 for the provided string format.

“Exception thrown: 'System.FormatException' in Microsoft.SqlServer.Types.dll ” in ASP.NET console application C#

1 Answers