SQL to convert String to Numeric

[TheWizardofInt]

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?

[jfackler]

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

[TheWizardofInt]

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.

[Derek Stone]

CAST(key2 AS int)

Ultimately you should be asking yourself why you're storing a numeric value in a string/character type however.

[TheWizardofInt]

That gave the same error

 

Frustrating, isn't it. I would have sworn that 'Val' would work

[jfackler]

You'll need to make them both integers:

Dim inttxtVal as Integer
inttxtVal = DirectCast(txtVal.text, Integer)

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

 

 

 

Jon

[TheWizardofInt]

It says that you can't use direct cast in that context and, when you convert it to integer using Val(), it fails

[wyrd]

jfackler:

 

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

 

Then again I'm probably wrong and just myself look like a doofus.

[wyrd]

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.

[jfackler]

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 � all of the special conversion behaviors from Object are ignored. When converting an expression of type Object whose run-time type is a primitive value type, DirectCast throws a System.InvalidTypeException exception if the specified type is not the same as the run-time type of the expression. If the specified type and the run-time type of the expression are the same, however, the run-time performance of DirectCast is better than that of CType.

 

In addition, the concatenation (&) operator can convert a number to a string implicitly. Which is what it will do in my code above.

 

Thanks for the slight correction *zzzztt* I'm better now.

 

Jon

[jfackler]

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