Problems with building sql statement

[Jack]

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

sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServi ceExpense, "
sql = sql & "TravelExpe nse, PersonnelExpens e)"
sql = sql & " VALUES('" & l_ENO & "', '" & l_Date & "', '" &
l_contractedser viceexpense & "', '" & l_travelexpense & "', '" &
personnelexpens e & "')"

The response.write on sql gives the following:

INSERT INTO tblExpense (ENO, EntryDate, ContractedServi ceExpense,
TravelExpense, PersonnelExpens e) 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.

[Bob Barrows [MVP]]

Re: Problems with building sql statement

Jack wrote:[color=blue]
> Hi,
> I have a sql statement in asp page as below
>
> sql = " INSERT INTO tblExpense (ENO, EntryDate,
> ContractedServi ceExpense, " sql = sql & "TravelExpe nse,
> PersonnelExpens e)" sql = sql & " VALUES('" & l_ENO & "', '" &
> l_Date & "', '" & l_contractedser viceexpense & "', '" &
> l_travelexpense & "', '" & personnelexpens e & "')"
>
> The response.write on sql gives the following:
>
> INSERT INTO tblExpense (ENO, EntryDate, ContractedServi ceExpense,
> TravelExpense, PersonnelExpens e) 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.
>[/color]

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/microsoft.public.inetserver.asp.db/msg/c966c9c8eb98f331?hl=en&lr=&ie=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"

[Aaron [SQL Server MVP]]

Re: Problems with building sql statement

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


[color=blue]
> INSERT INTO tblExpense (ENO, EntryDate, ContractedServi ceExpense,
> TravelExpense, PersonnelExpens e) 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.[/color]

[Jack]

Re: Problems with building sql statement

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:
[color=blue]
> 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
>
>
>[color=green]
> > INSERT INTO tblExpense (ENO, EntryDate, ContractedServi ceExpense,
> > TravelExpense, PersonnelExpens e) 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.[/color]
>
>[/color]