Why NULL??????

[Moorzee]

Why do we need NULL values in a varchar column on database? They are causing me so much jip. Why not just store emptystring instead of null? obj.Property = ds.tables(0)("StringValue") gives error if null value stored...... Why do we even have them?

Do i I really have to throw an "iif" around every non-mandatory value brought back from the db? Have I missed summot really really simple?

 

[TechGnome]

Because there is a difference beween an empty value (empty string) and a NULL - which means it's an UNKNOWN value.

There are three ways around this: 1) is to check the value against DBNULL, and handle it accordingly, 2) append a NullString value to it (it was called vbNullString in VB6, not sure what it is in .NET) or 3) Use ISNULL (or NZ in access) to return the empty string. What you shouldn't do is use IIF.... as VB will evaluate all three parts, which will result in an invalid use of Null error (silly I know, but that's the way it works.)

-tg

 

[Moorzee]

Cheers TG.

Yeah point 3 I encountered as soon as I started to use it. A bit sh!tty that I reckon. So I have a big n bulky if statement around every single string type data assignment from DB within my classes like so:

If ClientDets.Rows(0)("ForeName").Equals(DBNull.Value) Then
    Me.Forename = ""
Else
    Me.Forename = ClientDets.Rows(0)("ForeName")
End If

Horrible.......

 

[cjard]

There's nothing stopping you storing an empty string instead of null as default, and it really helps to work with strongly typed datasets here..

The reason for NULL, is that sometimes we want to differentiate between a value that has no content and a value that doesnt exist at all

When you work with strongly typed datasets, all the columns that might contain nulls are typed as Nullables (Of the type you normally store)

So you can either say:

Dim ro as myTypedDataRow = DirectCast(myTypedDataTable.Rows(0), myTypedDataRow)
Dim Forename as Nullable(Of String)
Forename = ro.Forename
Dim Surname as Nullable(Of String)
Surname = ro.Surname


Or you can say:
Dim ro as myTypedDataRow = DirectCast(myTypedDataTable.Rows(0), myTypedDataRow)
Dim Forename as String
Forename = ro.Forename.GetValueOrDefault("")
Dim Surname as String
Surname = ro.Surname.GetValueOrDefault("")


Once you start working with them, youll see that Nullables are beautiful things, and at any time you can say GetValueOrDefault and pass in what you want back if it is null.. no need for the IF
Additionally, you can say:
If ro.IsFornameNull Then 'do stuff

No need for this confusing this.that.GetValue.Equals(DBNull.Value)


But, as a final point, I need to make you aware that the only reason your column contains NULL values is because you put them there in the first place.. They cant get there any other way, so you need to cater for that. If youre adding rows to your datatable, thena one-time declaration of
myDataTable.COlumns(0).DefaultValue = ""
ensures your string column contaisn an empty string upon addition

If these records are coming out of a database, ensure that the database inspects them for null and subs in an empty string as they leave.. or update the entire table so they have empty strings instead of nulls. In Oracle this isnt possible, as '' and NULL are synonymous