Passing null values in a parameter to a number field in ASP.NET

[Boris Zakharin]

I have a numeric field which allows nulls. When I try to set an
OleDbParameter value to null I get an error. When I set it to DBNull.Value I
get a numeric value of zero. Can I pass a real null value this way?

 

[Brendan Reynolds \(MVP\)]

It seems to be possible to assign DBNull.Value if you define the DBType of
the parameter as DbType.Object (see the example code below). However, I
think there may be a more fundamental problem here. How would you define the
parameter in the SQL string? 'WHERE SomeField = ?' isn't going to work,
because nothing is equal to Null, not even another Null. (Is one unknown
value equal to another unknown value? The answer is unknown, i.e. Null).

In short, I think the answer is not to assign a Null value to a parameter,
but not to use a parameter when looking for Null values, use 'WHERE
SomeField IS NULL' in the SQL statement instead.

But you may get a more definitive answer in an ASP.NET (or ADO.NET)
newsgroup.

cmm = New System.Data.OleDb.OleDbCommand
With cmm
prm = .CreateParameter
prm.DbType = DbType.Object
prm.Value = DBNull.Value

End With

 

[Boris Zakharin]

I am not comparing to a null, I want to insert a null value. I've realized
that when I pass the contents of an empty textbox, I do get a null value
into the table now, so I'm fine. I will never compare to this null value, so
that's not the issue. The issue is displaying the resultant table in a
DataGrid, where a null value displays an empty cell (which is what I want)
and a value of zero displays as 0.00.

Thanks anyway,
Boris Zakharin

 

[Brendan Reynolds \(MVP\)]

In that case, DbType.Object seems to do the trick ...

Module Module1

Sub Main()

Dim cnn As System.Data.OleDb.OleDbConnection
Dim cmm As System.Data.OleDb.OleDbCommand
Dim prm As System.Data.OleDb.OleDbParameter

Try
cnn = New System.Data.OleDb.OleDbConnection
With cnn
.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0 ;
Data Source = C:\DSAPPS\db1.mdb"
End With
cmm = New System.Data.OleDb.OleDbCommand
With cmm
.CommandText = "UPDATE tblTest SET TestLong = ? WHERE TestID
= 2"
.CommandType = CommandType.Text
.Connection = cnn
prm = .CreateParameter
prm.DbType = DbType.Object
prm.Value = DBNull.Value
.Parameters.Add(prm)
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
End With
Console.WriteLine("OK")
Console.ReadLine()
Catch ex As Exception
Console.WriteLine(ex.ToString)
Console.ReadLine()
Finally
If Not cnn Is Nothing Then
If cnn.State <> ConnectionState.Closed Then
cnn.Close()
End If
End If
End Try

End Sub

End Module