none
Sqlbulkcopy Error : The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified RRS feed

  • General discussion

  •  

    Hi,

     

    The  table in SQL has column  Availability   Decimal (8,8) 

     

    Code in c#  using sqlbulkcopy trying to insert values  like  0.0000,  0.9999, 29.999  into the field  Availability

    we tried the datatype float , but it is converting values to scientific expressions…(eg: 8E-05) and the values displayed in reports are  scientifc expressions which is not expected

    we need to store values as is

     

     

    Error:

    base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}

     

    "System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range.\r\n   --- End of inner exception stack trace ---\r\n   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)\r\n   --- End of inner exception stack trace ---\r\n   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)\r\n   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()\r\n   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)\r\n   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)\r\n   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)\r\n   at MS.Internal.MS

    COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\\VSTS\\MXPS Shared Services\\RealTimeMonitoring\\AggregateRealTimeDataToSQL\\SQLHelper.cs:line 269"

     

     

    Code in C#

     

    SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default);

                DataRow dr;

                DataTable dt = new DataTable();

                DataColumn dc;

     

             try

             {

               

    dc = dt.Columns.Add("Availability", typeof(decimal));

    ….

     

               dr["Availability"] = Convert.ToDecimal(s[2]);   ------ I tried SqlDecimal

    ……….

     

                }

                bulkCopy.DestinationTableName = "dbo.[Tbl_Fact_App_Server_AvailPerMinute]";

                bulkCopy.WriteToServer(dt);

     

        

     

    thx

     

     

     

    Wednesday, April 16, 2008 4:20 AM

All replies

  • Hi

     

    what is the specification of the datatype scale & precision in sql server?

    also, what is the collation in sql server?

    can it be it sees the decimal not as a decimal but as a string/integer (ie . or , for decimals)

    what is the value of the Convert.ToDecimal?

     

    HTH

     

     

     

    Thursday, April 17, 2008 12:31 PM