# cannot compare dates with ASP and Access backend

Discussion in 'ASP General' started by Nick B, Oct 18, 2005.

1. ### Nick BGuest

Help! :{

I have a simple select query in an Access database that has three
parameters. Two are text, one is a date. If I run my query in Access, it
prompts for the date, and two text parameters, I enter them, and they work
fine. However, if I try to run the same thing in ASP, I get an error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
expression.

The code I am using is:
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adVarChar = 200
const adDate = 7
dim cmd
set cmd = server.createobject("adodb.command")
with cmd
.ActiveConnection = "....dsn"
.CommandText = "qryName"
.CommandType = adCmdStoredProc
.Parameters.Append
..CreateParameter("strFName",adVarChar,adParamInput,40,sFirstName)
.Parameters.Append
..CreateParameter("strLName",adVarChar,adParamInput,40,sLastName)
.Parameters.Append
..CreateParameter("dteDate",adDate,adParamInput,255,cDate(tmpDate))
set rsSched = .execute
end with

Now, if I go into the query and set the date criteria to be:
[dteDate]
then it works fine. However, if I set my criteria to be:
>=[dteDate]

then it errors out.

I originally had this as an adodb connection with a SQL string,
unfortunately, some of the last names have apostrophes, so this was the only
way I figured I could do it.

Any ideas?

Thanks!
Nick B, Oct 18, 2005

2. ### Bob Barrows [MVP]Guest

Nick B wrote:
> Help! :{
>
> I have a simple select query in an Access database that has three
> parameters. Two are text, one is a date. If I run my query in Access,
> it prompts for the date, and two text parameters, I enter them, and
> they work fine. However, if I try to run the same thing in ASP, I get
> an error: Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
>
> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
> criteria expression.
>
> The code I am using is:
> Const adCmdStoredProc = &H0004
> Const adParamInput = &H0001
> Const adVarChar = 200
> const adDate = 7
> dim cmd
> set cmd = server.createobject("adodb.command")
> with cmd
> .ActiveConnection = "....dsn"
> .CommandText = "qryName"
> .CommandType = adCmdStoredProc
> .Parameters.Append
> .CreateParameter("strFName",adVarChar,adParamInput,40,sFirstName)
> .Parameters.Append
> .CreateParameter("strLName",adVarChar,adParamInput,40,sLastName)
> .Parameters.Append
> .CreateParameter("dteDate",adDate,adParamInput,255,cDate(tmpDate))
> set rsSched = .execute
> end with
>
> Now, if I go into the query and set the date criteria to be:
> [dteDate]
> then it works fine. However, if I set my criteria to be:
>> =[dteDate]

> then it errors out.
>
> I originally had this as an adodb connection with a SQL string,
> unfortunately, some of the last names have apostrophes, so this was
> the only way I figured I could do it.
>
>

Four things:
1) Stop using the obsolete odbc driver and use the native Jet OLE DB
provider instead.
http://www.aspfaq.com/show.asp?id=2126
2) Stop using an implicit connection. Explicitly create an open a Connection
object. Using implicit connections can disable connection
pooling.
3) Use adDBTimeStamp instead of adDate
4) Instead of the Command object, try this:

Set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & "p:\ath\to\database.mdb"
set rs=createobject("adodb.recordset")
cn.qryName sFirstName,sLastName,cDate(tmpDate), rs
if not rs.eof then
...

If that raises the same error, show us how to recreate the error (table
definition and the sql statement)

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Bob Barrows [MVP], Oct 18, 2005

3. ### Nick BGuest

Thanks Bob. I do not think I can use the connection string as there are
single apostrophe's in the lname field. This is giving me errors. That is
whay I thought I had to use the command object rather than a connection.

"Bob Barrows [MVP]" wrote:
> >
> >

