cannot compare dates with ASP and Access backend

[Nick B]

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!

 

[Bob Barrows [MVP]]

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

 

[Nick B]

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]]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

http://www.google.com/[email protected]&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

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

Bob Barrows

 

[Nick B]

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!