How to insert null value for numeric field of a datatable in c#?

c# insert null numeric sqlbulkcopy

Question

Consider My dynamically generated datatable contains the following fileds Id,Name,Mob1,Mob2

If my datatable has this it get inserted successfully,

Id Name Mob1        Mob2
1  acp  9994564564  9568848526

But when it is like this it gets failed saying,

Id Name Mob1        Mob2
1  acp  9994564564  

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

I generating my datatable by readingt a csv file,

    CSVReader reader = new CSVReader(CSVFile.PostedFile.InputStream);
    string[] headers = reader.GetCSVLine();
    DataTable dt = new DataTable();
    foreach (string strHeader in headers)
    {
        dt.Columns.Add(strHeader);
    }
    string[] data;
    while ((data = reader.GetCSVLine()) != null)
    {
        dt.Rows.Add(data);
    }

Any suggestion how to insert null value for numeric field during BulkCopy in c#...

EDIT:

I tried this dt.Columns["Mob2"].AllowDBNull = true; but it doesn't seem to work...

Accepted Answer

I got it working by assigning a null value to it....

if(dr["Mob2"] == "")
  dr["Mob2"] =null;

Popular Answer

Are you sure the error doesn't occur somewhere else in your application? How dose your DataColumn even know that it contains decimal values and not strings, you do not define its DataType.

Anyway the error says that the problem is a string - decimal conversion (not an is-null issue), so I think you receive an emty string "" rather than null when you read the second line. Try to manully set the fields of your data array to null if they contain empty strings before you call Rows.Add



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why