Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > access database date parameter error

Reply
Thread Tools

access database date parameter error

 
 
Mr. x
Guest
Posts: n/a
 
      08-30-2003
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


 
Reply With Quote
 
 
 
 
Mr. x
Guest
Posts: n/a
 
      08-31-2003
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-" <(E-Mail Removed)> 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
>



 
Reply With Quote
 
 
 
 
William Ryan
Guest
Posts: n/a
 
      08-31-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
Mr. x
Guest
Posts: n/a
 
      08-31-2003
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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >

>
>



 
Reply With Quote
 
William Ryan
Guest
Posts: n/a
 
      08-31-2003
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" <(E-Mail Removed)> wrote in message
news:#tXuCN#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Mr. x
Guest
Posts: n/a
 
      09-01-2003
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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:#tXuCN#(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:#(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
IcingDeath IcingDeath is offline
Junior Member
Join Date: May 2006
Posts: 2
 
      05-23-2006
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM
Given a date, how to find the beginning date and ending date of that week Matt C++ 2 11-08-2003 08:30 PM



Advertisments