access database date parameter error

[Mr. x]

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]

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

 

[William Ryan]

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]

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]

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]

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

 

[IcingDeath]

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.