How to write an SQL statement with a where clause but there is a data type miss match?

Question

The SQL string looks like this :
"Select * from Test2 where PROVIDER_ID = '" & LogInfo.Provider_ID & "'"

i want to only select the info that matches up the provider_id. The problem is that the PROVIDER_ID in the database is a datatype number and cant be changed because it will mess up the relatioships in the database. I think the equals sign is the problem cause it's doing a math operation and not the compare. Is there any way around this. The login.provider_id is a string that is in a class that saves the provider_id i want to look up

Answer 1

Not sure of it but try "like" instead of "="

Answer 2

If Provider_ID is defined as a number field in the database, you don't need to put quotes around it. You use quotes for strings. That's why you get a type mismatch error. So, your SQL statement will look something like this,

"Select * from Test2 where PROVIDER_ID = " & LogInfo.Provider_ID

Answer 3

I think you have to convert the string Loginfo.provider_id into a number.
The expression for converting a string into a number depends on the Database you're using: if it's an Oracle DB you need something like TO_NUMBER (I think...). if it's a SQL Server DB you need an expression like CONVERT(Loginfo.provider_id,integer) (or something similar...).
Another solution would be if this query is contained into a VB program, as I imagine, you may try to convert the string into a number directly from there like:
"Select * from Test2 where PROVIDER_ID = '" & CONV(LogInfo.Provider_ID, integer) & "'"
These are my ideas. I'm sorry I cannot be more precise about the correct expressions but I suggest you try each of it and see if it works.

Good luck!

Answer 4

In SQL statement, to convert characters into numbers you use To_NUMBER and to do the opposite, you use TO_CHAR
so in your case,
Select * from Test2 where PROVIDER_ID = TO_NUMBER(LogInfo.Provider_ID)

What piece of software are you using? Visual Studio?? In this case you may considerer the following :
"Select * from Test2 where PROVIDER_ID = " & Cint(LogInfo.Provider_ID)

Answer 5

Use 'cast'

Select * from Test2 where cast(PROVIDER_ID as varchar(20))= '" & LogInfo.Provider_ID & "'"

check your SQL help, and find similar command for 'CAST'
I Use Interbase, and in interbase : string is varchar, you can change it to suit your Database (e.g. as string)

Answer 6

did you tried to unquote the search id?

select * from Test2 where PROVIDER_ID=352 and ....