Help formating time..

[dirt29]

I'm trying to pass a variable from a ASP page back into a DB call to see if both my title & date match up to a product.

Here's my SQL Statement:

set rs =db.execute("SELECT * FROM Calendar WHERE [Title] LIKE '" & replace(request("title"),"'","''") & "%' AND [EventDate] ='#" & request("EventDate") & "#' ORDER BY EventDate")

The first part of it for the title works fine, but I keep getting a data type mismatch on the date part. I added a # at each end, because when I tried this as a query against my access DB it wouldn't find the date otherwise. What am I screwingup here?

[russell]

get rid of the single quotes around your date. just use the #.

And date = #5/22/2006#

NOT

And date = '#5/22/2006#'

[dirt29]

Ok, I changed it...and what happens now is get the following:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '[Title] LIKE 'Broker Appraisal%' AND [EventDate] =#6#'.

/todaysmind/http/main_classes_list_view2.asp, line 25

If you notice, it has [EventDate] = #6#...Well the way I have this setup, is when it runs through the DB...it writes out the class title & eventdate as a parameter on a hyperlink, eg. <a href="class_view_2.asp?title=<%=title%>&eventdate=<%=eventdate%>">
<%=Title%></a>

So, when I hover over them, I see the values are getting passed with the link...but when I click I get whats above. And if I change months, i'll get 5 for may, 6 for june & 7 for july...so it looks like the #6# is the eventdate only containing the number of the month.

[russell]

try changing

<a href="class_view_2.asp?title=<%=title%>&eventdate=<%=eventdate%>">

to

<a href="class_view_2.asp?title=<%=title%>&eventdate=<%=Server.URLEncode(eventdate)%>">

[dirt29]

No luck...I get the same error, except with no number between the #'s

Scratch that, that worked...I must have done something wrong itially..Thank you.

[Terrorke]

set rs =db.execute("SELECT * FROM Calendar WHERE [Title] LIKE '" & replace(request("title"),"'","''") & "%' AND [EventDate] ='#" & request("EventDate") & "#' ORDER BY EventDate")

I think your mistake is within the requesting of your values. Are your trying to get the values of a form or a querystring?

Try something like this :

Form :

set rs =db.execute("SELECT * FROM Calendar WHERE [Title] LIKE '" & replace(request.form("title"),"'","''") & "%' AND [EventDate] ='#" & request.form("EventDate") & "#' ORDER BY EventDate")

or querystring :

set rs =db.execute("SELECT * FROM Calendar WHERE [Title] LIKE '" & replace(request.querystring ("title"),"'","''") & "%' AND [EventDate] ='#" & request.querystring ("EventDate") & "#' ORDER BY EventDate")

Maybe this helps.

Grtz