Again today, working in ASP backing onto Oracle I had another 80020009 error. As I previously stated, the error code was as follows:
error '80020009'Exception occurred./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:
SELECT completeddate,CASEWHEN (completeddate > SYSDATE) THEN'TRUE'ELSE'FALSE'END as FutureEventFROM tbleventsWHERE 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.
I ran into this issue using MS-SQL 2000. It seamed to happen with text and datetime (I would guess with image as well). I converted the data in my sql queries:
SELECT CONVERT(varchar(50),datefieldname) AS thedate FROM tbl_name;
Why would you ‘guess’ that it would also happen to an image as well?
A friend of mine has pointed out that at some point, someone might have manually edited the field of this row (possible I guess, unlikely but possible). At which point, some how they might have inserted invalid/unknown content into the field.
My friend tells me that with an Oracle date, that they store date plus time and zone information all in one. If you insert a plain date (ie dd/mm/yyyy) it will set all the other information to a default, which would get stripped out again if you selected the field back. That makes sense to me, as every other field was displaying through ASP as a plain date. So, maybe the person that edited this field appended additional information (ie time/timezone), it was recognised by Oracle (hence allowed the update/insert) yet isn’t recognised by ASP (hence the variable sub-type coming out as VBObject, instead of VBDate).
Have you heard of anyone else having a similar problem?
We have this same issue with an ASP application on Oracle 9.2. The strange thing is, however, that the error does not occur every time. Sometimes the page shows up correctly after a refresh.
The query is like this
select col1, col2, coldate ….
The recordset contains over 12000 records, bof and eof are false, rs(“col1”) and rs(“col2”) can be printed, but Response.write(rs(“coldate”)) fails with error 80020009.
Did you ever solve this problem ?
Guus,
No I didn’t find a specific solution to the problem.
Have you checked the contents of the field that won’t display, to make sure it isn’t a malformed date? I was caught by that some time ago. Also, when you say a refresh sometimes fixes it, is the exact same resultset coming back (ie, the row that is erroring is definitely being displayed)?
Al.
The VarType of rs(“coldate”) was also 9 in our case when the 0x8002009 error occurs. With the refresh the exact same recordset was returned.
CDate(rs(“coldate”)) fails as well.
Finally, I have found a workaround.
select col1, col2, to_char(coldate, ‘YYYYMMDD’) coldate …
d = DateSerial(Mid(rs(“coldate”), 1,4), Mid(rs(“coldate”),5,2), Mid(rs(“coldate”), 7,2))
But I still not found out what the cause is of the 0x80020009 problem.
Oracle Windows Client tools 9.2.0.10 and Oracle Database 9.2.0.5.0
Guus,
We’re running IIS6 with Oracle 10g on the backend, so it isn’t limited to a 9i error. On the field in question, did you try casting coldate to a valid date format (YYYY-MM-DD) and checking it? If that worked, it’d save you having to worry about all of the DateSerial/Mid function calls from VBScript.
Al.
Hi Al,
I did try to_date(coldate, ‘YYYY-MM-DD’), but with no success. The error only appears in a customer’s production environment, so it is hard to keep on experimenting.
More details:
We’re connecting with the Oracle Provider for OLE and the MDAC version is 2.80.1022.3
Guus
try with this if u r using asp programming
to_date(‘”&Intdt&”‘,’DD/MM/YYYY’)
Intdt being a variable
When you are dealing with dates and ASP make sure your SQL string is properly constructed. You cannot just insert date into an Oracle table by using the method Gloria mentioned above, but with some changes.
For example if want to insert date into your Oracle table, you can always use the following script:
udate=”‘” & Date & “‘”
strSQL = “Insert into MyORAtable>”_
& “(Date_Column)”_
& “Values(“_
& “To_Date(” & udate & “,” & “‘” & “MM-DD-YYYY” & “‘” & “)” & “)”
adoCon.Execute(strSQL)
I have an error when I add “‘” after URL:
error ‘80020009’
/aaaa.asp, line xxx
Can guest can attack my website by this error?