Take the 2-minute tour ×

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:

(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})

This query returns cnt = 0

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

returns cnt = 1

Any ideas are appreciated.

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
        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))
        End If
        Return oleCmd.ExecuteScalar()
    Catch ex As Exception
        Throw New Exception(ex.Message)
    End Try
End Function


share|improve this question
add comment

2 Answers

up vote 1 down vote accepted




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

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

share|improve this answer
add comment

Your GetScalar will run the query:

SELECT Count(*) FROM Hospitals WHERE LatLong = NULL

but you want

SELECT Count(*) FROM Hospitals WHERE LatLong IS NULL

You need to switch out the = for IS if you're comparing to null.

share|improve this answer
The OP was trying to do this with parameters. –  LarsTech Oct 3 '11 at 0:57
That's not what it looked like to me. They are inserting the Null as a parameter, but the = is hard-coded into the literal SQL string. –  Luke Hoffmann Oct 3 '11 at 1:03
add comment

Your Answer


