# 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:
dim cmd
with cmd
.ActiveConnection = "....dsn"
.CommandText = "qryName"
.Parameters.Append
.Parameters.Append
.Parameters.Append
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:
> dim cmd
> with cmd
> .ActiveConnection = "....dsn"
> .CommandText = "qryName"
> .Parameters.Append
> .Parameters.Append
> .Parameters.Append
> 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
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.
4) Instead of the Command object, try this:

cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & "p:\ath\to\database.mdb"
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
> 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.
> 4) Instead of the Command object, try this:
>
> cn.open "provider=microsoft.jet.oledb.4.0;" & _
> "data source=" & "p:\ath\to\database.mdb"
> 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

this technique:

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

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
>> 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.
>> 4) Instead of the Command object, try this:
>>
>> cn.open "provider=microsoft.jet.oledb.4.0;" & _
>> "data source=" & "p:\ath\to\database.mdb"
>> 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:

> this technique:
>
> http://groups.google.com/groups?hl=...=1&selm=ukS$6S$
>
>
>
> 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
> >> 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.
> >> 4) Instead of the Command object, try this:
> >>
> >> cn.open "provider=microsoft.jet.oledb.4.0;" & _
> >> "data source=" & "p:\ath\to\database.mdb"
> >> 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