I would like to replace all null values with DBnull in a data table

Help Activities
1

Dear Experts,

I would like to replace all null values from a data table as DBnull.value the following link was to just to replace only one column. I used to write with different column names, however this is not possible since the number of columns more that 25 columns and it becomes a challenge, so appreciate your support with an extension of the link below.

Continuing the discussion from I am not able to insert records into the database where date column is null:

Appreciate your feedback/support.

Regards,
Manjesh

2

@manjesh_kumar

You can follow below steps

  1. For each row in datatable on the main dt
  2. For loop activity and give in argument as Enumerable.Range(0,dt.Columns.count).ToArray and change type argument to integer
  3. Now use the if condition inside the loop isNothing(currentrow(currentitem) OrElse String.IsNullOrEmpty(currentrow(currentitem).toString.Trim)
  4. On the then side you can replace with dbNull.value

Hope this helps

Cheers

3

@Anil_G

can this be enhance on this code would be much easier.

in_dt_GrBlankAgeingDT.AsEnumerable.ToList.ForEach(Sub(row)
If(IsNothing(row(“GRN_DATE”)) Or (String.IsNullOrEmpty(row(“GRN_DATE”).tostring.Trim))) Then
row(“GRN_DATE”) = DBNull.Value
End If
End Sub
)

Regards,
Manjesh

4

@manjesh_kumar

Here it is

in_dt_GrBlankAgeingDT.AsEnumerable.ToList.ForEach(Sub(row)
For Each d In Enumerable.Range(0,in_dt_GrBlankAgeingdt.Columns.Count)
If(IsNothing(row(d)) Or (String.IsNullOrEmpty(row(d).tostring.Trim))) Then
row(d) = DBNull.Value
End If
Next
End Sub
)

Hope you know how to use in invoke code

cheers

5

similar to:

dtCorrected = OriginDataTableVar.Clone()

dtCorrected =

(From r In dtData.AsEnumerable
let ra = r.ItemArray.Select(Function (x) If(isNothing(x) OrElse String.IsNullOrEmpty(x.ToString.Trim), DBNull.Value, x)).toArray
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()
2 Likes
6

Dear @ppr,

Sorry for delayed response, this solves my issue and thank you very much.

@Anil_G Thank you very much for pitching in

Regards,
Manjesh

1 Like
7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.