Skip to main content
T
tedstar
Participant
October 10, 2006
Answered

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

  • October 10, 2006
  • 2 replies
  • 509 views
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,
This topic has been closed for replies.
Correct answer Newsgroup_User
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' "


2 replies

N
Newsgroup_User
Inspiring
October 10, 2006
> 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


N
Newsgroup_UserCorrect answer
Inspiring
October 10, 2006
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' "


T
tedstarAuthor
Participant
October 10, 2006
Thank you both very much for your help. Fantastic to get help so quickly.

cheers. Ed

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices