在 vb.net 中处理 dbnull 数据

发布于 2024-07-07 20:18:55 字数 837 浏览 12 评论 0原文

我想生成从 MS-Access 数据库检索并存储在 DataTable 对象/变量 myDataTable 中的数据的一些格式化输出。但是，myDataTable 中的某些字段包含 dbNull 数据。因此，如果 lastname、intials 或 sID 任何字段的值为 sID，则以下 VB.net 代码片段将给出错误>dbNull。

   dim myDataTable as DataTable
   dim tmpStr as String
   dim sID as Integer = 1

   ...
   myDataTable = myTableAdapter.GetData() ' Reads the data from MS-Access table
   ...

   For Each myItem As DataRow In myDataTable.Rows

    tmpStr = nameItem("lastname") + " " + nameItem("initials")

    If myItem("sID")=sID Then
        ' Do something
    End If

    ' print tmpStr

   Next

那么，当字段可能包含 dbNull 时，如何使上述代码正常工作，而不必每次都检查数据是否为 dbNull，如这个问题？

原文

I want to generate some formatted output of data retrieved from an MS-Access database and stored in a DataTable object/variable, myDataTable. However, some of the fields in myDataTable cotain dbNull data. So, the following VB.net code snippet will give errors if the value of any of the fields lastname, intials, or sID is dbNull.

   dim myDataTable as DataTable
   dim tmpStr as String
   dim sID as Integer = 1

   ...
   myDataTable = myTableAdapter.GetData() ' Reads the data from MS-Access table
   ...

   For Each myItem As DataRow In myDataTable.Rows

    tmpStr = nameItem("lastname") + " " + nameItem("initials")

    If myItem("sID")=sID Then
        ' Do something
    End If

    ' print tmpStr

   Next

So, how do i get the above code to work when the fields may contain dbNull without having to check each time if the data is dbNull as in this question?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

[旋木] 2024-07-14 20:18:55

我知道的唯一方法是对其进行测试，您可以进行组合，以使其变得容易。

If NOT IsDbNull(myItem("sID")) AndAlso myItem("sID") = sId Then
   'Do success
ELSE
   'Failure
End If

我用 VB 编写，因为这看起来就是您所需要的，即使您混合了多种语言。

编辑

清理以使用 IsDbNull 使其更具可读性

The only way that i know of is to test for it, you can do a combined if though to make it easy.

If NOT IsDbNull(myItem("sID")) AndAlso myItem("sID") = sId Then
   'Do success
ELSE
   'Failure
End If

I wrote in VB as that is what it looks like you need, even though you mixed languages.

Edit

Cleaned up to use IsDbNull to make it more readable

回复收藏 0 原文

吃兔兔 2024-07-14 20:18:55

我厌倦了处理这个问题，所以我编写了一个 NotNull() 函数来帮助我解决这个问题。

Public Shared Function NotNull(Of T)(ByVal Value As T, ByVal DefaultValue As T) As T
        If Value Is Nothing OrElse IsDBNull(Value) Then
                Return DefaultValue
        Else
                Return Value
        End If
End Function

用法：

If NotNull(myItem("sID"), "") = sID Then
  ' Do something
End If

我的 NotNull() 函数多年来经历了几次大修。在使用泛型之前，我只是将所有内容指定为对象。但我更喜欢通用版本。

I got tired of dealing with this problem so I wrote a NotNull() function to help me out.

Public Shared Function NotNull(Of T)(ByVal Value As T, ByVal DefaultValue As T) As T
        If Value Is Nothing OrElse IsDBNull(Value) Then
                Return DefaultValue
        Else
                Return Value
        End If
End Function

Usage:

If NotNull(myItem("sID"), "") = sID Then
  ' Do something
End If

My NotNull() function has gone through a couple of overhauls over the years. Prior to Generics, I simply specified everything as an Object. But I much prefer the Generic version.

回复收藏 0 原文

在风中等你 2024-07-14 20:18:55

您还可以使用 Convert.ToString() 和 Convert.ToInteger() 方法有效地转换 DB null 的项目。

回复收藏 0 原文

比忠 2024-07-14 20:18:55

Steve Wortham 的代码的变体，名义上与 nullable 类型一起使用：

Private Shared Function GetNullable(Of T)(dataobj As Object) As T
    If Convert.IsDBNull(dataobj) Then
        Return Nothing
    Else
        Return CType(dataobj, T)
    End If
End Function

例如，

mynullable = GetNullable(Of Integer?)(myobj)

您可以查询mynullable（例如，mynullable.HasValue）

A variation on Steve Wortham's code, to be used nominally with nullable types:

