Parameterized like queries

[Minko]

Hello,

Here is my code so far:

TempCommand.CommandText = "SELECT Lenscode FROM Products WHERE (Lenscode LIKE '%@Input%')"

TempCommand.Parameters.AddWithValue("@Input", "5")

When using a datareader this does not work. but when the code is like this it works:

TempCommand.CommandText = "SELECT Lenscode FROM Products WHERE (Lenscode LIKE '%5%')"

So my question is how can I make this work with the parameters.

Many thanks in advance.

Minko

[G_Waddell]

Hi,

You just need to modify your code slightly... in your SQL string, LIKE expects a value after it, you wish to use the parameter @Input to supply this value but because you are placing the parameter inside a string '%@Input%' the SQL literaly thinks find a record where lenscode contains "@Input" i.e. it does not read it in as a parameter...

So you can modify your code as follows:

TempCommand.CommandText ="SELECT Lenscode FROM Products WHERE (Lenscode LIKE @Input)" 

'I assume you pass the value into the input so I'll use a variable for you. 
'In your example,  MySearchValue =5
TempCommand.Parameters.AddWithValue("@Input", "%" &MySearchValue &"%")

As an alternative, it may be possible to do this - but I've never tried it...

TempCommand.CommandText= "SELECT Lenscode FROM Products WHERE (Lenscode LIKE '%' + @Input +'%')

[Minko]

Thank you so much, that has been driving me crazy. It is all working fine now so I will mark this as solved.

Once again, thanks.

Minko

[G_Waddell]

No Worries, Sometimes it just needs someone else to take a look at the problem...