解决GetStudentResultTotal函数DBNull转Double无效的空值处理问题

问题描述 #

我编写的GetStudentResultTotal函数用于对StudentData.tblStudentResult表的Total列求和并返回结果，但首次插入记录时，若查询返回Null值，会抛出‘Conversion from type dbnull to type double is not valid’错误，请问该如何控制空值？

相关代码 #

Function 1: GetStudentResultTotal

Function GetStudentResultTotal(ByVal schid As String, ByVal level As String, ByVal session As String, ByVal term As String, ByVal klass As String, ByVal regno As String) As Double
        If SCH_ID <> "" Then
            schid = SCH_ID
        End If
        Dim total As Double = 0
        Dim subjectCount As Integer = 0
        Dim fields As New ArrayList
        fields.Add("SUM(" & StudentData.Total & ") AS GrandTotal")
        Dim filterValues As New Hashtable
        filterValues.Add(StudentData.SchoolID, schid)
        filterValues.Add(StudentData.Level, level)
        filterValues.Add(StudentData.Session, session)
        filterValues.Add(StudentData.Term, term)
        filterValues.Add(StudentData.Klass, klass)
        filterValues.Add(StudentData.RegNo, regno)
        Dim filterArgs As String = "WHERE " & StudentData.SchoolID & "=@" & StudentData.SchoolID & " AND " & StudentData.Level & "=@" & StudentData.Level & " AND " & StudentData.Session & "=@" & StudentData.Session & " AND " & StudentData.Term & "=@" & StudentData.Term & " AND " & StudentData.Klass & "=@" & StudentData.Klass & " AND " & StudentData.RegNo & "=@" & StudentData.RegNo
        Dim data As DataSet = _Data.GetData(StudentData.tblStudentResult, fields, filterValues, filterArgs)
        'If data.Tables(0).Rows.Count > 0 Then
        '    For Each dr As DataRow In data.Tables(0).Rows
        '        total += CDbl(NormalizeRecord(dr(StudentData.Total)))
        '        subjectCount += 1
        '    Next
        'End If
        Dim dr As DataRow = data.Tables(0).Rows(0)
        total = CDbl(dr("GrandTotal"))
        Return total
End Function

Function 2: GetData

Function GetData(ByVal tbl As String, ByVal values As ArrayList, ByVal filters As Hashtable, ByVal filterArgs As String) As DataSet
        Dim _ds As New DataSet
        Dim sql As String = "SELECT "
        Dim fields As String = ""
        Using conn As New MySqlConnection(connString)
            conn.Open()
            If values IsNot Nothing Then
                For i = 0 To values.Count - 1
                    If fields = "" Then
                        fields = values.Item(i).ToString
                    Else
                        fields &= "," & values.Item(i).ToString
                    End If
                Next
                sql &= fields & " "
            End If
            sql &= "FROM " & tbl
            If filterArgs <> "" Then
                sql &= " " & filterArgs
            End If
            Dim cmd As New MySqlCommand(sql, conn)
            If filters IsNot Nothing Then
                For i = 0 To filters.Count - 1
                    cmd.Parameters.AddWithValue("@" & filters.Keys(i), filters.Values(i))
                Next
            End If
            Dim da As New MySqlDataAdapter(cmd)
            da.Fill(_ds)
            conn.Close()
        End Using
        Return _ds
End Function

Function 3: NormalizeRecord

Function NormalizeRecord(ByVal value As String) As String
        If value = "-" Then
            value = "0"
        End If
        Return value
End Function

解决方案 #

有三种可靠的方式处理这个空值问题，按推荐程度排序：

1. 在SQL层面处理Null（推荐）

直接在MySQL的SUM函数中使用IFNULL，将Null值转为0，这样查询结果永远不会返回Null，从根源避免转换错误。修改fields.Add的语句：

fields.Add("SUM(IFNULL(" & StudentData.Total & ", 0)) AS GrandTotal")

这种方法的优势是把空值处理放在数据库层，后续代码无需额外判断，逻辑更简洁可靠。

2. 在代码中显式判断DBNull

如果无法修改SQL，可以在获取DataRow后，先判断字段是否为DBNull，再执行类型转换：

Dim dr As DataRow = data.Tables(0).Rows(0)
If Not IsDBNull(dr("GrandTotal")) Then
    total = CDbl(dr("GrandTotal"))
Else
    total = 0.0
End If

3. 复用NormalizeRecord函数优化

可以扩展现有NormalizeRecord逻辑，或者在转换前结合空值判断：

Dim grandTotalValue As Object = dr("GrandTotal")
total = CDbl(If(IsDBNull(grandTotalValue), 0, NormalizeRecord(grandTotalValue.ToString())))

这种方式适合需要统一处理特殊值（比如原函数中的"-"转0）的场景。

修改后的完整函数示例（方法1） #

Function GetStudentResultTotal(ByVal schid As String, ByVal level As String, ByVal session As String, ByVal term As String, ByVal klass As String, ByVal regno As String) As Double
    If SCH_ID <> "" Then
        schid = SCH_ID
    End If
    Dim total As Double = 0
    Dim fields As New ArrayList
    ' 使用IFNULL确保SUM结果不为Null
    fields.Add("SUM(IFNULL(" & StudentData.Total & ", 0)) AS GrandTotal")
    Dim filterValues As New Hashtable
    filterValues.Add(StudentData.SchoolID, schid)
    filterValues.Add(StudentData.Level, level)
    filterValues.Add(StudentData.Session, session)
    filterValues.Add(StudentData.Term, term)
    filterValues.Add(StudentData.Klass, klass)
    filterValues.Add(StudentData.RegNo, regno)
    Dim filterArgs As String = "WHERE " & StudentData.SchoolID & "=@" & StudentData.SchoolID & " AND " & StudentData.Level & "=@" & StudentData.Level & " AND " & StudentData.Session & "=@" & StudentData.Session & " AND " & StudentData.Term & "=@" & StudentData.Term & " AND " & StudentData.Klass & "=@" & StudentData.Klass & " AND " & StudentData.RegNo & "=@" & StudentData.RegNo
    Dim data As DataSet = _Data.GetData(StudentData.tblStudentResult, fields, filterValues, filterArgs)
    Dim dr As DataRow = data.Tables(0).Rows(0)
    total = CDbl(dr("GrandTotal"))
    Return total
End Function

内容的提问来源于stack exchange，提问作者Fabian Technology