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.



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 +'%')

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.


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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.