nulled out - defaults instead?

[Bernie Yaeger]

OK, I'm nulled out.

I am converting a dbase based enterprise wide system that has almost 100
tables to convert into a vb .net system using ms sql server 2000 (I am
posting this both to ado .net newsgroups and sql server newsgroups). I am
working with a prototype where many of the columns in many of the tables
allow nulls. But often I can't call ... is null (in tsql) or isdbnull(...)
in vb .net on the same line as, say, 'or len(trim((dddd)) < 1' because this
throws an error if the col is null, since you can't measure anything when a
col is null.

Now null might have a place in the universe - like black holes - but not
being Stephen Hawkings I just don't know what that place is. But in vb .net
especially and to some extent in tsql also, it's just a pain in the ....

My question - is there any reason I shouldn't convert into tables where,
when the data is converted if it's empty or 0 (int) or # / / # (date), I
use defaults instead (eg, "", 0, 01/01/1900 respectively)? Do I lose
anything by doing this?

Tx for any help.

Bernie Yaeger

 

[tperovic]

I had the same problem until I discovered AndAlso and OrElse:

Instead of

If dr.IsNull("SomeColumn") Or Len(Trim(dr("SomeColumn"))) = 0 Then

where the RHS (right hand side) of the or will fail if the column is null
try this:

If dr.IsNull("SomeColumn") OrElse Len(Trim((dddd)) = 0 Then

Now, the RHS is not evaluated unless the LHS is not null.

This way you can keep your nulls because using dates like "01/01/1900" to
indicate no date or missing date will cause other problems.

 

[Bernie Yaeger]

Hi Bill,

Tx - you've articulated the madness better than I could - and tx for your
advice.

Bernie

 

[Bernie Yaeger]

Hi tp,

Tx for your advice - yes, 01/01/1900 could create problems, but surely 0 and
"" for int and chars respectively are better than dealing with null values.
I have occasion to use dr.isdbnull("col") and dr.isdbnull("col2") and
dr.isdbnull("col3") all at the same time - trapping that with orelse is
still difficult.

Thanks again,

Bernie