How can I do the following using LIKE%% with wildcards instead of =
?

code:

oCon = New Oracle.DataAccess.Client.OracleConnection
sSql = "SELECT * FROM TABLE Where NAME = Name "

Dim cmdMyCommand As New Oracle.DataAccess.Client.OracleCommand
Dim pInParam As New Oracle.DataAccess.Client.OracleParameter

pInParam.Value = "Company"

With cmdMyCommand
.Connection = oCon
.CommandText = sSql
.Parameters.Add(pInParam)
.ExecuteNonQuery()
End With
'''

in other words, instead of 'Where NAME = Name'
I want to do 'Where NAME LIKE :%pName%'
I'm not sure of the correct syntax...

*****

Also, what is the difference in building up statements in this manner
using parameters, and using BIND VARIABLES, or is it the same thing?
One of our DBAs has asked me to look at re-writing my code to use bind
variables for the oracle db access as it is supposedly more efficient.
In VB6, '?' were used to denote where bind variables would be appended,
this no longer appears to be a valid character in .net..
Thanks in advance for any replies to the above

On 18 Aug 2005 07:32:23 -0700, "RDS" <(E-Mail Removed)> wrote:

¤ How can I do the following using LIKE%% with wildcards instead of =
¤ ?
¤
¤ code:
¤
¤ oCon = New Oracle.DataAccess.Client.OracleConnection
¤ sSql = "SELECT * FROM TABLE Where NAME = Name "
¤
¤ Dim cmdMyCommand As New Oracle.DataAccess.Client.OracleCommand
¤ Dim pInParam As New Oracle.DataAccess.Client.OracleParameter
¤
¤ pInParam.Value = "Company"
¤
¤ With cmdMyCommand
¤ .Connection = oCon
¤ .CommandText = sSql
¤ .Parameters.Add(pInParam)
¤ .ExecuteNonQuery()
¤ End With
¤ '''
¤
¤ in other words, instead of 'Where NAME = Name'
¤ I want to do 'Where NAME LIKE :%pName%'
¤ I'm not sure of the correct syntax...
¤
¤ *****
¤
¤ Also, what is the difference in building up statements in this manner
¤ using parameters, and using BIND VARIABLES, or is it the same thing?
¤ One of our DBAs has asked me to look at re-writing my code to use bind
¤ variables for the oracle db access as it is supposedly more efficient.
¤ In VB6, '?' were used to denote where bind variables would be appended,
¤ this no longer appears to be a valid character in .net..
¤ Thanks in advance for any replies to the above

AFAIK the '?' character is still supported in ADO.NET, at least for OLEDB.

Using a named parameter should be supported as well for the .NET Oracle provider. When you add your
parameter, just make sure to specify the name as "pName" (according to your above example).


Paul
~~~~
Microsoft MVP (Visual Basic)