> Four things:
> 1) Stop using the obsolete odbc driver and use the native Jet OLE DB
> provider instead.
> http://www.aspfaq.com/show.asp?id=2126
> 2) Stop using an implicit connection. Explicitly create an open a Connection
> object. Using implicit connections can disable connection
> pooling.
> 3) Use adDBTimeStamp instead of adDate
> 4) Instead of the Command object, try this:
>
> Set cn=createobject("adodb.connection")
> cn.open "provider=microsoft.jet.oledb.4.0;" & _
> "data source=" & "p:\ath\to\database.mdb"
> set rs=createobject("adodb.recordset")
> cn.qryName sFirstName,sLastName,cDate(tmpDate), rs
> if not rs.eof then
> ...
>
> If that raises the same error, show us how to recreate the error (table
> definition and the sql statement)
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>
Nick B, Oct 21, 2005
4. ### Bob Barrows [MVP]Guest

Try it - it will work fine. Here's some more posts I've made about using
this technique:
http://groups.google.com/groups?hl=...=1&selm=

http://groups.google.com/groups?hl=...=1&selm=ukS$6S$

http://www.google.com/groups?selm=&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

Did you change adDate to adDBTimestamp as I suggested? Did you get the same
error?

Bob Barrows
Nick B wrote:
> Thanks Bob. I do not think I can use the connection string as there
> are single apostrophe's in the lname field. This is giving me errors.
> That is whay I thought I had to use the command object rather than a
> connection.
>
> "Bob Barrows [MVP]" wrote:
>>>
>>>

>> Four things:
>> 1) Stop using the obsolete odbc driver and use the native Jet OLE DB
>> provider instead.
>> http://www.aspfaq.com/show.asp?id=2126
>> 2) Stop using an implicit connection. Explicitly create an open a
>> Connection object. Using implicit connections can
>> disable connection pooling.
>> 3) Use adDBTimeStamp instead of adDate
>> 4) Instead of the Command object, try this:
>>
>> Set cn=createobject("adodb.connection")
>> cn.open "provider=microsoft.jet.oledb.4.0;" & _
>> "data source=" & "p:\ath\to\database.mdb"
>> set rs=createobject("adodb.recordset")
>> cn.qryName sFirstName,sLastName,cDate(tmpDate), rs
>> if not rs.eof then
>> ...
>>
>> If that raises the same error, show us how to recreate the error
>> (table definition and the sql statement)
>>
>> Bob Barrows
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Bob Barrows [MVP], Oct 21, 2005
5. ### Nick BGuest

Thanks Bob!

Great information!

I did not realize that you could just put your parameters after the query
name. As long as they are in the order that Access asks for them, it works
great.

Thanks again!

"Bob Barrows [MVP]" wrote:

> Try it - it will work fine. Here's some more posts I've made about using
> this technique:
> http://groups.google.com/groups?hl=...=1&selm=
>
> http://groups.google.com/groups?hl=...=1&selm=ukS$6S$
>
> http://www.google.com/groups?selm=&oe=UTF-8&output=gplain
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=
>
> Did you change adDate to adDBTimestamp as I suggested? Did you get the same
> error?
>
> Bob Barrows
> Nick B wrote:
> > Thanks Bob. I do not think I can use the connection string as there
> > are single apostrophe's in the lname field. This is giving me errors.
> > That is whay I thought I had to use the command object rather than a
> > connection.
> >
> > "Bob Barrows [MVP]" wrote:
> >>>
> >>>
> >> Four things:
> >> 1) Stop using the obsolete odbc driver and use the native Jet OLE DB
> >> provider instead.
> >> http://www.aspfaq.com/show.asp?id=2126
> >> 2) Stop using an implicit connection. Explicitly create an open a
> >> Connection object. Using implicit connections can
> >> disable connection pooling.
> >> 3) Use adDBTimeStamp instead of adDate
> >> 4) Instead of the Command object, try this:
> >>
> >> Set cn=createobject("adodb.connection")
> >> cn.open "provider=microsoft.jet.oledb.4.0;" & _
> >> "data source=" & "p:\ath\to\database.mdb"
> >> set rs=createobject("adodb.recordset")
> >> cn.qryName sFirstName,sLastName,cDate(tmpDate), rs
> >> if not rs.eof then
> >> ...
> >>
> >> If that raises the same error, show us how to recreate the error
> >> (table definition and the sql statement)
> >>
> >> Bob Barrows
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>
Nick B, Oct 21, 2005

### Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.