MS-Access查询不能使用OLEDB.NET正确解释DBNull.Value [英] MS-Access query does not correctly interpret DBNull.Value using OLEDB.NET

问题描述

我正在使用VB 2010和OLEDB连接到MS-Access 2007数据库.进行以下测试似乎表明，当从OLEDB作为参数发送时，MS-Access无法正确解释DBNull.Value:

I am connecting to a MS-Access 2007 database using VB 2010 and OLEDB. Conducting the following test seems to suggest that MS-Access does not interpret DBNull.Value correctly when sent as a parameter from OLEDB:

(医院"表包含1行，其中"LatLong""列设置为null)

(The Hospital table contains 1 row with the "LatLong" column set to null)

Dim cnt = Common.GetScalar(axsCon, "SELECT Count(*) FROM Hospitals WHERE LatLong = @LL ", _
                New String() {"LL"},
                New Object() {DBNull.Value})

此查询返回cnt = 0

This query returns cnt = 0

但是: cnt = Common.GetScalar(axsCon，从LatLong为NULL的医院中选择SELECT((*)))

However: cnt = Common.GetScalar(axsCon, "SELECT Count(*) FROM Hospitals WHERE LatLong IS NULL ")

返回cnt = 1

任何想法都值得赞赏.

p.s .: Common.GetScalar看起来像:

p.s.: Common.GetScalar looks like:

Public Shared Function GetScalar( _
    ByRef OleCon As OleDbConnection, _
    ByRef SQL As String, _
    Optional ByRef Params() As String = Nothing, _
    Optional ByRef Vals() As Object = Nothing, _
    Optional IsQuery As Boolean = False) As Object
    Try
        Dim oleCmd As OleDbCommand = OleCon.CreateCommand
        oleCmd.CommandType = IIf(IsQuery, CommandType.StoredProcedure, CommandType.Text)
        oleCmd.CommandText = SQL
        If Not Params Is Nothing Then
            Dim pInx As Int16
            For pInx = 0 To Params.Count - 1
                oleCmd.Parameters.AddWithValue(Params(pInx), Vals(pInx))
            Next
        End If
        Return oleCmd.ExecuteScalar()
    Catch ex As Exception
        Throw New Exception(ex.Message)
    End Try
End Function

TIA

推荐答案

更改

"SELECT Count(*) FROM Hospitals WHERE LatLong = @LL"

到

"SELECT Count(*)
来自医院
在哪里1 =
   案例
       当@LL为空且LatLong为空时，则1
        When LatLong = @LL THEN 1
    END"

"SELECT Count(*)
FROM Hospitals
WHERE 1=
    CASE
        WHEN @LL IS null AND LatLong IS null THEN 1
        WHEN LatLong = @LL THEN 1
    END"

然后将检查是否为空或匹配值.空值可能非常棘手.

This will then check for null or matching value. Nulls can be very very tricky.

这篇关于MS-Access查询不能使用OLEDB.NET正确解释DBNull.Value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！