Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Problems with building sql statement

Reply
Thread Tools

Problems with building sql statement

 
 
Jack
Guest
Posts: n/a
 
      03-30-2005
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.
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-31-2005
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"


 
Reply With Quote
 
 
 
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      03-31-2005
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.


 
Reply With Quote
 
Jack
Guest
Posts: n/a
 
      03-31-2005
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.

>
>

 
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
Problem with building sql statement to handle single quote in a fi Jack ASP General 4 01-10-2006 09:06 PM
Problem in building dynamic sql update statement Jack ASP General 2 12-15-2004 08:07 PM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page David Browne ASP .Net 0 08-21-2003 10:43 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM



Advertisments