TDU TDU - 1 month ago 12
Vb.net Question

VB.Net - Error in SQLDataReader and "Conversion from type 'DBNULL' to type 'Date' is not valid

I'm new to VB.Net, and have to do a "crash get 'er done" project using VB.Net. I've gotten a ways into it (in just 4 days), but am running into a serious issue at this point.

The SQL server database has a few date columns in it, and some are NULL. I'm trying to build up a gridcontrol, and have a custom class to handle the data for each "row".

I have a SQLDataReader set up:

Using sdr As SqlDataReader = cmd.ExecuteReader()


The error is tripping on:

' this is an abbreviated statement of the original - many more values being passed
appsList.Add(New PromiseAppClass(sdr("PrimID"), sdr("lastname"),
sdr("firstname"), sdr("middleinitial"), sdr("othernames"),
sdr("ssn"), sdr("date1"), sdr("date2")))


date1 and date2 are NULL in the DB, so the reader is failing attempting to convert to make the call to create the object (I'm assuming - VB isn't too explicit - to me - in detailing just WHERE the problem is here). Both of those two items are set in the PromiseAppClass as Dates, with the normal getters/setters.

How to resolve? Is there a method to code the particular variable in the class so that it can be tested/set there? Or a method to convert a NULL date in the above statement into something acceptable? I don't want to use a string, and not so sure I won't run into the same kind of problem, but now it can't convert NULL to string.

Answer

Try replacing sdr("date1") by

 If(sdr("date1") is nothing,nothing,sdr("date1"))

Or

If(IsDbNull(sdr("date1")),nothing,sdr("date1"))