英文:
vb.net DataTable Select Where Statement throws error (DBNull Value)
问题
我想要检索在“PreparedBy”列中值不为空（“”）的出现次数（获取计数）。
为此，我构建了一个选择条件语句，但它抛出了一个错误，因为数据表列可以包含一个空字符串（“”）或一个不等于空字符串（“”）的字符串。但在初始化时，该列为空（DBNull.Value），因此它是空的但不是（“”）。
我尝试使用内部的if（IIF）来解决这个问题，但一旦我尝试询问与“ISDBNULL”之外的内容时，它就会抛出错误。
以下是代码示例：
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()
仅需检查字段是否为DBNull.Value是不够的，重要的是要检查该值是否不为空（“”）。
希望对你有所帮助。
英文:
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.
答案1
得分: 3
The DataRow 类有它自己的 IsNull 方法来检查数据库中的 NULL，即 DBNull.Value，所以你应该使用它。这将给你在特定列中不是 NULL 或 Nothing 的行数：
Dim count = table.AsEnumerable().Count(Function(dr) Not dr.IsNull("Column1") AndAlso
dr.Field(Of String)("Column1") <> String.Empty)
值得注意的是，DBNull.ToString 方法返回一个空字符串，所以你也可以这样做：
Dim count = table.AsEnumerable().Count(Function(dr) dr("Column1").ToString() <> String.Empty)
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论