Problems with building sql statement

[Jack]

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.

 

[Bob Barrows [MVP]]

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.../c966c9c8eb98f331?hl=en&lr=&ie=UTF-8&c2coff=1

Bob Barrows

 

[Aaron [SQL Server MVP]]

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

 

[Jack]

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.