working with SQL null values....

[Aussie Rules]

Hi,
I have a result set that sometimes will contain a Null value from the SQL
server.

I have looked into this and am trying to manage this with the following

If Not DBNull.Value.Eq uals(dRow("path ")) Then
advCell.Text = dRow("path")
Else
advCell.Text = "No Path"
End If


However it doesn't matter what the result set contains.. it also execute top
condition..

Am I doing something wrong.... is there a better way to deal with Sql Null
values ?

[Steve Gerrard]

Re: working with SQL null values....

Aussie Rules wrote:

Hi,
I have a result set that sometimes will contain a Null value from the
SQL server.
>
I have looked into this and am trying to manage this with the
following
If Not DBNull.Value.Eq uals(dRow("path ")) Then
advCell.Text = dRow("path")
Else
advCell.Text = "No Path"
End If
>
>
However it doesn't matter what the result set contains.. it also
execute top condition..
>
Am I doing something wrong.... is there a better way to deal with Sql
Null values ?

Try
If Not dRow.IsNull("pa th") Then...

You may want to check for 0 length as well.

[=?Utf-8?B?U3VydHVyWg==?=]

Re: working with SQL null values....

DBNulls are so frustrating to me that I cheat and make all my columns
non-nullable. I then just have a default value that means "Null" e.g. "" for
a nvarchar column, VB's "New Date" for a Date field etc.

Not pretty, but it saves a lot of DBNull checking code. And the difference
between DBNull and "" is not as big as you would think.

--
David Streeter
Synchrotech Software
Sydney Australia


"Steve Gerrard" wrote:

Aussie Rules wrote:

Hi,
I have a result set that sometimes will contain a Null value from the
SQL server.

I have looked into this and am trying to manage this with the
following
If Not DBNull.Value.Eq uals(dRow("path ")) Then
advCell.Text = dRow("path")
Else
advCell.Text = "No Path"
End If


However it doesn't matter what the result set contains.. it also
execute top condition..

Am I doing something wrong.... is there a better way to deal with Sql
Null values ?

>
Try
If Not dRow.IsNull("pa th") Then...
>
You may want to check for 0 length as well.
>
>
>

[Lloyd Sheen]

Re: working with SQL null values....


"SurturZ" <surturz@newsgr oup.nospamwrote in message
news:D001405F-3A0E-44B9-A057-AE9024142F0C@mi crosoft.com...

DBNulls are so frustrating to me that I cheat and make all my columns
non-nullable. I then just have a default value that means "Null" e.g. ""
for
a nvarchar column, VB's "New Date" for a Date field etc.
>
Not pretty, but it saves a lot of DBNull checking code. And the difference
between DBNull and "" is not as big as you would think.
>
--
David Streeter
Synchrotech Software
Sydney Australia
>
>
"Steve Gerrard" wrote:
>

>Aussie Rules wrote:

Hi,
I have a result set that sometimes will contain a Null value from the
SQL server.
>
I have looked into this and am trying to manage this with the
following
If Not DBNull.Value.Eq uals(dRow("path ")) Then
advCell.Text = dRow("path")
Else
advCell.Text = "No Path"
End If
>
>
However it doesn't matter what the result set contains.. it also
execute top condition..
>
Am I doing something wrong.... is there a better way to deal with Sql
Null values ?

>>
>Try
> If Not dRow.IsNull("pa th") Then...
>>
>You may want to check for 0 length as well.
>>
>>
>>

Don't want to start a war but I have to disagree. The difference between
null and "" is that null means that the database does not know the value and
"" is the value (a string with zero length). While what you are doing may
work in some cases concider the case of an apartment number. Null means you
don't know if there is an apartment number but "" means there is no
apartment number.

LS

[Armin Zingler]

Re: working with SQL null values....

"Lloyd Sheen" <a@b.cschrieb

>
Don't want to start a war but I have to disagree. The difference
between null and "" is that null means that the database does not
know the value and "" is the value (a string with zero length). While what
you are doing may work in some cases concider the case of
an apartment number. Null means you don't know if there is an
apartment number but "" means there is no apartment number.