Private Shared Function GetNullable(Of T)(dataobj As Object) As T
    If Convert.IsDBNull(dataobj) Then
        Return Nothing
    Else
        Return CType(dataobj, T)
    End If
End Function

e.g.

mynullable = GetNullable(Of Integer?)(myobj)

You can then query mynullable (e.g., mynullable.HasValue)

回复收藏 0 原文

瞳孔里扚悲伤 2024-07-14 20:18:55

微软在.NET 1.0中提出了DBNull来表示数据库NULL。但是，使用起来很麻烦，因为您无法创建强类型变量来存储真实值或 null。 Microsoft 在 .NET 2.0 中使用可空类型解决了这个问题。但是，您仍然受困于使用 DBNull 的大量 API，并且无法更改它们。

只是一个建议，但我通常做的是：

所有包含从数据库读取或写入数据库的数据的变量都应该能够处理空值。对于值类型，这意味着将它们设置为 Nullable(Of T)。对于引用类型（String 和 Byte()），这意味着允许值为 Nothing。
编写一组函数以在“可能包含 DBNull 的对象”和“可空 .NET 变量”之间来回转换。将所有对 DBNull 样式 API 的调用包装在这些函数中，然后假装 DBNull 不存在。

回复收藏 0 原文

心奴独伤 2024-07-14 20:18:55

您可以使用 IsDbNull 函数：

  If  IsDbNull(myItem("sID")) = False AndAlso myItem("sID")==sID Then
    // Do something
End If

You can use the IsDbNull function:

  If  IsDbNull(myItem("sID")) = False AndAlso myItem("sID")==sID Then
    // Do something
End If

回复收藏 0 原文

已下线请稍等 2024-07-14 20:18:55

如果您使用 BLL/DAL 设置，请在读入 DAL 中的对象时尝试 iif

While reader.Read()
 colDropdownListNames.Add(New DDLItem( _
 CType(reader("rid"), Integer), _
 CType(reader("Item_Status"), String), _
 CType(reader("Text_Show"), String), _
 CType( IIf(IsDBNull(reader("Text_Use")), "", reader("Text_Use")) , String), _
 CType(reader("Text_SystemOnly"), String), _
 CType(reader("Parent_rid"), Integer)))
End While

If you are using a BLL/DAL setup try the iif when reading into the object in the DAL

While reader.Read()
 colDropdownListNames.Add(New DDLItem( _
 CType(reader("rid"), Integer), _
 CType(reader("Item_Status"), String), _
 CType(reader("Text_Show"), String), _
 CType( IIf(IsDBNull(reader("Text_Use")), "", reader("Text_Use")) , String), _
 CType(reader("Text_SystemOnly"), String), _
 CType(reader("Parent_rid"), Integer)))
End While

回复收藏 0 原文

梦幻之岛 2024-07-14 20:18:55

对于包含字符串的行，我可以将它们转换为字符串，就像更改

tmpStr = nameItem("lastname") + " " + nameItem("initials")

为

tmpStr = myItem("lastname").toString + " " + myItem("intials").toString

对于 if 语句 myItem("sID")=sID 中的比较，它需要是更改为

myItem("sID").Equals(sID)

然后，代码将运行，不会因 vbNull 数据而出现任何运行时错误。

For the rows containing strings, I can convert them to strings as in changing

tmpStr = nameItem("lastname") + " " + nameItem("initials")

tmpStr = myItem("lastname").toString + " " + myItem("intials").toString

For the comparison in the if statement myItem("sID")=sID, it needs to be change to

myItem("sID").Equals(sID)

Then the code will run without any runtime errors due to vbNull data.

回复收藏 0 原文

十年不长 2024-07-14 20:18:55

   VB.Net
   ========
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Call conecDB()        'Connection to Database
    da.SelectCommand = New SqlCommand("select max(RefNo) from BaseData", connDB)

    da.Fill(dt)

    If dt.Rows.Count > 0 And Convert.ToString(dt.Rows(0).Item(0)) = "" Then
        MsgBox("datbase is null")

    ElseIf dt.Rows.Count > 0 And Convert.ToString(dt.Rows(0).Item(0)) <> "" Then
        MsgBox("datbase have value")

    End If

   VB.Net
   ========
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Call conecDB()        'Connection to Database
    da.SelectCommand = New SqlCommand("select max(RefNo) from BaseData", connDB)

    da.Fill(dt)

    If dt.Rows.Count > 0 And Convert.ToString(dt.Rows(0).Item(0)) = "" Then
        MsgBox("datbase is null")

    ElseIf dt.Rows.Count > 0 And Convert.ToString(dt.Rows(0).Item(0)) <> "" Then
        MsgBox("datbase have value")

    End If

