Error in ODBC

[ptran]

Help,

I get the following error with the code below, this is an ASP file with MS-ACCESS database. Any one point out to me what I did wrong here?

Thank in advance...

ERROR!
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4.

CODE!
<%
' The exsisting values, that can be viewed, updated and deleted
Query = "select EVENT_ID, EVENT_NAME, Years=DateDiff(year, DATE_TIME, '"
Query = Query & FormatDateTimeCn(dtToday, 4)
Query = Query & "') from CALENDAR where EVENT_TYPE=8 and NAME='"
Query = Query & Session("UserName")
Query = Query & "' and datepart(month, DATE_TIME)="
Query = Query & Month(dtToday)
Query = Query & " and datepart(day, DATE_TIME)="
Query = Query & Day(dtToday)
set Check = dbConnection.Execute(Query)
Do While Not Check.EOF
Response.Write("<a href=""delete_special.asp?event_id=" & trim(Check("EVENT_ID")) & """>")
Response.Write("<img src=""button_delete_large.gif"" width=8 height=8 border=0 alt=Delete>")
Response.Write("</a>" & vbCrLf)
Response.Write(trim(Check("EVENT_NAME")) & " " & trim(Check("Years")) & "Year<br>")
Check.MoveNext
Loop
%>

[HarryW]

It's a bit hard to tell what exactly will happen when you execute that SQL statement, but what I do if I have a problem eith the SQL is comment out the execute statement and add a 'response.write query' line instead, so that you can see exactly what the string contains.

I'm not quite sure what the 'years=datediff(year, DATE_TIME.. etc )' bit means, I'm no SQL expert but is that an assignmen or err.. anyway, I know I'm meant to be helping you but give me a clue what that means will ya? I've never seen a select statement like that.

[Ianpbaker]

Your problem is when you are comparing the dates In the Where clause try changing the following

Query = Query & "' and datepart(month, DATE_TIME)="
Query = Query & Month(dtToday)
Query = Query & " and datepart(day, DATE_TIME)="
Query = Query & Day(dtToday)

To

Query = Query & "' and datepart(month, DATE_TIME)=#"
Query = Query & Month(dtToday) & "#"
Query = Query & " and datepart(day, DATE_TIME)=#"
Query = Query & Day(dtToday) & "#"

In access dates that are not in a datepart function need to be enclosed in #'s Or '.

This shouls sort out your problem

[Edited by Ianpbaker on 04-25-2000 at 02:45 PM]

[HarryW]

So what does this bit do ?

"Years=DateDiff(year, DATE_TIME, '" & FormatDateTimeCn(dtToday, 4) & "')"

[Ianpbaker]

Harry
with your question the 'years=datediff(year, DATE_TIME.. etc )' Bit. It returns a column called years and will return in years the difference between today and whatever is in the field DATE_TIME. You can use the 'anystring'= IN SQL to combine fields,mathmatic functions and other things into one Field that the query returns and is not in the database.

Hope this helps

Ian

[ptran]

Hi Harry,

Datediff() is part of ASP function:
DateDiff()
FUNCTION: Returns the number of intervals between two dates.
SYNTAX: DateDiff(timeinterval,date1,date2 [, firstdayofweek [, firstweekofyear]])
ARGUMENTS: timeinterval is the time interval to add; date is a valid date expression; firstdayofweek and firstweekofyear are optional values to specify the first day of the week and first week of year.
EXAMPLE: <%
fromDate = #8/4/99#
toDate = #1/1/2000#
response.write "There are " & _
DateDiff("d",fromDate,toDate) & _
" days to millenium from 8/4/99."
%>
RESULT: There are 150 days to millenium from 8/4/99.

FormatDateTimeCn(dtToday, 4) is the javafunction that run at server to get the current date.

[ptran]

Lanpbaker,

I get the following error when I put the '#' in:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'EVENT_TYPE=8 and NAME='' and datepart(month, DATE_TIME)=#4# and datepart(day, DATE_TIME)=#25#'.

Any idea?

[Ianpbaker]

Sorry ptran I am being completly stupid

What you had earlier In the where clause was correct. Have you set your ASP language to javascript or Have you left it as it is (The default is VBSCRIPT). If it is default replace the 'FormatDateTimeCn(dtToday, 4)' to now() which is the vbscript equivelent.

This could be your problem.