Whoops - Oracle DB doesn't like Nulls

[CraigBest]

Can someone help me, please?

I have a VB.NET app I'm writing that communicates with an Oracle 9i DB. I am trying to write records into a table, and in that table there is a date field. I'm getting an type mismatch error when it tries to plug a null (no value for Date) into the record.

How can I avoid this error? What can I send to the table that won't be interpreted as an incorrect date, and will allow me to use the as written code for updating the table?

Can I send a blank string ("") in place of the null? I don't think sending a 0 (zero) will work properly.

Thanks for any help you might be able to provide.




CraigHartz

 

[engineer2100]

Craig,

Did you check the ADO.NET mapping of DATE field onto Oracle's? See if there is a specific way to handle them.

Also if you are storing the date in a Character field and then passing it to Oracle, dont forget to cast it either using .NET's conversion function or oracle's to_date function.

HTH

Engi

 

[CraigBest]

Engi,

Thanks but I spoke too soon. I thought the problem was writing the data out to the table, turns out after more careful investigation the problem is my ODBC.Datareader that's choking on a type conversion from DBNull to Date.

But I'll print out your suggestion and keep it nearby when I get to actually writing data out to the DB, because I'm sure there will be an issue!

Thanks


CraigHartz

 

[SantaMufasa]

Craig,

If "the problem is my ODBC.Datareader that's choking on a type conversion from DBNull to Date," then you can try some variation of this successful test that converts a standard Oracle NULL to an Oracle NULL Date:

SQL> select to_date(null,'dd mon yyyy') from dual;

TO_DATE(N
---------

Let us know if this provides some insight.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]