回复收藏 0 原文

恋你朝朝暮暮 2024-07-14 20:18:55

我认为这应该更容易使用：

从表名中选择 ISNULL(sum(field),0)

复制自： http://www.codeproject.com/Questions/736515/How-do-I-avoide-Conversion-from-type-DBNull-to-typ

回复收藏 0 原文

撩动你心 2024-07-14 20:18:55

Hello Friends

这是检查 DataGrid 中的 db Null 并转换为字符串的最短方法

创建单元格验证事件并编写此代码
If Convert.ToString(dgv.CurrentCell.Value ) = "" then
CurrentCell.Value = ""
End If

回复收藏 0 原文

深海蓝天 2024-07-14 20:18:55

这是迄今为止将DBNull 转换为字符串的最简单方法。
诀窍是，在引用数据库中的字段时，您不能使用TRIM函数（这是我最初的问题）：

BEFORE（生成错误消息）：

Me.txtProvNum.Text = IIf(Convert.IsDBNull(TRIM(myReader("Prov_Num"))), "", TRIM(myReader("Prov_Num")))

之后（不再有错误消息:-)）：

Me.txtProvNum.Text = IIf(Convert.IsDBNull(myReader("Prov_Num")), "", myReader("Prov_Num"))

This is BY FAR the easiest way to convert DBNull to a string.
The trick is that you CANNOT use the TRIM function (which was my initial problem) when referring to the fields from the database:

BEFORE (produced error msg):

Me.txtProvNum.Text = IIf(Convert.IsDBNull(TRIM(myReader("Prov_Num"))), "", TRIM(myReader("Prov_Num")))

AFTER (no more error msg :-) ):

Me.txtProvNum.Text = IIf(Convert.IsDBNull(myReader("Prov_Num")), "", myReader("Prov_Num"))

回复收藏 0 原文

请远离我 2024-07-14 20:18:55

简单，但不明显。

DbNull.Value.Equals(myValue)

我讨厌VB.NET

Simple, but not obvious.

DbNull.Value.Equals(myValue)

I hate VB.NET

回复收藏 0 原文

白龙吟 2024-07-14 20:18:55

对于您的问题，您可以使用以下仅存在于 VB.Net 中的特殊解决方法编码。

Dim nId As Integer = dr("id") + "0"

此代码将用整数 0 替换 id 列中包含的 DBNull 值。

