Detecting "Null" database fields

[D. Shane Fowlkes]

I'm using SQL Server 2000 and on my page, I'm simply creating a
SQLDataReader and filling in Labels with the retrieved (single) record.
However, how can I prevent from getting errors when a field is null?
I've tried something like this (experimenting with IsDBNull):
================================================== =======
If dtrData.IsDBNull("Address2") = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If

And this....

If IsNothing(dtrDatal("Address2")) = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
================================================== ======
...and neither seem to work. Any suggestions? Thanks!

[DalePres]

You can use

Select ISNULL(Address2, '') AS Address2 FROM MyTable

in your SELECT statement or just use this when reading the data:

lblAddress2.Text = "" + dtrData("Addres2")

Dale

"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I'm using SQL Server 2000 and on my page, I'm simply creating a
SQLDataReader and filling in Labels with the retrieved (single) record.
However, how can I prevent from getting errors when a field is null?
I've tried something like this (experimenting with IsDBNull):
================================================== =======
If dtrData.IsDBNull("Address2") = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If

And this....

If IsNothing(dtrDatal("Address2")) = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
================================================== ======
..and neither seem to work. Any suggestions? Thanks!

[Cowboy \(Gregory A. Beamer\)]

My code is normally more like so (C#)

if(dr["field"] == DBNull.Value)
statement;
else
statement;

Which equates to (VB.NET)

If (dr("field") == DBNull.Value) Then
Else
End If

As suggested, you can also do this in the SQL statement if you want to fill
with a string, as you are doing.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I'm using SQL Server 2000 and on my page, I'm simply creating a
SQLDataReader and filling in Labels with the retrieved (single) record.
However, how can I prevent from getting errors when a field is null?
I've tried something like this (experimenting with IsDBNull):
================================================== =======
If dtrData.IsDBNull("Address2") = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If

And this....

If IsNothing(dtrDatal("Address2")) = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
================================================== ======
..and neither seem to work. Any suggestions? Thanks!

[Fred Nelson]

Try:

if (your parameter name) is System.DBNull.Value then
lblAddress2.text = ""
else
lblAddress2.text = (your parameter name)
end if
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I'm using SQL Server 2000 and on my page, I'm simply creating a
SQLDataReader and filling in Labels with the retrieved (single) record.
However, how can I prevent from getting errors when a field is null?
I've tried something like this (experimenting with IsDBNull):
================================================== =======
If dtrData.IsDBNull("Address2") = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If

And this....

If IsNothing(dtrDatal("Address2")) = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
================================================== ======
..and neither seem to work. Any suggestions? Thanks!

[D. Shane Fowlkes]

Modifying my Select would be a sloppy hack...seems like it would anyway.

I did get this to work:

If IsDBNull(dtrData("Address2")) Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
Thanks guys.
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in
message news:O5**************@TK2MSFTNGP11.phx.gbl...

My code is normally more like so (C#)

if(dr["field"] == DBNull.Value)
statement;
else
statement;

Which equates to (VB.NET)

If (dr("field") == DBNull.Value) Then
Else
End If

As suggested, you can also do this in the SQL statement if you want to fill with a string, as you are doing.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I'm using SQL Server 2000 and on my page, I'm simply creating a
SQLDataReader and filling in Labels with the retrieved (single) record.
However, how can I prevent from getting errors when a field is null?
I've tried something like this (experimenting with IsDBNull):
================================================== =======
If dtrData.IsDBNull("Address2") = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If

And this....

If IsNothing(dtrDatal("Address2")) = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
================================================== ======
..and neither seem to work. Any suggestions? Thanks!

[DalePres]

I wouldn't call ISNULL a sloppy hack. There's a reason they built the
functionality into SQL. The advantage is that, if it's in a stored
procedure, any app that calls the stored proc gets the same handling and
gives a consistent result. Also, it is one place to change the code, rather
than in every app.

Just my opinion.

Dale

"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Modifying my Select would be a sloppy hack...seems like it would anyway.

I did get this to work:

If IsDBNull(dtrData("Address2")) Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
Thanks guys.
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in
message news:O5**************@TK2MSFTNGP11.phx.gbl...

My code is normally more like so (C#)

if(dr["field"] == DBNull.Value)
statement;
else
statement;

Which equates to (VB.NET)

If (dr("field") == DBNull.Value) Then
Else
End If

As suggested, you can also do this in the SQL statement if you want to

fill

with a string, as you are doing.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I'm using SQL Server 2000 and on my page, I'm simply creating a
SQLDataReader and filling in Labels with the retrieved (single) record. However, how can I prevent from getting errors when a field is null?
I've tried something like this (experimenting with IsDBNull):
================================================== =======
If dtrData.IsDBNull("Address2") = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If

And this....

If IsNothing(dtrDatal("Address2")) = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
================================================== ======
..and neither seem to work. Any suggestions? Thanks!

[D. Shane Fowlkes]

No offense. A sloppy hack for me maybe....I just wanted to learn what I was
doing wrong first.
"DalePres" <[e-mail]> wrote in message
news:u2**************@tk2msftngp13.phx.gbl...

I wouldn't call ISNULL a sloppy hack. There's a reason they built the
functionality into SQL. The advantage is that, if it's in a stored
procedure, any app that calls the stored proc gets the same handling and
gives a consistent result. Also, it is one place to change the code, rather than in every app.

Just my opinion.

Dale

"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Modifying my Select would be a sloppy hack...seems like it would anyway.

I did get this to work:

If IsDBNull(dtrData("Address2")) Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
Thanks guys.
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in

message news:O5**************@TK2MSFTNGP11.phx.gbl...

My code is normally more like so (C#)

if(dr["field"] == DBNull.Value)
statement;
else
statement;

Which equates to (VB.NET)

If (dr("field") == DBNull.Value) Then
Else
End If

As suggested, you can also do this in the SQL statement if you want to

fill

with a string, as you are doing.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
> I'm using SQL Server 2000 and on my page, I'm simply creating a
> SQLDataReader and filling in Labels with the retrieved (single)

record. > However, how can I prevent from getting errors when a field is null?
>
>
> I've tried something like this (experimenting with IsDBNull):
> ================================================== =======
> If dtrData.IsDBNull("Address2") = True Then
> lblAddress2.Text = ""
> Else
> lblAddress2.Text = dtrData("Address2")
> End If
>
> And this....
>
> If IsNothing(dtrDatal("Address2")) = True Then
> lblAddress2.Text = ""
> Else
> lblAddress2.Text = dtrData("Address2")
> End If
> ================================================== ======
>
>
> ..and neither seem to work. Any suggestions? Thanks!
>
>
>
>

[Cowboy \(Gregory A. Beamer\)]

The code there is very VB.NET centric. This is not a problem, but realize
that it does not translate directly to other .NET languages. If VB.NET is
the language of choice, go with it. Peace!

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Modifying my Select would be a sloppy hack...seems like it would anyway.

I did get this to work:

If IsDBNull(dtrData("Address2")) Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
Thanks guys.
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in
message news:O5**************@TK2MSFTNGP11.phx.gbl...

My code is normally more like so (C#)

if(dr["field"] == DBNull.Value)
statement;
else
statement;

Which equates to (VB.NET)

If (dr("field") == DBNull.Value) Then
Else
End If

As suggested, you can also do this in the SQL statement if you want to

fill

with a string, as you are doing.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I'm using SQL Server 2000 and on my page, I'm simply creating a
SQLDataReader and filling in Labels with the retrieved (single) record. However, how can I prevent from getting errors when a field is null?
I've tried something like this (experimenting with IsDBNull):
================================================== =======
If dtrData.IsDBNull("Address2") = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If

And this....

If IsNothing(dtrDatal("Address2")) = True Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
================================================== ======
..and neither seem to work. Any suggestions? Thanks!

[Cowboy \(Gregory A. Beamer\)]

In some instances, it would be a sloppy hack. For example, I have seen
databases (God only knows why) that use both null and empty string (or
single char) to represent different conditions. ISNULL would not detect
between the two.

Detecting on the UI layer is a better option in some situations, while
detecting and "correcting" on the data layer is better in others.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"DalePres" <[e-mail]> wrote in message
news:u2**************@tk2msftngp13.phx.gbl...

I wouldn't call ISNULL a sloppy hack. There's a reason they built the
functionality into SQL. The advantage is that, if it's in a stored
procedure, any app that calls the stored proc gets the same handling and
gives a consistent result. Also, it is one place to change the code, rather than in every app.

Just my opinion.

Dale

"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Modifying my Select would be a sloppy hack...seems like it would anyway.

I did get this to work:

If IsDBNull(dtrData("Address2")) Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
Thanks guys.
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in

message news:O5**************@TK2MSFTNGP11.phx.gbl...

My code is normally more like so (C#)

if(dr["field"] == DBNull.Value)
statement;
else
statement;

Which equates to (VB.NET)

If (dr("field") == DBNull.Value) Then
Else
End If

As suggested, you can also do this in the SQL statement if you want to

fill

with a string, as you are doing.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
> I'm using SQL Server 2000 and on my page, I'm simply creating a
> SQLDataReader and filling in Labels with the retrieved (single)

record. > However, how can I prevent from getting errors when a field is null?
>
>
> I've tried something like this (experimenting with IsDBNull):
> ================================================== =======
> If dtrData.IsDBNull("Address2") = True Then
> lblAddress2.Text = ""
> Else
> lblAddress2.Text = dtrData("Address2")
> End If
>
> And this....
>
> If IsNothing(dtrDatal("Address2")) = True Then
> lblAddress2.Text = ""
> Else
> lblAddress2.Text = dtrData("Address2")
> End If
> ================================================== ======
>
>
> ..and neither seem to work. Any suggestions? Thanks!
>
>
>
>

[francois]

Usually a NULL value means that the value has not been set and "waits" some
value in a later update statement later in the business logic or program
life.
While an empty string value means that the value is set to empty

let say u have a table with names (the exemple is stupid it is just for
exemple sake)

FirstName
MiddleName
LastName

- MiddleName to null means the middle name is not know yet. then a UI could
say to the application user something like "hey dude, ask your guy to let
you know about his middle name".
- MiddleName to "" (empty string) means that we know the guy does not have a
middle name (yes it happens I do not have any!). No need to ask for it !

My 2 cents, :)

Francois

"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in
message news:u3*************@tk2msftngp13.phx.gbl...

In some instances, it would be a sloppy hack. For example, I have seen
databases (God only knows why) that use both null and empty string (or
single char) to represent different conditions. ISNULL would not detect
between the two.

Detecting on the UI layer is a better option in some situations, while
detecting and "correcting" on the data layer is better in others.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"DalePres" <[e-mail]> wrote in message
news:u2**************@tk2msftngp13.phx.gbl...

I wouldn't call ISNULL a sloppy hack. There's a reason they built the
functionality into SQL. The advantage is that, if it's in a stored
procedure, any app that calls the stored proc gets the same handling and
gives a consistent result. Also, it is one place to change the code,

rather

than in every app.

Just my opinion.

Dale

"D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Modifying my Select would be a sloppy hack...seems like it would anyway.
I did get this to work:

If IsDBNull(dtrData("Address2")) Then
lblAddress2.Text = ""
Else
lblAddress2.Text = dtrData("Address2")
End If
Thanks guys.
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in message news:O5**************@TK2MSFTNGP11.phx.gbl...
> My code is normally more like so (C#)
>
> if(dr["field"] == DBNull.Value)
> statement;
> else
> statement;
>
> Which equates to (VB.NET)
>
> If (dr("field") == DBNull.Value) Then
> Else
> End If
>
> As suggested, you can also do this in the SQL statement if you want to fill
> with a string, as you are doing.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ************************************************** ******************** > Think Outside the Box!
> ************************************************** ******************** > "D. Shane Fowlkes" <sh**********@h-o-t-m-a-i-l.com> wrote in message
> news:%2****************@TK2MSFTNGP09.phx.gbl...
> > I'm using SQL Server 2000 and on my page, I'm simply creating a
> > SQLDataReader and filling in Labels with the retrieved (single)

record.

> > However, how can I prevent from getting errors when a field is null? > >
> >
> > I've tried something like this (experimenting with IsDBNull):
> > ================================================== =======
> > If dtrData.IsDBNull("Address2") = True Then
> > lblAddress2.Text = ""
> > Else
> > lblAddress2.Text = dtrData("Address2")
> > End If
> >
> > And this....
> >
> > If IsNothing(dtrDatal("Address2")) = True Then
> > lblAddress2.Text = ""
> > Else
> > lblAddress2.Text = dtrData("Address2")
> > End If
> > ================================================== ======
> >
> >
> > ..and neither seem to work. Any suggestions? Thanks!
> >
> >
> >
> >
>
>