I want to retrieve the number of occurences (get count) in Column "PreparedBy" where the value is not empty ("").
To do so I build a Select Where Statement but it throws an error, because the datatable column can contain a string of nothing ("") or a string not equal to (""). But on initialization the column is empty (DBNull.Value), so it is empty but not ("").
I tried to solve it with an inner if (IIF) but as soon as I try ask something else than "ISDBNULL" it throws the error.
Dim iCountPrepared As Integer = dtLeft.Select().Where(Function(s) CType(s("Type"), String) = "Dep" And Not CType(IIf(IsDBNull(s("PreparedBy")), "", CType(s("PreparedBy"), String)), String) = "").Count()
It is not enough to check whether the field is DBNull.Value, it is important to check whether the value is not empty ("").
Any help appreciated.
The DataRow class has its own IsNull method to check for database NULLs, i.e. DBNull.Value, so you should be using that. This will give you the count of rows that are not NULL or Nothing in a particular column:
Dim count = table.AsEnumerable().Count(Function(dr) Not dr.IsNull("Column1") AndAlso
dr.Field(Of String)("Column1") <> String.Empty)
It's also worth noting that the DBNull.ToString method returns an empty string, so you could do this:
Dim count = table.AsEnumerable().Count(Function(dr) dr("Column1").ToString() <> String.Empty)