Initial Query:
"SELECT USR_EMAIL FROM USER WHERE USR_ID IN (:usrid)"
What I want query to be:
"SELECT USR_EMAIL FROM USER WHERE USR_ID IN (1, 2, 3)"
The paramater I add to the connection is obviously "1, 2, 3"
When I try to set up the reader I get the following error
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> Oracle.DataAccess.Client.OracleException ORA-01722: invalid number
How can I pass these as a paramater This works fine if there is only 1 number.
Problem adding paramater to query.
gh0st 1
I am not familiar with Oracle, but in SQL server there is a built-in function called sp_executesql. If Oracle has somthing similar, you could use that instead of executing your query directly. So your command text would be something like:
sp_executesql 'SELECT USR_EMAIL FROM USER WHERE USR_ID IN (' + :usrid + ')'
So you can still you the parameter. Again, I am not familiar with Oracle, so I'm not sure what the exact syntax would be.
MArkyMArc
Have a look at this most over at the Oracel forums.
http://forums.oracle.com/forums/thread.jspa threadID=354098&tstart=0
Dion
M. Zafar Iqbal
This is a common problem and there really isn't a way to work through it via ADO that I have ever seen. The only way I have found to pass in an IN clause is to use embeded SQL. Since you are using Oracle, I have no idea what to tell you.
ADO is making 1 parameter out of the syntax that you are passing in "1, 2, 3", so obviously, the database is telling you that it doesn't think the value passed in is numeric, which it isn't.
For SQL Server, I have created a variable that contains the SQL string that needs to be run and then concatenated the IN clause into it and then executed that statement. There are tons of reasons not to do it this way, but it works if you can control the environment in which it is happening. (This can lead to SQL Injection attacks, etc.)
Hope this helps. Maybe it will get you pointed in the right direction.