«

Hi,
I have a sql statement in asp page as below

sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense, "
sql = sql & "TravelExpense, PersonnelExpense)"
sql = sql & " VALUES('" & l_ENO & "', '" & l_Date & "', '" &
l_contractedserviceexpense & "', '" & l_travelexpense & "', '" &
personnelexpense & "')"

The response.write on sql gives the following:

INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,
TravelExpense, PersonnelExpense) VALUES('1', '03/01/2005', '5', '5', '10')

However when I am trying to open the recordset, it gives me the following
error:

Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

I suspect the variables are not properly used to build the above statement.

The ENo is of text type,
EntryDate is of date type while
the rest of the last three fields are of number type.

I appreciate any help. Thanks in advance. Regards.

Jack wrote:
> Hi,
> I have a sql statement in asp page as below
>
> sql = " INSERT INTO tblExpense (ENO, EntryDate,
> ContractedServiceExpense, " sql = sql & "TravelExpense,
> PersonnelExpense)" sql = sql & " VALUES('" & l_ENO & "', '" &
> l_Date & "', '" & l_contractedserviceexpense & "', '" &
> l_travelexpense & "', '" & personnelexpense & "')"
>
> The response.write on sql gives the following:
>
> INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,
> TravelExpense, PersonnelExpense) VALUES('1', '03/01/2005', '5', '5',
> '10')
>
> However when I am trying to open the recordset, it gives me the
> following error:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E07)
> Data type mismatch in criteria expression.
>

You're having this problem because you are using dynamic sql. Since you have
ignored my previous attempts to wean you off this dangerous crutch, study
this:
http://groups-beta.google.com/group/...UTF-8&c2coff=1

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"

Drop the ' quotes around numeric values.

Access expects # around dates, not '

You should also use YYYY-MM-DD format so that there is no ambiguity (is that
Jan 3rd or Mar 1st?).

Search www.aspfaq.com for 80040e07 and for yyyy-mm-dd

A



> INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,
> TravelExpense, PersonnelExpense) VALUES('1', '03/01/2005', '5', '5', '10')
>
> However when I am trying to open the recordset, it gives me the following
> error:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E07)
> Data type mismatch in criteria expression.

Thanks to both you Aaron and Bob for the help. Looks like time to wean off
from dynamic sql. Let's see if I can do that this time. Regards.

"Aaron [SQL Server MVP]" wrote:

> Drop the ' quotes around numeric values.
>
> Access expects # around dates, not '
>
> You should also use YYYY-MM-DD format so that there is no ambiguity (is that
> Jan 3rd or Mar 1st?).
>
> Search www.aspfaq.com for 80040e07 and for yyyy-mm-dd
>
> A
>
>
>
> > INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,
> > TravelExpense, PersonnelExpense) VALUES('1', '03/01/2005', '5', '5', '10')
> >
> > However when I am trying to open the recordset, it gives me the following
> > error:
> >
> > Error Type:
> > Microsoft JET Database Engine (0x80040E07)
> > Data type mismatch in criteria expression.
>
>