NULL value in query results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

NULL value in query results

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-01 : 15:46:12

Hi, I am using VB and SQL.

When I make a query, I get records with null values.
The thing is, when I try to display the null value in
a message box, or edit box I get an error because it is
not a correct data type.

My question is, how do you guys deal with this problem.
I have considered putting the values into a function that
changes a null to an empty string but there must be a better way.

Is there a way to have Nulls converted to empty strings when
the query is produced?

Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-01 : 15:51:23

ISNULL(column,'')

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-01 : 16:03:07

Hi, I tried it in query analyser and it worked fine. thanks.

I tried it within my sql code though and the compiler seems to
thing that ISNULL(Customer.FirstName,'') is the column name.

I.e

sMyQuery = "SELECT Audit.*, ISNULL(Customer.FirstName,'')
FROM Audit LEFT OUTER JOIN Customer
ON (Customer.RecordID = Audit.CustomerID)"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-01 : 16:09:31

sMyQuery = "SELECT Audit.*, ISNULL(Customer.FirstName,'') AS FirstName
FROM Audit LEFT OUTER JOIN Customer
ON (Customer.RecordID = Audit.CustomerID)"

//Also, you shouldn't use "*". You need to list out your columns.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-01 : 16:17:25

Damit, you are good. thank you very much.

By the way, I suppose I could also use

on error resume next in my VB code

I.e

On error resume next
MsgBox ors.fields("Customer.FirstName")

Do you see any problems with this? Is this a valid way to
avoid the null problem?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-01 : 18:50:04

It's not really a "null problem" so much as it is understanding what NULL is, which is unknown. I don't if that's a valid way to handle it, because I don't know what you're trying to handle. I wouldn't think your FirstName should ever be NULL in the first place, which sounds like a validation problem. ??

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources