handle dbnull

[buddyel]

When trying to retrieve a record from a database and set variables equal to the fields, i get "Cast from DBNull to Integer is not valid" on certain fields that contain no value. Does anyone know of a good way to handle this, so this doesnt happen? Thanks in advance...

 

[JohnEfford]

I have only been working with Char and VarChar fields, but if you have a field that could contain a Null value then.

If Not IsDbNull(DbField) then 'Ensure not Null
VarName = DbField 'Do assignment
End If

Should solve your problem.

Regards
John

 

[buddyel]

I receieve the message on more than one field. Do i have to do a if statement for each each database field i pull. i have about 30, that seems like a lot of if statements.

 

[JohnEfford]

One method I have used is to test for the null value in the select statement and convert it into an appropraite value. EG

Select (Case MayBeNull When Null "" Else MayBeNull)

You are still doing the evaluations, but moving them into the select.

Another method that I have just found, which seems like it could be a lot easier is by Setting

DsDataSet.Tables(0).Columns.Item(1).AllowDBNull()

For each column in the table that can accept Nulls.

The advantage of the first method is that you do not get (Null) displayed when you use the data grid control (if anyone knows how to stop please tell)

Regards
John

 

[netangel]

Just to optimize John's select statment, instead of:
Select (Case MayBeNull When Null "" Else MayBeNull)
I always use:
ISNULL(MayBeNull, 0) -> integer, long, ...
ISNULL(MayBeNull, "&quot -> char, varchar, ...

When I know null values may reach my code, I use:
MyVar = Iif(IsDbNull(MyDt("MayBeNull&quot), 0, MyDt("MayBeNull&quot) -> integer, long, ...
MyVar = Iif(IsDbNull(MyDt("MayBeNull&quot), "", MyDt("MayBeNull&quot) -> char, varchar, ...

When I use the values on DataTables, I use the same ideia:
Iif(IsDbNull(DataBinder.Eval(Container, "DataItem.MayBeNull&quot), "", DataBinder.Eval(Container, "DataItem.MayBeNull&quot)
NetAngel

 

[myronjbarrett]

netangel,

I tried your IIF method but I still get a DBNull error. Here's what my code looks like:

ldblInterest += IIf(DataSetPayments.LoanPayments(0).IsUnpaidInterestNull, 0, DataSetPayments.LoanPayments(0).UnpaidInterest)

From what I've read, the reason I'm getting the error is because VB evaluates all parts of the statement before executing it. Since the "False" part of the statement evaluates to an error it throws an exception. Maybe I'm doing something wrong if you aren't getting the same error with a similar statement. What do you think?

Myron

 

[chiph]

You shouldn't be having the full-evaluation problem checking for NULLs - one of the changes in .NET was to go to short-circuit evaluation for performance reasons. But then again, this might be one of the things they put back in after protests from the VB6 community

If I recall correctly, IsDbNull is a shared (static) method of the DataReader object, and you'd use it like:

Interest += IIf(MyDataReader.IsDbNull(4), 0, MyDataReader.GetInt32(4))

which will get you the contents of the 4th column in your result set.

Chip H.

 

[mattyboy2000]

If you use the 'ToString' method of the Item then it will return "" if the cell is null:

DsDataSet.Tables(0).Rows(0).Item(1).ToString()

Hope it helps

Matt