cannot compare dates with ASP and Access backend

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

  1. Nick B

    Nick B Guest

    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
    #1
    1. Advertising

  2. 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
    #2
    1. Advertising

  3. Nick B

    Nick B Guest

    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
    #3
  4. 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
    #4
  5. Nick B

    Nick B Guest

    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
    #5
    1. Advertising

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.
Similar Threads
  1. csgraham74

    Problems Asp.Net with Access Backend

    csgraham74, Aug 10, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    533
    Lucas Tam
    Aug 10, 2005
  2. David Lozzi

    Dates dates dates dates... SQL and ASP.NET

    David Lozzi, Sep 29, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    904
    Rob Schieber
    Sep 30, 2005
  3. Ted
    Replies:
    1
    Views:
    441
  4. Keith R
    Replies:
    2
    Views:
    470
    =?Utf-8?B?QklUUyBlcnJvciBjb2RlIC0yMTQ3MDI0ODkxIG9y
    Apr 24, 2007
  5. PW

    Dates! Dates! Dates!

    PW, Aug 7, 2004, in forum: ASP General
    Replies:
    4
    Views:
    230
    Mark Schupp
    Aug 9, 2004
Loading...

Share This Page