I am pretty new to this, so be gentle please. :)

I am trying to let a user do a search for products within a certain price 
range from a user chosen manufacturer. I am getting the error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Disallowed implicit conversion from data type varchar to data type money, 
table '1stoplighting.dbo.Styles', column 'Price'. Use the CONVERT 
function to run this query.


in my WHERE statement below because the access database uses a data type 
of currency for the styles.price field, and I don't know how to make it 
match up with the user input. 

Thank you to anyone who can set me straight.


Here is the code I am using...


I use a FORM to get: 

<select name="Search__ManfactID" width="260">
<INPUT TYPE="text" NAME="Search__MinPrice" size="8">
<INPUT TYPE="text" NAME="Search__MaxPrice" size="8">

I then send this to an asp page which has the code below to validate that 
records exist before sending stuff to another page:

<%
	SearchManfactID=request("Search__ManfactID")
	SearchMinPrice=request("Search__MinPrice")
	SearchMaxPrice=request("Search__MaxPrice")
if SearchMinPrice="" then SearchMinPrice="0" 
if SearchMaxPrice="" then SearchMaxPrice="100000" 
if SearchMinPrice > SearchMaxPrice then 
	SearchMinPrice=request("Search__MaxPrice")
	SearchMaxPrice=request("Search__MinPrice")
end if

sql="SELECT Min(Styles.Price) AS 
minPrice,Product.Name,Product.ProductID,Product.Pix2,Product.Active,Produc
t.FeatureProduct,Product.CategoryType,Product.partnum,product.categoryid,p
roduct.subcategoryid,product.manfactid,"

sql=sql & " 
product.categoryid2,product.subcategoryid2,product.categoryid3,product.sub
categoryid3,manufacturer.name as 
manname,product.specialtext,product.onsale,Min(Styles.sale) AS 
minSalePrice "

sql=sql & " FROM (Product INNER JOIN Styles ON Product.ProductID = 
Styles.ProdNum) inner join manufacturer on 
product.manfactid=manufacturer.categoryid "

sql=sql & " WHERE Product.Active = 1 AND (product.manfactid like '%" & 
Search__ManfactID & "%' AND Styles.Price BETWEEN '%" & Search__MinPrice 
& "%' AND '%" & Search__MaxPrice & "%')"

sql=sql & " GROUP BY 
Product.Name,Product.ProductID,Product.Pix2,Product.Active,Product.Feature
Product,Product.CategoryType,Product.partnum,product.categoryid,product.su
bcategoryid,product.manfactid,"

sql=sql & " 
product.name,product.partnum,product.categoryid2,product.subcategoryid2,pr
oduct.categoryid3,product.subcategoryid3,manufacturer.name,product.special
text,product.onsale "

sql=sql & " order by manufacturer.name,1"
set rs=conn.execute(sql)

if rs.eof then
	response.redirect("adv_search.asp?Type="&request("type")
&"&category="&request("category")&"&subcategory="&request("subcategory")
&"&SearchManfactID="&request("Search__ManfactID")
&"&SearchMinPrice="&request("Search__MinPrice")&"&SearchMaxPrice="&request
("Search__MaxPrice")&"&status=NotFound")
else
	response.redirect("adv_search.asp?Type="&request("type")
&"&category="&request("category")&"&subcategory="&request("subcategory")
&"&SearchManfactID="&request("Search__ManfactID")
&"&SearchMinPrice="&request("Search__MinPrice")&"&SearchMaxPrice="&request
("Search__MaxPrice")&"&status=Found")
end if
%>

Use "ccur" to convert the form value to currency.

ccur(Request.Form("ValueToConvert"))

JK

> I am pretty new to this, so be gentle please. :)

> I am trying to let a user do a search for products within a certain 
price 
r> ange from a user chosen manufacturer. I am getting the error:

> Error Type:
M> icrosoft OLE DB Provider for SQL Server (0x80040E07)
D> isallowed implicit conversion from data type varchar to data type 
money, 
t> able '1stoplighting.dbo.Styles', column 'Price'. Use the CONVERT 
f> unction to run this query.

