sql: convert data type from varchar to money (asp, vb, sql)

[tedstar]

Hi,

I'm trying to do a simple search form that takes a value for a max imum price from a text-field in a form, which is then passed to a catalogue page. On this page I have a select query that includes a where condition for price less than the value of the query string.

A simplified version of the query is:

"SELECT * from table where price < 'varPrice' "

When I run this I get an error saying

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

I've googled this a fair bit and can't really find what I need to get me started - anyone able to point me in the right direction?

Thank you very much,

[Newsgroup_User · Accepted solution]

You're comparing price (money) to 'varPrice' (text), and an implicit
conversion between those two types is not allowed. You have to explicitly
CAST or CONVERT the column.

In your case, I imagine you don't really mean for varPrice to be text. Lose
the quotes and it will be considered a number.


"tedstar" <webforumsuser@macromedia.com> wrote in message
news:eggr3h$old$1@forums.macromedia.com...
> "SELECT * from table where price < 'varPrice' "

[tedstar]

Thank you both very much for your help. Fantastic to get help so quickly.

cheers. Ed

[Newsgroup_User]

> A simplified version of the query is:
>
> "SELECT * from table where price < 'varPrice' "

If price is an integer, it can't be greater than or less than a string.

You need to compare apples to apples.

I'd do this:

dim varPrice as integer
varPrice = cint(yourFormField.text)
"SELECT * from table where price <" & varPrice

Though note that you shouldn't be passing user-input data into a SQL string
like that. Look into use parameterized SQL to handle that.

-Darrel