select query data type mismatch

E

eyoung1

this works

sSQL = "SELECT *" & _
" FROM Expenses2008" & _
" WHERE Amount Like '%" & Request.Form("searchItem") & "%'"
set rs = Connect.Execute(sSQL)

however if I enter an amount of 99 it not only gives me all entries
with 99.00 in the Amount collum but 199.00, 1991.72...anything with
two 9s together.

So I tried

sSQL = "SELECT *" & _
" FROM Expenses2008" & _
" WHERE Amount = '" & Request.Form("searchItem") & "'"
set rs = Connect.Execute(sSQL)

But I get an error message

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/eforms/shiprec/search.asp, line 201


Can someone help me with this?
 
B

Bob Barrows [MVP]

this works

sSQL = "SELECT *" & _
" FROM Expenses2008" & _
" WHERE Amount Like '%" & Request.Form("searchItem") & "%'"
set rs = Connect.Execute(sSQL)

however if I enter an amount of 99 it not only gives me all entries
with 99.00 in the Amount collum but 199.00, 1991.72...anything with
two 9s together.

So I tried

sSQL = "SELECT *" & _
" FROM Expenses2008" & _
" WHERE Amount = '" & Request.Form("searchItem") & "'"
set rs = Connect.Execute(sSQL)

But I get an error message

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/eforms/shiprec/search.asp, line 201


Can someone help me with this?
When you use Like, Jet converts the numeric data in your Number field to
strings in order to do the comparison.
When you use = no implicit conversion is performed. Since you are
comparing data contained in a column whose datatype is number to a
literal value contained in quotes (a string) a data type mismatch
occurs. You need to remove the quotes from this line:
" WHERE Amount = '" & Request.Form("searchItem") & "'"
so that it reads:
" WHERE Amount = " & Request.Form("searchItem")

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers (tokens):
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]
 
D

Daniel Crichton

wow...that was too easy.

Not a problem...internal server used by only 15 people.

What happens when one of those people decides they're going to leave the
company and aren't happy and puts something in the searchItem field of the
form that results in a SQL injection that does something to your data?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top