dates cause asp page to fail with ole db error

[bookouri]

Ive got a page that does a select statement against an oracle database. The page works fine until it hits a date that the user has mistakenly put in as 0007, 0r 0068 instead of 1907, 2007, or 1968. Im using something like:

strsql = "select mydate from mytable"
set rstsearch=cnnsearch.execute(strsql)

<td>
<%=rstsearch.fields("mydate"),value %>
</td?

can anybody tell me why these dates fail? The may be incorrect data entry, but they are actual dates. A person might not have been born in 01/01/0076, but it IS a valid date.

thanks for any comments

 

[Chopstik]

What exactly is your error message and where is it hitting the error? Chances are, you should be preventing your users from entering in bad dates in the first place which would prevent these errors...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[DotNetGnat]

if isdate(rstsearch.fields("mydate").value) then
'its a valid date
else
'it is not a valid date
end if

but as chopstik pointed out, do some validation at the user input level...

-DNG

 

[bookouri]

but, they ARE valid dates..?? If a book was published on 1/1/2007 and the user put in 1/1/0007. The year 7 date is perfectly valid, it just happens to be the wrong date. There WAS a year 7 and there was a Jan 1 of that year. Yes, I can do some validation, but what if this database required entry of dates like 0007? If I wanted to have a database of Papal coronations or some other historical events, and I wanted to store dates that are pre year 1000, I wouldnt be able to do it. I guess it just seemed odd to me that oracle and/or oledb would behave like this.

 

[DotNetGnat]

then why dont you make the field in the database to be of string/text datatype instead of the date datatype...

-DNG

 

[Chopstik]

Is your problem on the database side or on the ASP side? My understanding of your original post was that there was an ASP issue but now it seems to be on the database side. If so, then you need to handle this issue in one of the Oracle forums. If this is an ASP issue, then you will need to give the error message that you are encountering and what line it is hitting on in order for someone to help you track it down.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill