Nulls, DBNull, and Nothing -- when and how?

[Yuliya]

First all, thank you to everybody on this forum! It's been tremendously helpful to me.

I have yet another question. I am very confused about null values and when to use what.

When I call FindBy() method of a table in a database, apparently it returns Nothing if such an entry doesn't exist.

When I call DataRow().Item(some column), apparently it returns DBNull is there is nothing there.

But when I call TableAdapter.Insert(), apparently I have to specify a value for each column, because it would not take DBNull.Value as a parameter. But what do I do if I'd like to leave it empty?

Are there any rules for when you use what and how to check if something is null, or what to set it to when I'd like it to be null?

 

[Yuliya]

But when I call TableAdapter.Insert(), apparently I have to specify a value for each column, because it would not take DBNull.Value as a parameter. But what do I do if I'd like to leave it empty?

I discovered that it seems to work if I set it to Nothing. But how do you know when to use Nothing and when to use DBNull?

 

[JohnH]

DBNull is DB-side, Nothing is VB-side, "null" is a Sql query keyword. A TableAdapter (TA) is a proxy that encapsulates the communication with the database and strong type the dataset with its tables and columns. A column have DB data type (for example indicated as SqlDbType.Int in VS) and a corresponding VB data type (Integer). For your strong type easy-to-use TA there is perhaps a Name column of type String, in VB you can set this to Nothing, but you can't set a String to DBNull. If you had written the database interaction yourself with a DataAdapter/SqlCommand you would have to use DBNull as parameter value for a query (or hardcoding null into the query string), because database don't understand the VB Nothing, but it does know DBNull. TA does this translation automatically when interacting with the DB, it checks if the VB value is Nothing and passes DBNull to database. The other way around it is opposite, the values from database is filled straight to the tables, so a row item may be a DBNull. Many dataobjects in .Net have a IsDBNull method that simply compares the data item value to the DBNull object, this is even added as a typed method for each field in a TA datarow (for example somedbDataset.Table1Row.IsNameNull method). If you retrieve for example the Name column(item) of a row from a TA it will try to convert the underlying value to a String, if the value is a DBNull the conversion fails and you get a VB Nothing in return.

 

[Yuliya]

Thank you. That's very helpful.

 

[cjard]

First all, thank you to everybody on this forum! It's been tremendously helpful to me.

I have yet another question. I am very confused about null values and when to use what.

When I call FindBy() method of a table in a database, apparently it returns Nothing if such an entry doesn't exist.

Nothing is VB's way of returning you a pointer that points to.. well.. Nothing. There is nearly always a need for a value of Nothing to specify that something doesnt exist. If the FindBy method returned you a row full of blank strings, how would you know whether it didnt find anything, or whether there really was actually a row in your table full of blank strings?

Nothing is really helpful.. Suppose I ask:
Am I taller than you?
"yes"
"no"
dont know

It sa boolean question, but how do you indicate that you dont know the answer? You cant say "no" - because thats a deifnite answer. If you dont know, you say Nothing. Does this make sense?

Nothing is used throughout VB wherever it makes sense to be able to communicate that Nothing is available for that context. Its a client side "value" of a variable that indicates the variable has no value what so ever.

When I call DataRow().Item(some column), apparently it returns DBNull is there is nothing there.

Yes, because databases are separate programs, on se[arate systems on separate computers maybe on the other side of the world. And your VB has to interact with lots of them. DBNull is an abstraction of the "Nothing" concept above, but for databases. Think of it as a way of communicating to or from a database that a particular column has NO VALUE what so ever.

But when I call TableAdapter.Insert(), apparently I have to specify a value for each column, because it would not take DBNull.Value as a parameter. But what do I do if I'd like to leave it empty?

DBNull is a specific type. It is not a string, it is not a number, it is not a boolean. If INSERT() demands a string, as a client side VB data type, you cannot pass DBNull.Value in there because ther eis no conversion from string to DBNull just like there is no conversion from Button, to Service or any other crazy conversion you can think of.

This is quite hard to explain, but quite simple to remember when you get the concept. As a rule:
If you want to store nothing in a TYPE SPECIFIC datatable column, use Nothing and the datatable/adapter will convert to DBNull for you when it sends to the database
If you want to store nothing in a generic datatable, set it to DBNull.Value


Ideally, you should be using typed datatables all the time, and asking:

myDataSet.MyDataTable(0).IsMY_COLUMNNull()

NOT doing:
myDataSet.Tables("MyDataTable").Rows(0).Items("MY_COLUMN").Value = DbNull.Value

Are there any rules for when you use what and how to check if something is null, or what to set it to when I'd like it to be null?

Look at the signature of the INSERT statement of the tableadapter. Remeber that DBNull is a type, just like String, and that it can be Nothing just like a string. Only Nothing can be used as a "value" (as an indicator of 'no value what so ever') for any reference type object variable

Dim s as String
s = Nothing

Dim d as DBNull
d = Nothing

(this may not be possible if DBNull is a singleton, i'm just trying to get the point across that DBNull is a type of object just like string, and is not interchangeable for Nothing)

See the remarks in MSDN:
http://msdn2.microsoft.com/en-us/library/system.dbnull.aspx

"Do not confuse..."

 

[Yuliya]

Thank you. Makes sense.