ODBC DataReader choking on DBNull to Date conversion

[CraigBest]

Hello there...

I have an ODBC DataReader connected to an Oracle DB. It seems to work all right most of the time, but in one case I need to read data from a table that has null values in a Date field. When the DataReader hits that, I'm getting a conversion error saying conversion from type DBNull to type 'Date' is not valid.

The SQL string I'm using to get the data is quite simple, "SELECT * FROM USERS". Is there some way I can structure this to ignore the nulls, or present them some other way (such as empty strings, or a literal I can read and work around?

Thanks for any help you can offer.



CraigHartz

 

[Qik3Coder]

for starters, any SQL "guy" is going to choke you (possibly literally) for using a select *.

You should be using
"Select column1, column2, datecolumn3 from users"

To avoid the datetime error, try this:

Select
case when datecolumn is null then '' else datecolumn end as 'datecolumn'
from users

-The answer to your problem may not be the answer to your question.

 

[CraigBest]

Thanks, I tried something similar (NVL function in MSQL) that solved the issue.


CraigHartz

 

[jshurst]

Or after you get data from the database you can write it out as a string, like...

dataset(0).table(0).rows(0).item(0).ToString()

It will write out a "" if it null, which still won't convert to a date (such as Dim d as Date = dataset(0).table(0).rows(0).item(0).ToString), but is useful to know ;-)

J

Might've missed some plurals in the code, can't remember if it should "table" or "tables" (etc)...