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

  • Thread starter Thread starter Boris Zakharin
  • Start date Start date
B

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?
 
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
 
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
 
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
 
Back
Top