473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

working with SQL null values....

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.Equals(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 ?
Jul 12 '08 #1
8 1452
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.Equals(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("path") Then...

You may want to check for 0 length as well.
Jul 12 '08 #2
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.Equals(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("path") Then...

You may want to check for 0 length as well.
Jul 14 '08 #3

"SurturZ" <su*****@newsgroup.nospamwrote in message
news:D0**********************************@microsof t.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.Equals(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("path") 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

Jul 14 '08 #4
"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

Jul 14 '08 #5

"Armin Zingler" <az*******@freenet.dewrote in message
news:%2****************@TK2MSFTNGP04.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

Jul 14 '08 #6
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

Jul 15 '08 #7
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.
Jul 15 '08 #8
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.
Jul 16 '08 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Andrew DeFaria | last post by:
I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB...
9
by: Thomas R. Hummel | last post by:
Hello, I am importing data that lists rates for particular coverages for a particular period of time. Unfortunately, the data source isn't very clean. I've come up with some rules that I think...
2
by: Athanasius | last post by:
Could someone shed some light as to why the following setTimeout function will not work on the Mac IE5.2? It does however work on PC(Forefox,Netscape,IE) & Mac(Safari,Firefox). Here is the script,...
7
by: Mikael Östberg | last post by:
Hello all! I have been working on a project for some time now and yesterday, my debugger stopped working. It is a class library which I run from a Win32 test app, so no IIS involved at this...
13
by: Lee | last post by:
Hello All, First of all I would like to say thank you for all of the help I have received here. I have been teaching myself Access for about 4 years now and I've always been able to find a...
2
by: George G. | last post by:
Hi there, I hope I am posting to the correct news group. What would be a good way to deal with null database values using ADO.NET. My SQL queries sometimes return null values when I have left...
4
by: kamin | last post by:
I have this query select created_date, tyg_aging_due_dates.object_type, tyg_aging_due_dates.due_date from #tyg_aging_service_metrics right join tyg_aging_due_dates
17
by: erikcw | last post by:
Hi all, I'm trying to run the following query: amember_db = MySQLdb.connect(host="localhost", user="**********", passwd="*****", db="*******") # create a cursor self.amember_cursor =...
10
by: Stephen.Schoenberger | last post by:
Hello, Sorry if this is not "exactly" a C topic but I thought this would be the best place to start. I need some guidance on working with bitmap images in ANSI C. I need to "read" in a bitmap...
5
by: mukeshrasm | last post by:
Hi I am using AJAX to display the value in selection/list box. this code is working fine in Firefox Mozila browser but it is not working in Internet Explorer so please tell me how this will work...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.