唯一可接受的默认值是“0”，因为当 dr( “id”） 不为 NULL！

因此，使用这种技术，您的代码将是

   Dim myDataTable as DataTable
   Dim s as String
   Dim sID as Integer = 1

   ...
   myDataTable = myTableAdapter.GetData() ' Reads the data from MS-Access table
   ...

   For Each myItem As DataRow In myDataTable.Rows
       s = nameItem("lastname") + " " + nameItem("initials")
       If myItem("sID") + "0" = sID Then
           ' Do something
       End If
   Next

我已经测试了此解决方案，并且它可以在我的 PC 上的 Visual Studio 2022 上运行。

PS：如果 sID 可以等于 0 并且您想要在以下情况下执行不同的操作dr("sID") 值为 NULL，您还必须熟练编程，并且可能使用本答案末尾建议的Extension。

我已经测试了以下语句

Dim iNo1 As Integer = dr("numero") + "0"
Dim iNo2 As Integer = dr("numero") & "0" '-> iNo = 10 when dr() = 1
Dim iNo3 As Integer = dr("numero") + "4" '-> iNo = 5  when dr() = 1
Dim iNo4 As Integer = dr("numero") & "4" '-> iNo = 14 when dr() = 1
Dim iNo5 As Integer = dr("numero") + "" -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo6 As Integer = dr("numero") & "" -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo7 As Integer = "" + dr("numero") -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo8 As Integer = "" & dr("numero") -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo9 As Integer = "0" + dr("numero")
Dim iNo0 As Integer = "0" & dr("numero")

以下语句也可以正常工作

Dim iNo9 As Integer = "0" + dr("numero")
Dim iNo0 As Integer = "0" & dr("numero")

我认识到这有点棘手。

如果您不喜欢技巧，您还可以定义一个扩展，以便以下代码可以工作。

Dim iNo = dr.GetInteger("numero",0)

其中 GetInteger() 代码可以跟随

Module Extension
    '***********************************************************************
    '* GetString()
    '***********************************************************************

    <Extension()>
    Public Function GetString(ByRef rd As SqlDataReader, ByRef sName As String, Optional ByVal sDefault As String = "") As String
        Return GetString(rd, rd.GetOrdinal(sName), sDefault)
    End Function

    <Extension()>
    Public Function GetString(ByRef rd As SqlDataReader, ByVal iCol As Integer, Optional ByVal sDefault As String = "") As String
        If rd.IsDBNull(iCol) Then
            Return sDefault
        Else
            Return rd.Item(iCol).ToString()
        End If
    End Function

    '***********************************************************************
    '* GetInteger()
    '***********************************************************************

    <Extension()>
    Public Function GetInteger(ByRef rd As SqlDataReader, ByRef sName As String, Optional ByVal iDefault As Integer = -1) As Integer
        Return GetInteger(rd, rd.GetOrdinal(sName), iDefault)
    End Function

    <Extension()>
    Public Function GetInteger(ByRef rd As SqlDataReader, ByVal iCol As Integer, Optional ByVal iDefault As Integer = -1) As Integer
        If rd.IsDBNull(iCol) Then
            Return iDefault
        Else
            Return rd.Item(iCol)
        End If
    End Function

End Module

这些方法更明确且不那么棘手。

此外，还可以定义除 ZERO 以外的默认值，也可以定义特定版本为 GetBoolean() 或 GetDate() 等...

另一种可能性是报告 SQL SQL 命令中的默认转换使用 COALESCE SQL 命令！

For your problem, you can use following special workaround coding that only exists in VB.Net.

Dim nId As Integer = dr("id") + "0"

This code will replace DBNull value contained in id column by integer 0.

The only acceptable default value is "0" because this expression must also be used when dr("id") is not NULL !

So, using this technic, your code would be

   Dim myDataTable as DataTable
   Dim s as String
   Dim sID as Integer = 1

   ...
   myDataTable = myTableAdapter.GetData() ' Reads the data from MS-Access table
   ...

   For Each myItem As DataRow In myDataTable.Rows
       s = nameItem("lastname") + " " + nameItem("initials")
       If myItem("sID") + "0" = sID Then
           ' Do something
       End If
   Next

I have tested this solution and it works on my PC on Visual Studio 2022.

PS: if sID can be equal to 0 and you want to do something distinct when dr("sID") value is NULL, you must also adept you program and perhaps use Extension as proposed at end of this answer.

I have tested following statements

Dim iNo1 As Integer = dr("numero") + "0"
Dim iNo2 As Integer = dr("numero") & "0" '-> iNo = 10 when dr() = 1
Dim iNo3 As Integer = dr("numero") + "4" '-> iNo = 5  when dr() = 1
Dim iNo4 As Integer = dr("numero") & "4" '-> iNo = 14 when dr() = 1
Dim iNo5 As Integer = dr("numero") + "" -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo6 As Integer = dr("numero") & "" -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo7 As Integer = "" + dr("numero") -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo8 As Integer = "" & dr("numero") -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo9 As Integer = "0" + dr("numero")
Dim iNo0 As Integer = "0" & dr("numero")

Following statements works also correctly

Dim iNo9 As Integer = "0" + dr("numero")
Dim iNo0 As Integer = "0" & dr("numero")

I recognize that is a little tricky.

If trick are not your tips, you can also define an Extension so that following code works.

Dim iNo = dr.GetInteger("numero",0)

where GetInteger() code can be following

Module Extension
    '***********************************************************************
    '* GetString()
    '***********************************************************************

    <Extension()>
    Public Function GetString(ByRef rd As SqlDataReader, ByRef sName As String, Optional ByVal sDefault As String = "") As String
        Return GetString(rd, rd.GetOrdinal(sName), sDefault)
    End Function

    <Extension()>
    Public Function GetString(ByRef rd As SqlDataReader, ByVal iCol As Integer, Optional ByVal sDefault As String = "") As String
        If rd.IsDBNull(iCol) Then
            Return sDefault
        Else
            Return rd.Item(iCol).ToString()
        End If
    End Function

    '***********************************************************************
    '* GetInteger()
    '***********************************************************************

    <Extension()>
    Public Function GetInteger(ByRef rd As SqlDataReader, ByRef sName As String, Optional ByVal iDefault As Integer = -1) As Integer
        Return GetInteger(rd, rd.GetOrdinal(sName), iDefault)
    End Function

    <Extension()>
    Public Function GetInteger(ByRef rd As SqlDataReader, ByVal iCol As Integer, Optional ByVal iDefault As Integer = -1) As Integer
        If rd.IsDBNull(iCol) Then
            Return iDefault
        Else
            Return rd.Item(iCol)
        End If
    End Function

End Module

These methods are more explicitely and less tricky.

In addition, it is possible to define default values other than ZERO and also specific version as GetBoolean() or GetDate(), etc ...

Another possibility is to report SQL default conversion in SQL command using COALESCE SQL command !

回复收藏 0 原文

~没有更多了~