DBNULL not really equal to VB.NET NOTHING??

Post by jobs
"None" is the text and does show up, but entries with DBNULL don't seem
to match to "Nothing"
Is there some code or value I can put in there that will tie to DBNULL,
or do I need to Cast DBNULL on the way out and on the way up?

This question really belonged in an ADO.NET newsgroup -- it's got
practically nothing to do with SQL Server, as you should see the same
behavior with almost any back end.

But I'll give the answer anyway:

sender.Items.Insert(0, New ListItem("None",DBNull.Value))

rpresser

2006-12-06 21:55:07 UTC

This question really belonged in an ADO.NET newsgroup -- it's got
practically nothing to do with SQL Server, as you should see the same
behavior with almost any back end.
sender.Items.Insert(0, New ListItem("None",DBNull.Value))

Let me revise and extend that answer somewhat.

DBNull.Value is the equivalent to a NULL value returned from a
database. In other words, the following code snippet will print true
(not complete, but you should get the idea)

Dim o as object
With new oledbConnection.CreateCommand()
.CommandText = "SELECT NULL AS TheColumn"
o = .ExecuteScalar()
End With
Debug.Print IsDBNull(o) ' Prints "True"

However, as you can see at the end, you cannot test for DBNull.Value
using an equals or any other test except the IsDBNull() function. So
your listbox code will probably need to explicitly test for
DBNull.Value before doing anything with anything.

w***@gmail.com

2006-12-06 22:12:39 UTC

Post by rpresser
However, as you can see at the end, you cannot test for DBNull.Value
using an equals or any other test except the IsDBNull() function.

You can use the equality operator (==) as well.

Let me revise and extend that answer somewhat.
DBNull.Value is the equivalent to a NULL value returned from a
database. In other words, the following code snippet will print true
(not complete, but you should get the idea)
Dim o as object
With new oledbConnection.CreateCommand()
.CommandText = "SELECT NULL AS TheColumn"
o = .ExecuteScalar()
End With
Debug.Print IsDBNull(o) ' Prints "True"
However, as you can see at the end, you cannot test for DBNull.Value
using an equals or any other test except the IsDBNull() function. So
your listbox code will probably need to explicitly test for
DBNull.Value before doing anything with anything.

rpresser

2006-12-06 22:36:28 UTC

Post by rpresser
However, as you can see at the end, you cannot test for DBNull.Value
using an equals or any other test except the IsDBNull() function.

You can use the equality operator (==) as well.

Unless C#, which I am not familiar with, automatically translates use
of the equality operator into an invocation of IsDBNull, or handles the
matter in some other mysterious way, then this statement is false, or
at least of no practically value. Any attempt to use the equality
operator when one side is DBNull.Value and the other side isn't will
throw an exception. At least that's my experience in VB.NET.

But as I've said three times now, this is an ADO.NET matter and doesn't
belong in this newsgroup at all.

w***@gmail.com

2006-12-06 23:48:18 UTC

Post by rpresser
Unless C#, which I am not familiar with, automatically translates use
of the equality operator into an invocation of IsDBNull, or handles the
matter in some other mysterious way, then this statement is false, or
at least of no practically value.

Actually it's just the opposite and not mysterious in any way, take a
look at the function. IsDbNull just runs some other checks in addition
to using the equality(inequality) operator. From the function:

...
if ((obj1 != null) && (obj1 != DBNull.Value))
{
return ((INullable) obj1).IsNull;
}
return true;

Post by rpresser
Any attempt to use the equality
operator when one side is DBNull.Value and the other side isn't will
throw an exception.

They won't be equal if the object being compare isn't actually of type
DBNull but there won't be an exception.

SqlCommand command = new SqlCommand("select null",conn);
object o = command.ExecuteScalar();
Console.WriteLine(o == DBNull.Value);

Post by rpresser
But as I've said three times now, this is an ADO.NET matter and doesn't
belong in this newsgroup at all.

True, but it's still good to have the information correct if provided.

Post by rpresser
However, as you can see at the end, you cannot test for DBNull.Value
using an equals or any other test except the IsDBNull() function.

You can use the equality operator (==) as well.

Unless C#, which I am not familiar with, automatically translates use
of the equality operator into an invocation of IsDBNull, or handles the
matter in some other mysterious way, then this statement is false, or
at least of no practically value. Any attempt to use the equality
operator when one side is DBNull.Value and the other side isn't will
throw an exception. At least that's my experience in VB.NET.
But as I've said three times now, this is an ADO.NET matter and doesn't
belong in this newsgroup at all.

