Importing Shp file - Failed to convert parameter value from a String to a Double.

Topics: Developer Forum, User Forum
Apr 1, 2009 at 10:21 PM
The shape file "STEVENGONG_ROADS.shp" has been imported couple of times by the released version 0.1.1 of msscmd successfully. Then I have rebuilt the solution of the source code version 48758, and deployed the database using the permission UNSAFE. It created the spatial functions and stored procedures successfully on the testing database.

However running of ImportFromShapefile got the following error:

Input 1(Use SQL Server Management Studio):
EXECUTE ST.ImportFromShapefile 'E:\...\STEVENGONG_ROADS.shp', '', 'ROADS', 'column', -1

Input 2(Use MS DOS Command Line):
msscmd -server="STEVEN\SQLEXPRESS" -db="MsSqlSpatial" -table="ROADS" -import=shp -shp_filename="E:\...\STEVENGONG_ROADS.shp"


By the way, why we need to specify 'column' in input 1, but not in input 2?
Which column does it refer to?

Output 1 and 2:
Msg 6522, Level 16, State 1, Procedure ImportFromShapefile, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "ImportFromShapefile":
System.FormatException: Failed to convert parameter value from a String to a Double. ---> System.FormatException: Input string was not in a correct format.
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
   at System.Double.Parse(String s, NumberStyles style, NumberFormatInfo info)
   at System.Convert.ToDouble(String value, IFormatProvider provider)
   at System.String.System.IConvertible.ToDouble(I
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
   at System.Data.SqlClient.SqlParameter.GetCoercedValue()
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.SetUpSmiRequest(SqlInternalConnectionSmi innerConnection)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at StoredProcedures.ShapefileImportProcess.Execute(SqlCommand command, String columnName, Int32 srid, Int64& importedRows)
   at StoredProcedures.DataImportProcess(IDataImportProcess process, String schemaName, String tableName, String columnName, Int32 srid, String type, String connectionString, Boolean appendRows, Int64& importedRows, TimeSpan& elapsedTime)
   at StoredProcedures.ImportFromShapefileMain(String shapefile, String schema_name, String table_name, String column_name, Int32 srid, String type, String connectionString, Boolean appendRows, Int64& importedRows, TimeSpan& elapsedTime)
   at StoredProcedures.ImportFromShapefile(SqlString shapefile, SqlString ...


Apr 2, 2009 at 9:50 AM
Hi Steven, The column refers to the name of the geometry column to be created in the db, It doesn't need to be specified in the msscmd because it is supplied with a default value from the settings file if not present as an argument  (-column=name)
Re the errors: I am guessing that there is a column of type double in the dbf but the actual content for a record is either an empty string or the word Null (or some other non numeric value) and this is being incorrectly converted / coerced.
Can you check the dbf with another tool?
cheers jd
Apr 3, 2009 at 3:35 AM
Thanks John,
I've fixed my problem by investigating the .dbf file, and found one of the field have null values, which is defined as double from the .shp file.

To use '0' as the default value for this invalid column, I did the following:

1.Manually open the .dbf file in Excel
2.On the right of the invalid column, insert a new column
3.Set the value cell formula to be =if([leftcell eg. A2]>0, [leftcell eg. A2],0)
4.Apply this formula to autofill all the cells in this new column
5.Copy Special, only the values of this column to the invalid column
6.Delete the new column
7.Save and close the dbf file.

Too many steps, and it's not capable for large amount of data.
Is there any other solution or tools can do this auto assignment easier?

Cheers sg
Apr 3, 2009 at 9:52 AM
Although a bit hacky you may be able to do something by editing the ImportFromShapefile.cs code around line 267.
You could probably check for the strings "NULL" and "" and change the course of action accordingly to send the value DBNull.Value however this may cause issues if any columns are not nullable.
Otherwise I think you will have to experiment with other dbf editors.. hth jd