If we have decimal columns in a file that we need to export to SQL Server, there could be an error at the point of bulk insert if the user has entered varchar values for decimal columns.

  1. /*
  2. sbc.NotifyAfter = temp.Rows.Count;
  3. sbc.WriteToServer(temp); /*Here we can have error regarding type casting.*/
  4. sbc.Close();
  6. */

To avoid this error at the point of bulk insert, we can raise a validation error before the execution reaches the final point of bulk insert by either run for loop and check invalid values in decimal columns or by another step as below.

  1. string sql = " ((Convert([" + strCropColumn1 + "], 'System.Decimal')) * 0) = 0 ";
  3. string sql1 = " ((Convert([" + strCropColumn2 + "], 'System.Decimal')) * 0) = 0 ";
  5. try
  7. {
  9. DataRow[] drSql1 = dt.Select(sql);
  10. }catch (Exception)
  12. {
  14. strErrorMessage = "Invalid value in \"" + strCropColumn1 + "\"";
  15. return;
  17. }
  18. try
  20. {
  22. DataRow[] drSql1 = dt.Select(sql1);
  24. }
  26. catch (Exception)
  28. {
  30. strErrorMessage = "Invalid value in \"" + strCropColumn2 + "\"";
  32. return;
  34. }
Using the preceding , we have given a restricted execution before it reaches the final bulk insert statement as well as we can give a validation message regarding a false value to the decimal columns.

Happing coding.