Again today, working in ASP backing onto Oracle I had another 80020009 error. As I previously stated, the error code was as follows:
/somefile.asp, line <number>
This time I immediately went to solve the problem based on my previous solution. As it turned out, the date was incorrect. Once corrected however, the error did not go away. I preceded to set the date field back to NULL and then back to a valid date; which was being stored in dd/mm/yyyy format, still nothing.
At this point I started hunting down the possible cause. I couldn’t display the date in that field at all through ASP, yet the same field in different tuples were just fine. I could use an isNull() on the date and it reported that there was in fact ‘something’ in the field. If I tried a Len(), I would get an error. The sub-type of the field in the recordset reported by VarType() for all other tuples was in fact 7 (VBDate), however the one in error was a 9 (VBObject).
At this point I was wondering whether Oracle had some how managed to store a damaged date, so I wrote a small query using SQL to do a comparison on the field against SYSDATE:
WHEN (completeddate > SYSDATE) THEN
END as FutureEvent
WHERE eventid = 49401;
As expected, Oracle returned the correct values in every case. Which left me with no place to really go forward from here. To put the web application back into a working state, I used the VarType of the field to test whether I should or shouldn’t attempt to display the value; dirty hack but a working solution for the moment.
This leads me to believe, that some how ASP/ADO/something is some how reporting/munging the value of that field for some reason. I still don’t quite know how or why; all I know at this point is that it is frustrating and consumed my time on something that should have otherwise worked perfectly.