VB.NET服务程序读取SQL Server含NULL列数据报错及遍历问题

你的错误根源在于：

• 直接对SqlDataReader.Item返回的DBNull值调用Trim()方法，这会触发类型转换异常，因为Trim()仅能作用于字符串类型。
• 处理operationComplete等列的逻辑存在错误：当值为DBNull时，你仍尝试执行Trim(postCount3.Item(22)).ToString()，而Trim(DBNull)本身就不合法，必然触发异常。

编写一个通用的辅助函数，统一处理DBNull与空字符串的转换，所有列的读取都通过该函数完成，避免重复的判断逻辑。

步骤1：添加安全读取字符串的辅助函数 #

在你的类中添加以下函数，它会自动将DBNull转换为空字符串，同时对非空值执行Trim()：

Private Function GetSafeString(reader As SqlDataReader, columnIndex As Integer) As String
    If reader.IsDBNull(columnIndex) Then
        Return String.Empty
    Else
        Return Trim(reader.Item(columnIndex).ToString())
    End If
End Function

步骤2：修改数据读取逻辑 #

将所有直接读取Item并调用Trim的代码替换为调用上述辅助函数，同时移除原来错误的operationComplete等列的单独判断逻辑（辅助函数已统一处理）：

修改后的核心读取代码如下：

While postCount3.Read()
    Try
        SourceID = GetSafeString(postCount3, 0)
        strSourceName = GetSafeString(postCount3, 1)
        strOrderNumber = GetSafeString(postCount3, 4)
        strItemNumber = GetSafeString(postCount3, 11)
        strOperationNumber = GetSafeString(postCount3, 7)
        strCurrentStatus = GetSafeString(postCount3, 2)
        strDepartment = GetSafeString(postCount3, 5)
        strWorkCenter = GetSafeString(postCount3, 6)
        strOperator = GetSafeString(postCount3, 8)
        strTrxDate = GetSafeString(postCount3, 3)
        strPosted = GetSafeString(postCount3, 14)
        totalLaborHrs = GetSafeString(postCount3, 9)
        laborFeetProduced = GetSafeString(postCount3, 10)
        operationComplete = GetSafeString(postCount3, 22)
        strDefaultBin = GetSafeString(postCount3, 13)
        strLotNo = GetSafeString(postCount3, 12)

        ' 日志输出部分保持不变
        FileIO.WriteLog("")
        FileIO.WriteLog(SourceID)
        FileIO.WriteLog(strOrderNumber & " Source Name = " & strSourceName)
        FileIO.WriteLog(strOrderNumber & " Order Number = " & strOrderNumber)
        FileIO.WriteLog(strOrderNumber & " Item Number = " & strItemNumber)
        FileIO.WriteLog(strOrderNumber & " Operation Number = " & strOperationNumber)
        FileIO.WriteLog(strOrderNumber & " Department = " & strDepartment)
        FileIO.WriteLog(strOrderNumber & " Work Center = " & strWorkCenter)
        FileIO.WriteLog(strOrderNumber & " Operator = " & strOperator)
        FileIO.WriteLog(strOrderNumber & " Current Status = " & strCurrentStatus)
        FileIO.WriteLog(strOrderNumber & " Total Labor Hours = " & totalLaborHrs)
        FileIO.WriteLog(strOrderNumber & " Labor Feet Produced = " & laborFeetProduced)
        FileIO.WriteLog(strOrderNumber & " Operation Complete = " & operationComplete)
        FileIO.WriteLog(strOrderNumber & " Default Bin = " & strDefaultBin)
        FileIO.WriteLog(strOrderNumber & " Lot No = " & strLotNo)
    Catch ex As Exception
        FileIO.WriteLog("Error : " & ex.Message)
    End Try
    Continue While
End While

额外优化建议 #

• 尽量使用列名而非索引读取数据，避免表结构变化导致索引对应错误。可以修改辅助函数支持列名：

Private Function GetSafeString(reader As SqlDataReader, columnName As String) As String
    Dim columnIndex As Integer = reader.GetOrdinal(columnName)
    If reader.IsDBNull(columnIndex) Then
        Return String.Empty
    Else
        Return Trim(reader.Item(columnIndex).ToString())
    End If
End Function

使用时改为SourceID = GetSafeString(postCount3, "SOURCE_ID")即可。

• 对于数值类型（比如totalLaborHrs如果是数值），可以编写对应的GetSafeDecimal或GetSafeDouble辅助函数，避免后续使用时的类型转换问题。

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