No rows returned with OleDbParameter.Value = DBNull.Value

[rguay]

I'm trying to select data with an OleDbCommand on a parameterized query. When the parameter's value is set to DBNull.Value, no rows are returned even if one exists. I'm using an Orable 9i database.

Here is a complete setup to reproduce the situation, you just need your own connection parameters.

Any comment will be appreciated.

Imports System.Data.Oledb

Module Module1

Sub Main()

Const SQL_CREATE As String = "CREATE TABLE TEST (FIELD1 VARCHAR2(20))"
Const SQL_INSERT1 As String = "INSERT INTO TEST (FIELD1) VALUES ('This is a test')"
Const SQL_INSERT2 As String = "INSERT INTO TEST (FIELD1) VALUES (NULL)"
Const SQL_SELECT As String = "SELECT FIELD1 FROM TEST WHERE (FIELD1 = ?)"
Const SQL_DROP As String = "DROP TABLE TEST"

' Create and open connection.
Dim cn As New OleDbConnection("Provider=""MSDAORA.1"";User ID=xxxxx;Data Source=""your.datasource"";Password=xxxxx")
cn.Open()

Try
' Create a command.
Dim cmd As New OleDbCommand
cmd.Connection = cn

' Create table.
cmd.CommandText = SQL_CREATE
cmd.ExecuteNonQuery()

' Insert rows.
cmd.CommandText = SQL_INSERT1
cmd.ExecuteNonQuery()
cmd.CommandText = SQL_INSERT2
cmd.ExecuteNonQuery()

' Prepare Select.
Dim number As Integer
cmd.CommandText = SQL_SELECT
cmd.Parameters.Add(New OleDbParameter("param1", DBNull.Value))
Dim da As New OleDbDataAdapter(cmd)
Dim ds As DataSet

' Select Not Null
cmd.Parameters("param1").Value = "This is a test"
ds = New DataSet
da.Fill(ds)
' ### This is OK, it finds the row. ###
number = ds.Tables(0).Rows.Count

' Select DbNull
cmd.Parameters("param1").Value = DBNull.Value
ds = New DataSet
da.Fill(ds)
' ### !!! This does not return the expected row. !!! ###
number = ds.Tables(0).Rows.Count

' Drop table.
cmd.CommandText = SQL_DROP
cmd.Parameters.Clear()
cmd.ExecuteNonQuery()

Finally
cn.Close()
End Try

End Sub

End Module