Date Bug of Death - This is a Tough One

[Michael42]

Hello, I am using ASP with IIS 5. I have a really weird date related bug with an Access 97 database.

From a form I build a recordset based on my SQL string based on the users form selections. I detect and build my SQL statment based on the field type.

For instance, if I select "ReceivedDate" from my combo my code knows this is a date field and builds the SQL using the appropriate syntax (#). Example:

sSQL = "SELECT * FROM Requests WHERE " & sSearchField & " = #" & sSearchString & "#"

The above works when entering a valid date. If I enter giberish (anything not a date) my code blows up eventually timingout the page. In my ErrorTrap routines either I am getting an Err.Number = 0 or nothing at all.

I am so baffled why I cannot trap for the error in my ASP and handle it.

My DB Connection works but it seems I cannot build the Recordset for the SQL (oRS.Open sSQL, oConn)

What do ya think - do you have a clue???

Thanks for your time,

Michael

 

[lobstah]

if it blows up on an invalid date, why not check for a valid date before building the sql statement and posting your own custom error message (possibly re-posting the form asking to input a valid date) rather than letting the script die?

 

[Michael42]

>> why not check for a valid date

Yes, will do. I am just not sure why my RS blows up without leting me trap for it more gracefully. Very weird.

Thanks

 

[Michael42]

Hey, just thought I'd pass along a workaround. This is not perfect but avoids the show stopping error:

'Redundant Date Error Trap
if not isDate(sSearchString) then
Message("Invalid Date Field Entered&quot
Response.End
end if

Function Message(sMessage)
'Purpose: Simple Output To Page
'Usage: Message("What ever you want to say goes here&quot
Response.Write &quot;<center>&quot;
Response.Write &quot;<table cellpadding=2 cellspacing=0 width=750 border=1><tr><td bgcolor=#eeeeee align=center>&quot;
Response.Write &quot;<table cellpadding=2 cellspacing=0 width=100% border=0>&quot;
Response.Write &quot; <tr>&quot;
Response.Write &quot; <td align=center><font size=3 color=red><b>&quot; & sMessage & &quot;</b></font></td>&quot;
Response.Write &quot; </tr>&quot;
Response.Write &quot;</table>&quot;
Response.Write &quot;</td></tr></table>&quot;
Response.Write &quot;</center>&quot;
End Function

 

[codestorm]

From memory Javascript handles code and database within the same error catching mechanism, but VBScript handles ADO errors separately from VBS errors. <insert witticism here>
codestorm