System.InvalidOperationException with SQlBulkCopy

Question

I got the following error when executing bulkcopy.

System.InvalidOperationException
The given value of type String from the data source cannot be converted to 
type decimal of the specified target column.

I use the following code.

DataTable empTable = DataTemplate.GetEmployees();
    DataRow row;
    for (int i = 0; i < gv.Rows.Count;i++ )
    {
        row = empTable.NewRow();
        string empName = gv.DataKeys[i].Values[0].ToString(); //first key
        string hourSalary = gv.DataKeys[i].Values[1].ToString(); //second key
        row["Emp_Name"] = empName;
        row["Hour_Salary"] = Convert.ToDecimal(hourSalary);
        row["Advance_amount"] = Convert.ToDecimal(0);
        row["Created_Date"] = Convert.ToDateTime(System.DateTime.Now.ToString());
        row["Created_By"] = Convert.ToInt64(1);
        row["Is_Deleted"] = Convert.ToInt64(0);
        empTable.Rows.Add(row);
    }
    InsertintoEmployees(empTable, "Employee");

My SQL datatypes for the above fields are:

Emp_Name nvarchar(50) ,
Hour_Salary numeric(18, 2), 
Advance_amount numeric(18, 2), 
Created_Date datetime,
Created_By numeric(18, 0), 
Is_Deleted numeric(18, 0) 

I don't know what I am doing wrong.

Answer 1

Your hourSalary variable is a string that you later try to convert to a Decimal and this fails.

Most probably you have either an empty string value or an invalid value.

Depending on your requirements you must decide what to do with the invalid value. You basically have two options except failing: Store as a default value (0 maybe?) or store as a Null value.

If you want to store a default value then you can try the following when converting the salary:

string hourSalary = gv.DataKeys[i].Values[1].ToString();
double salary = 0;
if (!double.TryParse(hourSalary, out salary))
{
  salary = 0; // Set your default value here
}
row["Hour_Salary"] = salary;

This will ensure that you get a valid decimal value stored in your Hour_Salary column.

If you want to store a null value then you must change the code somewhat:

string hourSalary = gv.DataKeys[i].Values[1].ToString();
double salary;
object salaryValue;
if (double.TryParse(hourSalary, out salary))
{
   salaryValue = salary;
}
else
{
  salaryValue = DBNull.Value; // Store as a null
}
row["Hour_Salary"] = salaryValue;

In this example we do the same TryParse, but if it fails we store DBNull.Value instead of the default 0 value.