Converting DBNull.Value

  • Thread starter Thread starter João Roberto Alonso
  • Start date Start date
J

João Roberto Alonso

Hi

I wanto to know if there is a way to convert a DBNull.Value to Decimal and
returns to me Decimal.Zero?

Examples

COLUMN1 = NULL from DB

Ex1.: cstr((dt.rows(0).item("COLUMN1"))

returns Error

Ex2.: dt.rows(0).item("COLUMN1").toString

returns String.Empty

Ex3.: cdec((dt.rows(0).item("COLUMN1"))

returns Error

Is there a way to return to me Decimal.Zero??

I use this...

cdec((iif(dt.rows(0).item("COLUMN1") is dbnull.value,
decimal.zero,dt.rows(0).item("COLUMN1") ) )
 
Your way works, the only other way I can think of is using IsNull(Column1,
0) in your SQL Statement, which may or may not be the way to go.

HTH,

Bill
 
João Roberto Alonso said:
Hi

I wanto to know if there is a way to convert a DBNull.Value to
Decimal and returns to me Decimal.Zero?

Examples

COLUMN1 = NULL from DB

Ex1.: cstr((dt.rows(0).item("COLUMN1"))

returns Error

Ex2.: dt.rows(0).item("COLUMN1").toString

returns String.Empty

Ex3.: cdec((dt.rows(0).item("COLUMN1"))

returns Error

Is there a way to return to me Decimal.Zero??

I use this...

cdec((iif(dt.rows(0).item("COLUMN1") is dbnull.value,
decimal.zero,dt.rows(0).item("COLUMN1") ) )
Click to expand...


If you don't distinguish between Null and zero, why do you allow Null values
in the database at all?

Apart from your current working approach, the only shorter version I can
think of is a function performing the replacement if necessary:

Public Function DecimalNull2Zero(ByVal Value As Object) As Decimal
If TypeOf Value Is DBNull Then Return 0D
Return DirectCast(Value, Decimal)
End Function

Call:
dim d as decimal
d = DecimalNull2Zero(dt.rows(0).item("COLUMN1"))
 
i don't think there's an option or function in .net that will handle it for
you. you could create your own to handle various types with an option to
return a default value like this psuedo code:

function getDbValue(byval column as field, byval type as db.datatypes, byval
default as string) as object
on error resume next
if not isnull(column.value) return column.value
return ctype(default, type)
end function

*remember the above i *psuedo* code...b4 i get a million flames about the
details.
another route would be to overload the above function, removing the "type"
argument and setting the "default" to int16, int32, long, string, boolean,
etc. for each type of data you need to deal w/...i think the above would
save a lot of proc time when resolving which version vb should use and,
you'd be saving a ton of space in your code project.

the other option, if you have access and aproval, is to handle it w/n the
db.

queries, views, etc. should use:

sql server: isnull(myColumn, dbo.Zero)
oracle: nvl(myColumn, 0)

or in the table def, make the column "DEFAULT 0".

hth,

steve
 
Back
Top