I agree with both of you. In some cases you have to distinguish between Null
and "", in some cases you don't have to. In my personal experience, the
former happens much more often, therefore I handle those like David does.


Armin

[Lloyd Sheen]

Re: working with SQL null values....


"Armin Zingler" <az.nospam@free net.dewrote in message
news:%23Zv095d5 IHA.4560@TK2MSF TNGP04.phx.gbl. ..

"Lloyd Sheen" <a@b.cschrieb

>>
>Don't want to start a war but I have to disagree. The difference
>between null and "" is that null means that the database does not
>know the value and "" is the value (a string with zero length). While
>what
>you are doing may work in some cases concider the case of
>an apartment number. Null means you don't know if there is an
>apartment number but "" means there is no apartment number.

>
>
I agree with both of you. In some cases you have to distinguish between
Null
and "", in some cases you don't have to. In my personal experience, the
former happens much more often, therefore I handle those like David does.
>
>
Armin
>

Again I would state that if you can handle the data in any way but it should
be stored in the database as nulls to indicate that state. There are plenty
of methods to display the data after including COALESCE in SQL and Iff in
VB.NET

LS

[=?Utf-8?B?U3VydHVyWg==?=]

Re: working with SQL null values....

Don't want to start a war but I have to disagree. The difference between

null and "" is that null means that the database does not know the value and
"" is the value (a string with zero length). While what you are doing may
work in some cases concider the case of an apartment number. Null means you
don't know if there is an apartment number but "" means there is no
apartment number.

Oh, I agree with you. Setting database fields to not nullable is a cheat.

However, I have found that the "hacks" to avoid DBNulls are usually easier
to read than processing DBNulls. Let's face it, programmers that allow
DBNulls in their database usually have some little function called NULLSAFE
or something that converts DBNulls to empty strings anyway.

For your example of apartment number, either the address is complete or it
isn't. If the apartment number is "" you can assume the address is not an
apartment and format the address appropriately.

In my experience, end users rarely distinguish between "not known" and
"doesn't exist". YMMV of course.


--
David Streeter
Synchrotech Software
Sydney Australia

[Steve Gerrard]

Re: working with SQL null values....

SurturZ wrote:

>
However, I have found that the "hacks" to avoid DBNulls are usually
easier to read than processing DBNulls. Let's face it, programmers
that allow DBNulls in their database usually have some little
function called NULLSAFE or something that converts DBNulls to empty
strings anyway.
>
For your example of apartment number, either the address is complete
or it isn't. If the apartment number is "" you can assume the address
is not an apartment and format the address appropriately.
>

I have no trouble with 0 length strings instead of null, but then again, I have
no trouble with null string fields in the first place. VB seems happy to return
"" from the ToString method.

For Dates, on the other hand, Nulls can be quite meaningful, where a fake date
is not helpful. When is the wedding? Null, for now. Specifying anything else
would be misleading.

[=?Utf-8?B?U3VydHVyWg==?=]

Re: working with SQL null values....

Null Date

Fair enough, I use "01/01/1753 12:00:00" (oldest possible date), which I
then convert to a New Date.

One place you really can't get away from DBNull is for fields that are part
of a relation. e.g. if you have a link to the Employer table from your Client
table, but some Clients are unemployed.

--
David Streeter
Synchrotech Software
Sydney Australia


"Steve Gerrard" wrote:

SurturZ wrote:

However, I have found that the "hacks" to avoid DBNulls are usually
easier to read than processing DBNulls. Let's face it, programmers
that allow DBNulls in their database usually have some little
function called NULLSAFE or something that converts DBNulls to empty
strings anyway.

For your example of apartment number, either the address is complete
or it isn't. If the apartment number is "" you can assume the address
is not an apartment and format the address appropriately.

>
I have no trouble with 0 length strings instead of null, but then again, I have
no trouble with null string fields in the first place. VB seems happy to return
"" from the ToString method.
>
For Dates, on the other hand, Nulls can be quite meaningful, where a fake date
is not helpful. When is the wedding? Null, for now. Specifying anything else
would be misleading.
>
>
>