SQL to convert String to Numeric

TheWizardofInt

TheWizardofInt

Well-known member
Joined
Dec 31, 1969
Messages
333
Location
Orlando, FL
I want to see if a value in the field Key2 >= 11000

I use this SQL statement

"Select * from Contacts Where val(Key2)>=" & txtVal.text

txtVal is the text box that contains the value 11000

This gives me a DataType Mismatch. Any idea why?
 
11000 in the txtVal.text would be a string. I would suspect the val(Key2) value is probably an integer. Thus the Data Type Mismatch.

Jon
 
Close Jon.

11000 is a string, but Key2 is a string as well, which is why I am trying to convert it to a numeric.

I also tried replacing Val with cast(key2 as long) and cast(rtrim(key2) as long) and got an exception error.
 
Code: 
CAST(key2 AS int)
Ultimately you should be asking yourself why youre storing a numeric value in a string/character type however.
 
Youll need to make them both integers:
Code: 
Dim inttxtVal as Integer
inttxtVal = DirectCast(txtVal.text, Integer)

"SELECT* FROM Contacts WHERE CAST(Key2 AS Integer)>=" & inttxtVal



Jon
 
jfackler:

Actually that would be a bit silly as youd unbox (string to int) then box (int to string) the value.

Then again Im probably wrong and just myself look like a doofus.
 
TheWizardofInt:

I think he ment Integer.Parse(txtVal.Text). (or is it int.Parse()? Either way..) DirectCast() is for casting object to object.

EDIT:
Ohya, and if Integer.Parse() throws an exception then the value is not a valid integer. IE: 23k23 will throw an exception.
 
Wyrd,
Right on all counts, synaptic lubricant deficiency on my part.

For those unaware and looking to this forum for direction:

DirectCast is special in that conversions from type Object to any other type are performed as a direct cast down the hierarchy
 
Wiz,
I believe the answer to your problem lies in the use of a selection parameter.
Parameters are indicated either with a placeholder (a question mark) or with a named parameter variable. Parameters for queries involving OleDbCommand objects use question marks; queries that use SqlCommand objects use named parameters.

In your application, you prompt the user for a value. You then set the parameter to that value and run the command.

We all knew that of course.

Jon
 
Back
Top