> 
i> n my WHERE statement below because the access database uses a data type 
o> f currency for the styles.price field, and I don't know how to make it 
m> atch up with the user input. 

> Thank you to anyone who can set me straight.

> 
H> ere is the code I am using...

> 
I>  use a FORM to get: 

> <select name="Search__ManfactID" width="260">
<> INPUT TYPE="text" NAME="Search__MinPrice" size="8">
<> INPUT TYPE="text" NAME="Search__MaxPrice" size="8">

> I then send this to an asp page which has the code below to validate 
that 
r> ecords exist before sending stuff to another page:

> <%
	> SearchManfactID=request("Search__ManfactID")
	> SearchMinPrice=request("Search__MinPrice")
	> SearchMaxPrice=request("Search__MaxPrice")
i> f SearchMinPrice="" then SearchMinPrice="0" 
i> f SearchMaxPrice="" then SearchMaxPrice="100000" 
i> f SearchMinPrice > SearchMaxPrice then 
	> SearchMinPrice=request("Search__MaxPrice")
	> SearchMaxPrice=request("Search__MinPrice")
e> nd if

> sql="SELECT Min(Styles.Price) AS 
m> 
inPrice,Product.Name,Product.ProductID,Product.Pix2,Product.Active,Produc
t> .FeatureProduct,Product.CategoryType,Product.partnum,product.categoryid,
p
r> oduct.subcategoryid,product.manfactid,"

> sql=sql & " 
p> 
roduct.categoryid2,product.subcategoryid2,product.categoryid3,product.sub
c> ategoryid3,manufacturer.name as 
m> anname,product.specialtext,product.onsale,Min(Styles.sale) AS 
m> inSalePrice "

> sql=sql & " FROM (Product INNER JOIN Styles ON Product.ProductID = 
S> tyles.ProdNum) inner join manufacturer on 
p> roduct.manfactid=manufacturer.categoryid "

> sql=sql & " WHERE Product.Active = 1 AND (product.manfactid like '%" & 
S> earch__ManfactID & "%' AND Styles.Price BETWEEN '%" & Search__MinPrice 
&>  "%' AND '%" & Search__MaxPrice & "%')"

> sql=sql & " GROUP BY 
P> 
roduct.Name,Product.ProductID,Product.Pix2,Product.Active,Product.Feature
P> 
roduct,Product.CategoryType,Product.partnum,product.categoryid,product.su
b> categoryid,product.manfactid,"

> sql=sql & " 
p> 
roduct.name,product.partnum,product.categoryid2,product.subcategoryid2,pr
o> 
duct.categoryid3,product.subcategoryid3,manufacturer.name,product.special
t> ext,product.onsale "

> sql=sql & " order by manufacturer.name,1"
s> et rs=conn.execute(sql)

> if rs.eof then
	> response.redirect("adv_search.asp?Type="&request("type")
&> "&category="&request("category")&"&subcategory="&request("subcategory")
&> "&SearchManfactID="&request("Search__ManfactID")
&> "&SearchMinPrice="&request("Search__MinPrice")
&"&SearchMaxPrice="&request
(> "Search__MaxPrice")&"&status=NotFound")
e> lse
	> response.redirect("adv_search.asp?Type="&request("type")
&> "&category="&request("category")&"&subcategory="&request("subcategory")
&> "&SearchManfactID="&request("Search__ManfactID")
&> "&SearchMinPrice="&request("Search__MinPrice")
&"&SearchMaxPrice="&request
(> "Search__MaxPrice")&"&status=Found")
e> nd if
%

Thanks for taking the time to answer.  I ended up using a CONVERT command 
in the WHERE statement (see below).  Just thought I would post what 
worked if anyone else had this problem.

Dave



sql=sql & " WHERE Product.Active = 1 AND (Product.ManfactID 
= "&SearchManfactID&" AND Styles.Price BETWEEN convert
(money,"&SearchMinPrice&") AND convert(money,"&SearchMaxPrice&"))"