How do you catch a Database error?

[SYMBIO]

Hi guys!

i keep getting this datetime error.


Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting DATETIME from character string.

/internet/internet_news/search.asp, line 410


i dont wanna use no javascript to catch the entry the user makes. so how do i catch this error and say "oi you dumb idiot! stop putting letters into the date field!"

[raf]

befor you build the sql statement, you check the value from the date-field, to make sure that it is a date or a value that can be converted to a date. (Oi you dumb coder : letters in a datefield don't need to cause problems, as you can see below :-))

you can use this sort a code:


dim datevalue
datevalue = request.form("fieldname")
If IsDate(datevalue)= false then
response.write("You didn't fill in a valid date")
else
...

end if


You can add aditional messages of coarse, or supply a "back"-link or use a multiple purpose page ....

More info from the Miscrosoft Development Environnement-helpfunction:
"
Description
Returns a Boolean value indicating whether an expression can be converted to a date.
Syntax
IsDate(expression)
The expression argument can be any date expression or string expression recognizable as a date or time.

Remarks
IsDate returns True if the expression is a date or can be converted to a valid date; otherwise, it returns False. In Microsoft Windows, the range of valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the ranges vary among operating systems.
The following example uses the IsDate function to determine whether an expression can be converted to a date:

Dim MyDate, YourDate, NoDate, MyCheck
MyDate = "October 19, 1962": YourDate = #10/19/62#: NoDate = "Hello"
MyCheck = IsDate(MyDate) ' Returns True.
MyCheck = IsDate(YourDate) ' Returns True.
MyCheck = IsDate(NoDate) ' Returns False.

"

[whammy]

I usually use a combination of IsDate() and another Date-checking regular expression (since I prefer the MM/DD/YYYY format, usually, especially on my intranet application):


Function ValidDateFormat(str)
Dim vdfRegEx
Set vdfRegEx = New RegExp
vdfRegEx.Pattern = "^(\d{1,2})\/(\d{1,2})\/(\d{4})$"
ValidDateFormat = vdfRegEx.Test(str)
End Function

If ValidDateFormat(MyDate) = True AND IsDate(MyDate) Then
' Yay! It's good!
Else
' Boo! It's not good!
End If


That makes sure it's not only a valid date, but in the format you prefer. Of course you can use whatever format you want. :)

[raf]

whammy,

you don't need to wright your own function for the format checking. (I don't say it's better, but it's more Zen )Take a look at this simple code. It's the think i use (because i frequently run into date problems since my LCID displays dates a dd/mm/yyyy, buth asp sometimes flips them to mm/dd/yyyy when parsing the file (its weird, buth especially when combined with saving in SQL database, the format can change sometimes)

With this code, it doesn't matter witch format was used


dim startdd, startmm, startyyyy
startdd=Day(startdate)
startmm=Month(startdate)
startyyyy=Year(startdate)

startdate=startmm & "/" & startdd & "/" & startyyyy

[whammy]

I meant more for validating data that would be written to a database, not extracting a date from a database.

If I have any problems with that, I usually use the DatePart() function in SQL, but that does the same thing really... :D

[raf]

Hmm.

Differetnt options all around:
To display data pulled from a database in the desired order (mm/dd/yyyy) you best use the LCID that you want (or that you users want)


session.LCID=the-id-from-your-country


the code from my earlier post is what i use to build the date i'm goïng to write to my database (as value in 1 variable (last line) or as three separated variables (the most secure way)
The extra is that you can process dates like October19, 1962
and save them as 19/10/1962 or 10/19/1962 .

buth for all you americans out there, all this doesn't matter ...

[SYMBIO]

Thanks to all who replied!

I used the "IsDate" function, and it works pretty well.

Thank you all so much for your help.

[whammy]

Hmm, thanks for sharing that, at any rate, raf. :)

If I need to pull a date for Europeans, I'll probably use a long date format... like:

December 25, 2002

To avoid confusion.

;)