rpresser

2006-12-07 15:35:16 UTC

Post by rpresser
Any attempt to use the equality
operator when one side is DBNull.Value and the other side isn't will
throw an exception.

They won't be equal if the object being compare isn't actually of type
DBNull but there won't be an exception.

My apologies, and gratitude for correcting a mistaken notion in my
head.

I was misled by the memory of how the typed dataset code throws an
exception if you try to use a column value in a row which is DBNull.
But that's code explicitly written to trap DBNulls.

rpresser

2006-12-07 15:58:53 UTC

Post by rpresser
Any attempt to use the equality
operator when one side is DBNull.Value and the other side isn't will
throw an exception.

They won't be equal if the object being compare isn't actually of type
DBNull but there won't be an exception.

My apologies, and gratitude for correcting a mistaken notion in my
head.
I was misled by the memory of how the typed dataset code throws an
exception if you try to use a column value in a row which is DBNull.
But that's code explicitly written to trap DBNulls.

Now I have to retract my apology. Comparing two references of
different types using the equality operator WILL throw an exception.

Sub test()
Dim x, y As Object
x = DBNull.Value
y = New System.Diagnostics.DefaultTraceListener
Try
If x = y Then
Debug.WriteLine("yes")
End If
Catch ex As Exception
Debug.WriteLine(ex.ToString)
End Try

End Sub

System.InvalidCastException: Operator is not valid for type 'DBNull'
and type 'DefaultTraceListener'.
at
Microsoft.VisualBasic.CompilerServices.ObjectType.ThrowNoValidOperator(Object
obj1, Object obj2)
at Microsoft.VisualBasic.CompilerServices.ObjectType.ObjTst(Object
o1, Object o2, Boolean TextCompare)
at pdfselect.pdftopdf.test() in
W:\Development\pdfselect\pdftopdf.vb:line 194

Now, if the behavior is different in C#, that's fine when you're
writing C#, but if you're writing VB.NET, you have to be careful of
types.

w***@gmail.com

2006-12-07 18:57:00 UTC

I don't use VB.net but I believe that you want the "is" operator
instead of the "=" operator.

If x is y Then

Post by rpresser
Any attempt to use the equality
operator when one side is DBNull.Value and the other side isn't will
throw an exception.

They won't be equal if the object being compare isn't actually of type
DBNull but there won't be an exception.

My apologies, and gratitude for correcting a mistaken notion in my
head.
I was misled by the memory of how the typed dataset code throws an
exception if you try to use a column value in a row which is DBNull.
But that's code explicitly written to trap DBNulls.

Now I have to retract my apology. Comparing two references of
different types using the equality operator WILL throw an exception.
Sub test()
Dim x, y As Object
x = DBNull.Value
y = New System.Diagnostics.DefaultTraceListener
Try
If x = y Then
Debug.WriteLine("yes")
End If
Catch ex As Exception
Debug.WriteLine(ex.ToString)
End Try
End Sub
System.InvalidCastException: Operator is not valid for type 'DBNull'
and type 'DefaultTraceListener'.
at
Microsoft.VisualBasic.CompilerServices.ObjectType.ThrowNoValidOperator(Object
obj1, Object obj2)
at Microsoft.VisualBasic.CompilerServices.ObjectType.ObjTst(Object
o1, Object o2, Boolean TextCompare)
at pdfselect.pdftopdf.test() in
W:\Development\pdfselect\pdftopdf.vb:line 194
Now, if the behavior is different in C#, that's fine when you're
writing C#, but if you're writing VB.NET, you have to be careful of
types.

w***@gmail.com

2006-12-07 19:04:14 UTC

I don't use VB.net but I believe that you want the "is" operator
instead of the "=" operator.

If x is y Then

Post by rpresser
Any attempt to use the equality
operator when one side is DBNull.Value and the other side isn't will
throw an exception.

They won't be equal if the object being compare isn't actually of type
DBNull but there won't be an exception.

My apologies, and gratitude for correcting a mistaken notion in my
head.
I was misled by the memory of how the typed dataset code throws an
exception if you try to use a column value in a row which is DBNull.
But that's code explicitly written to trap DBNulls.

