PROBLEM EXECUTING a query in vb

Question

I have a problem with a sql query. Through the query I am trying to search database for any occurrences of string (can be anything) in a column using the SQL LIKE command. The problem is that it works fine for most of the strings say john, jim, ji"m , but does not work when i include the following characters which are ( ' , { , } , and a single quotation mark). MYSQL query takes care of these special cases by putting them in [] block whenever user enters them .

But i am getting the following error when i go to query the database using the GetSelectCommand() in VB.NET

Exception information: Exception type: System.Reflection.TargetInvocationException Exception message: Exception has been thrown by the target of an invocation.

Inner exception information (level 1): Exception type: System.ApplicationException Exception message: Number of values provided must be equal to the number of placeholders in query.

I have checked the query over and over again .. but its fine .

My database server is Sql Server 2008.

So my application throws the exception in this command:

Using reader As MustDisposeDataReader = pmSystem.DatabaseManager.GetSelectCommand(selectStatementBuilder.ToString(), New Object() {})

Where MustDisposeDataReader is an instance of a class in an internally developed library, which inherits from System.Object. pmSystem is an instance of the class PlanManagerSystem which implements the commandlayer. GetSelectCommand() takes the select command

can someone tell me what to do ???

Answer 1

you should use a parametrized query. Then vb.net will take care of escaping the strings conveniently.

Answer 2

There are a lot of ways to do this. Where is your query? Is it in a datasource object, a sqlcommand object in the code behind, somewhere else?

In any case, do a google search for "parameter" + datasource (or "parameter" + sqlcommand, etc.), and you'll come up with a TON of how-to on it. MSDN, VB Help, etc. are also good places to try to look it up. If you try, and still can't find an answer, then come back and ask again.