Friday, March 30, 2012

PadLeft function on Null value

Can you use a padleft function on a null value of a field?

I get this when i try to padleft on a result from a db. This happens only when one of the fields are null.

Public member 'PadLeft' on type 'DBNull' not found.

You need handle the NULL from the database before you pass on to your PadLeft function in your program.

Limno

|||

How would I do that?

Can i set the default value of that field to be an empty string? if so how is that done.

thanks,

|||

This should work for ya in VB.NET

CTYPE("" & {Whatever you are trying to padleft that might be DBNULL} & "",string).padleft( ... )

Basically concatenating a string with DBNULL will return the string in vb.net, so it's a quick and easy way to replace fields with dbnull with an empty string. Then I'm casting the result to a string although it isn't necessary, it helps VS2005 to understand the datatype so that intellisense still works (And you can type .padleft, etc). It'll still work without it, but no intellisense (Atleast it didn't in VS2003, and old habits die hard).

|||

I would handle it in a helper function in your application.

Like:

Function fixNull(ByVal sItemAsObject)AsString

If sItemIs DBNull.ValueThen

fixNull = "-"

Else

fixNull = sItem

EndIf

EndFunction

In your code: PadLeft(fixNull(yourInterestedField...))

Or

you can handle this in the SQL statement. For example:

SELECT CASE WHEN col1isnull THEN'-' ELSE col1 ENDas col1FROM testtable

You can replace '-' with an empty string''(two single quote) if you like.

you can process this field as usaual.

Limno

No comments:

Post a Comment