Now I have to retract my apology. Comparing two references of
different types using the equality operator WILL throw an exception.
Sub test()
Dim x, y As Object
x = DBNull.Value
y = New System.Diagnostics.DefaultTraceListener
Try
If x = y Then
Debug.WriteLine("yes")
End If
Catch ex As Exception
Debug.WriteLine(ex.ToString)
End Try
End Sub
System.InvalidCastException: Operator is not valid for type 'DBNull'
and type 'DefaultTraceListener'.
at
Microsoft.VisualBasic.CompilerServices.ObjectType.ThrowNoValidOperator(Object
obj1, Object obj2)
at Microsoft.VisualBasic.CompilerServices.ObjectType.ObjTst(Object
o1, Object o2, Boolean TextCompare)
at pdfselect.pdftopdf.test() in
W:\Development\pdfselect\pdftopdf.vb:line 194
Now, if the behavior is different in C#, that's fine when you're
writing C#, but if you're writing VB.NET, you have to be careful of
types.

w***@gmail.com

2006-12-06 22:03:58 UTC

Where have you read this? Nothing (null) and DBNull.Value are not the
same thing. DBNull.Value returns a reference to the sole instance of
the DBNull class (since it's a singleton). Nothing (null) implies that
you have a reference that doesn't point to anything.

Post by jobs
I have a dropdownlist that can't match up a NULL value to entry during
has a SelectedValue which is invalid because it does not exist in the
list of items.
Parameter name: value
I know I can get around the issue by doing a union with a DBNULL in the
column before binding in the .net sqldatasource control. However, I
want to use that datasource for other controls and dont want it to have
a DBNULL value. So I thought the below code would work, since Ive read
that vb's NOthing is equal to DBNULL. Apparently it's not as the error
persist.
Protected Sub AddNull(ByVal sender As Object, ByVal e As
System.EventArgs)
sender.Items.Insert(0, New ListItem("None", Nothing))
End Sub
"None" is the text and does show up, but entries with DBNULL don't seem
to match to "Nothing"
Is there some code or value I can put in there that will tie to DBNULL,
or do I need to Cast DBNULL on the way out and on the way up?

rpresser

2006-12-06 22:32:17 UTC

Post by w***@gmail.com
Where have you read this? Nothing (null) and DBNull.Value are not the
same thing. DBNull.Value returns a reference to the sole instance of
the DBNull class (since it's a singleton). Nothing (null) implies that
you have a reference that doesn't point to anything.

As I said, this question belongs in ADO.NET. But you've made it worse.

NULL, Nothing, and DBNull.Value are three different things. NULL is not
Nothing.

NULL in my answer refers to a NULL value *in the database*. In SQL,
NULL means that a particular column in a particular row has not been
assigned a value. There's no NULL in any part of the .NET framework;
it's a SQL term.

"Nothing" is, as you say, a reference that doesn't point to anything.
And you are correct again, that DBNull.Value returns a reference to the
sole instance of the singleton class DBNull.

When OLEDB or SQLClient are used to retrieve data from a data source
and represent it in the .NET Framework, a NULL on the database end is
represented by DBNull.Value in the .NET Framework.

I state again, there is no NULL in any part of the .NET framework.

On the other hand, "null" is the keyword that is used in C# to mean the
same thing that "Nothing" means in VB.NET -- a reference that doesn't
point to anything.

w***@gmail.com

2006-12-06 23:33:02 UTC

I apologize, when I put null in the parans I was explaining that
nothing is the same idea as null in C# (and almost every other OO
language).

As I said, this question belongs in ADO.NET. But you've made it worse.
NULL, Nothing, and DBNull.Value are three different things. NULL is not
Nothing.
NULL in my answer refers to a NULL value *in the database*. In SQL,
NULL means that a particular column in a particular row has not been
assigned a value. There's no NULL in any part of the .NET framework;
it's a SQL term.
"Nothing" is, as you say, a reference that doesn't point to anything.
And you are correct again, that DBNull.Value returns a reference to the
sole instance of the singleton class DBNull.
When OLEDB or SQLClient are used to retrieve data from a data source
and represent it in the .NET Framework, a NULL on the database end is
represented by DBNull.Value in the .NET Framework.
I state again, there is no NULL in any part of the .NET framework.
On the other hand, "null" is the keyword that is used in C# to mean the
same thing that "Nothing" means in VB.NET -- a reference that doesn't
point to anything.

Mike C#

2006-12-07 02:31:32 UTC