469,647 Members | 1,800 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

Detecting "Null" database fields

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!


Nov 18 '05 #1
9 1812
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!

Nov 18 '05 #2
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!

Nov 18 '05 #3
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!

Nov 18 '05 #4
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!


Nov 18 '05 #5
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!



Nov 18 '05 #6
No offense. A sloppy hack for me maybe....I just wanted to learn what I was
doing wrong first.
"DalePres" <don-t-spa-m-me@lea-ve-me-a-lone--.com> 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!
>
>
>
>



Nov 18 '05 #7
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!



Nov 18 '05 #8
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" <don-t-spa-m-me@lea-ve-me-a-lone--.com> 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!
>
>
>
>



Nov 18 '05 #9
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" <don-t-spa-m-me@lea-ve-me-a-lone--.com> 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!
> >
> >
> >
> >
>
>



Nov 18 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by hjyn | last post: by
19 posts views Thread by Gav | last post: by
3 posts views Thread by Karunakararao | last post: by
8 posts views Thread by craigkenisston | last post: by
2 posts views Thread by Anastasios Papadopoulos | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.