SQlBulkCopy throws a System.InvalidOperationException.

c# invalidoperationexception sqlbulkcopy


I got the following error when executing bulkcopy.

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);
    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.

5/31/2010 11:50:16 PM

Accepted Answer

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;
  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.

1/8/2010 11:17:19 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow