access database date parameter error

Discussion in 'ASP .Net' started by Mr. x, Aug 30, 2003.

  1. Mr. x

    Mr. x Guest

    Hello,
    I am using access database in aspx.
    I did something like this :
    ....
    currParam = new oleDbParameter("@create_date", OleDbType.DBTimeStamp)
    currParam.value = Now
    cmd.parameters.add(currParam)

    In the sql I wrote :
    insert into mytable ( ... , create_date , ....)
    values( ..., @create_date, ...)

    When execute the query I get an error :
    Data type mismatch in criteria expression.

    What is the problem ?

    Thanks :)
    Mr. x, Aug 30, 2003
    #1
    1. Advertising

  2. Mr. x

    Mr. x Guest

    Thanks :)
    This doen't work,
    but I did paid attention to something strange, that may be the reason to the
    problem :

    I am trying doing the follow :
    Insert into mytable(col1, col2, col3, col4, ... col10)
    values(@par1, @par2, @par3, @par4, ... @par10)

    I have notice that the row is not inserted as I wish to,
    and columnt : col9 i.e is set to parameter : par5 (and not to par9
    respectively).
    And this is not the only column that is inserted not as the sql command is
    asking to.

    (colomns doesn't get the right parameters, even I change the order of the
    columns and the parameters).

    Why is this problem ?

    Thanks :)

    "Cindy Meister -WordMVP-" <> wrote in message
    news:VA.000082e3.003a9cc2@speedy...
    > Hi Mr.,
    >
    > If you print @create_date to the screen, what exactly do you see?
    >
    > As a general rule, dates for Access need to be passed using #mm/dd/yyyy#
    > format - including the # to identify it as a date.
    >
    > > I am using access database in aspx.
    > > I did something like this :
    > > ....
    > > currParam = new oleDbParameter("@create_date",

    OleDbType.DBTimeStamp)
    > > currParam.value = Now
    > > cmd.parameters.add(currParam)
    > >
    > > In the sql I wrote :
    > > insert into mytable ( ... , create_date , ....)
    > > values( ..., @create_date, ...)
    > >
    > > When execute the query I get an error :
    > > Data type mismatch in criteria expression.
    > >

    >
    > Cindy Meister
    > INTER-Solutions, Switzerland
    > http://homepage.swissonline.ch/cindymeister
    > http://www.mvps.org/word
    > http://go.compuserve.com/MSOfficeForum
    >
    > This reply is posted in the Newsgroup; please post any follow question or
    > reply in the newsgroup and not by e-mail :)
    >
    Mr. x, Aug 31, 2003
    #2
    1. Advertising

  3. Mr. x

    William Ryan Guest

    First, you are using the @ symbol for param names, and in Access, I don't
    think that'll work for you --- you need to use the ? insert into
    mytable(..., create_date, ...) values (?, ?, ?)

    check out this link here, he goes into the whole process, but I think that's
    the main problem. Also, you can just use a DateTime field instead of
    Timestamp unless your db field is marked as such.

    Let me know if you have any problems.

    Bill
    "Mr. x" <> wrote in message
    news:...
    > Hello,
    > I am using access database in aspx.
    > I did something like this :
    > ...
    > currParam = new oleDbParameter("@create_date",

    OleDbType.DBTimeStamp)
    > currParam.value = Now
    > cmd.parameters.add(currParam)
    >
    > In the sql I wrote :
    > insert into mytable ( ... , create_date , ....)
    > values( ..., @create_date, ...)
    >
    > When execute the query I get an error :
    > Data type mismatch in criteria expression.
    >
    > What is the problem ?
    >
    > Thanks :)
    >
    >
    William Ryan, Aug 31, 2003
    #3
  4. Mr. x

    Mr. x Guest

    Thanks :)
    Now it's clear.

    The name after the insert ( ....) value (@par1, ...)
    par1 for parameter 1 - the name is ignored by the asp interperter, so I can
    use @xyz and it has the same result.
    (It doesn't seem to be the parameter currParam = new oldDBParameter("@xyz",
    OleDBType.Date)
    The thing that what is metter is the order of the paramerter and not what I
    called it at the insert command.
    I don't know why can I write any name I like after @ in the insert command -
    maybe it is for another purpose - I would like to know for what ?
    And suppose I don't want to use ? instead, but the name of the parameter
    itself - can I do it ?

    Thanks :)

    "William Ryan" <> wrote in message
    news:#...
    > First, you are using the @ symbol for param names, and in Access, I don't
    > think that'll work for you --- you need to use the ? insert into
    > mytable(..., create_date, ...) values (?, ?, ?)
    >
    > check out this link here, he goes into the whole process, but I think

    that's
    > the main problem. Also, you can just use a DateTime field instead of
    > Timestamp unless your db field is marked as such.
    >
    > Let me know if you have any problems.
    >
    > Bill
    > "Mr. x" <> wrote in message
    > news:...
    > > Hello,
    > > I am using access database in aspx.
    > > I did something like this :
    > > ...
    > > currParam = new oleDbParameter("@create_date",

    > OleDbType.DBTimeStamp)
    > > currParam.value = Now
    > > cmd.parameters.add(currParam)
    > >
    > > In the sql I wrote :
    > > insert into mytable ( ... , create_date , ....)
    > > values( ..., @create_date, ...)
    > >
    > > When execute the query I get an error :
    > > Data type mismatch in criteria expression.
    > >
    > > What is the problem ?
    > >
    > > Thanks :)
    > >
    > >

    >
    >
    Mr. x, Aug 31, 2003
    #4
  5. Mr. x

    William Ryan Guest

    Since you can't name the parameters if you use the ?, then order is
    absolutely critical. With named params, it doesn't matter, but if you don't
    have them, it's critical. So, did this fix your problem or is it still
    happening?

    Let me know


    Bill
    "Mr. x" <> wrote in message
    news:#tXuCN#...
    > Thanks :)
    > Now it's clear.
    >
    > The name after the insert ( ....) value (@par1, ...)
    > par1 for parameter 1 - the name is ignored by the asp interperter, so I

    can
    > use @xyz and it has the same result.
    > (It doesn't seem to be the parameter currParam = new

    oldDBParameter("@xyz",
    > OleDBType.Date)
    > The thing that what is metter is the order of the paramerter and not what

    I
    > called it at the insert command.
    > I don't know why can I write any name I like after @ in the insert

    command -
    > maybe it is for another purpose - I would like to know for what ?
    > And suppose I don't want to use ? instead, but the name of the parameter
    > itself - can I do it ?
    >
    > Thanks :)
    >
    > "William Ryan" <> wrote in message
    > news:#...
    > > First, you are using the @ symbol for param names, and in Access, I

    don't
    > > think that'll work for you --- you need to use the ? insert into
    > > mytable(..., create_date, ...) values (?, ?, ?)
    > >
    > > check out this link here, he goes into the whole process, but I think

    > that's
    > > the main problem. Also, you can just use a DateTime field instead of
    > > Timestamp unless your db field is marked as such.
    > >
    > > Let me know if you have any problems.
    > >
    > > Bill
    > > "Mr. x" <> wrote in message
    > > news:...
    > > > Hello,
    > > > I am using access database in aspx.
    > > > I did something like this :
    > > > ...
    > > > currParam = new oleDbParameter("@create_date",

    > > OleDbType.DBTimeStamp)
    > > > currParam.value = Now
    > > > cmd.parameters.add(currParam)
    > > >
    > > > In the sql I wrote :
    > > > insert into mytable ( ... , create_date , ....)
    > > > values( ..., @create_date, ...)
    > > >
    > > > When execute the query I get an error :
    > > > Data type mismatch in criteria expression.
    > > >
    > > > What is the problem ?
    > > >
    > > > Thanks :)
    > > >
    > > >

    > >
    > >

    >
    >
    William Ryan, Aug 31, 2003
    #5
  6. Mr. x

    Mr. x Guest

    Thanks :)
    I see by the time of your massege that you spending nights in the internet -
    thank you for any of your efforts.

    I see that with named params or with ? the order is critical.
    With named params - even a named that doesn't exists - aspx runs with no
    problem, but the order is critical.

    Anyway - everything works now, since I have paid attention to the order of
    creation of the parameters.

    Thanks :)

    "William Ryan" <> wrote in message
    news:#...
    > Since you can't name the parameters if you use the ?, then order is
    > absolutely critical. With named params, it doesn't matter, but if you

    don't
    > have them, it's critical. So, did this fix your problem or is it still
    > happening?
    >
    > Let me know
    >
    >
    > Bill
    > "Mr. x" <> wrote in message
    > news:#tXuCN#...
    > > Thanks :)
    > > Now it's clear.
    > >
    > > The name after the insert ( ....) value (@par1, ...)
    > > par1 for parameter 1 - the name is ignored by the asp interperter, so I

    > can
    > > use @xyz and it has the same result.
    > > (It doesn't seem to be the parameter currParam = new

    > oldDBParameter("@xyz",
    > > OleDBType.Date)
    > > The thing that what is metter is the order of the paramerter and not

    what
    > I
    > > called it at the insert command.
    > > I don't know why can I write any name I like after @ in the insert

    > command -
    > > maybe it is for another purpose - I would like to know for what ?
    > > And suppose I don't want to use ? instead, but the name of the parameter
    > > itself - can I do it ?
    > >
    > > Thanks :)
    > >
    > > "William Ryan" <> wrote in message
    > > news:#...
    > > > First, you are using the @ symbol for param names, and in Access, I

    > don't
    > > > think that'll work for you --- you need to use the ? insert into
    > > > mytable(..., create_date, ...) values (?, ?, ?)
    > > >
    > > > check out this link here, he goes into the whole process, but I think

    > > that's
    > > > the main problem. Also, you can just use a DateTime field instead of
    > > > Timestamp unless your db field is marked as such.
    > > >
    > > > Let me know if you have any problems.
    > > >
    > > > Bill
    > > > "Mr. x" <> wrote in message
    > > > news:...
    > > > > Hello,
    > > > > I am using access database in aspx.
    > > > > I did something like this :
    > > > > ...
    > > > > currParam = new oleDbParameter("@create_date",
    > > > OleDbType.DBTimeStamp)
    > > > > currParam.value = Now
    > > > > cmd.parameters.add(currParam)
    > > > >
    > > > > In the sql I wrote :
    > > > > insert into mytable ( ... , create_date , ....)
    > > > > values( ..., @create_date, ...)
    > > > >
    > > > > When execute the query I get an error :
    > > > > Data type mismatch in criteria expression.
    > > > >
    > > > > What is the problem ?
    > > > >
    > > > > Thanks :)
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Mr. x, Sep 1, 2003
    #6
  7. Mr. x

    IcingDeath

    Joined:
    May 23, 2006
    Messages:
    2
    I was reading this post cause I run into a simillar problem with date datatype and access. I know its kind of out of date but still I wanna post my solution here so other people can find it.

    In my case, I have made a wrapper around the OleDBParameter collection in order to be able to switch to another database engine if the need arises. The sub that adds parameters into the collection is this one

    Public Sub Add(ByVal name As String, ByVal value As Object)
    With mCommand.Parameters.Add(name, value)
    .Direction = ParameterDirection.Input
    If TypeOf value Is Date Then
    .OleDbType = OleDb.OleDbType.Date
    End If
    End With
    End Sub

    Although I hadnt read this post before today, I too realized that names dont matter in access. The order matters.
    Another thing I noticed is that although the Add Function of the OleDBParameter collection is supposed to set the OleDBType on its own (it does that in most cases) it has some sort of bug (maybe because there are two date types in .net Date and DateTime) when it comes to the Date DataType.

    As you can see in the above function, the If Typeof value is Date part is what solved my problem.
    Before I put that I kept getting "Data type mismatch in criteria expression" errors.
    IcingDeath, May 23, 2006
    #7
    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. Matt
    Replies:
    1
    Views:
    597
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,250
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    515
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    719
    Richard Heathfield
    Nov 8, 2003
  5. Matt
    Replies:
    11
    Views:
    405
    Aaron Bertrand [MVP]
    Nov 8, 2003
Loading...

Share This Page