SQL parameterized query not returning correct results

Question

I have a view in my DB and the view has a row I am trying to search for. I've tested it in sql server and it returned the correct result. However when I try it with parameters from vb it won't return anything. The Sql code that I get a query to return a correct result looks like

SELECT * 
FROM 
   (SELECT 
       ROW_NUMBER() OVER (ORDER BY groupID DESC) AS Row, * 
    FROM 
       SchedulingGroup_VIEW 
    WHERE 
       (scheduled = 1) 
       AND ((building LIKE '%dunn%') OR (room LIKE '%dunn%') 
            OR (requestBy LIKE '%dunn%') OR (requestFor LIKE '%dunn%') 
            OR (groupID LIKE '%dunn%') OR (description LIKE '%dunn%')) 
       AND (NOT EXISTS (SELECT gID FROM facilitiesForm 
                        WHERE facilitiesForm.gID <> gID))) AS TMP 
WHERE 
    (Row BETWEEN 0 AND 100)

The SQL with parameter looks like

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY groupID DESC) AS Row, * 
FROM schedulingGroup_VIEW 
WHERE (scheduled = 1) AND 
( (building LIKE '%' + @search + '%') 
   OR (room LIKE '%' + @search + '%') 
   OR (requestBy LIKE '%' + @search + '%') 
   OR (requestFor LIKE '%' + @search + '%') 
   OR (groupID LIKE '%' + @search + '%') 
   OR (description LIKE '%' + @search + '%')) 
AND 
  (NOT EXISTS (SELECT gID FROM facilitiesForm 
     WHERE facilitiesForm.gID <> gID))) AS TMP WHERE (Row BETWEEN 0 AND 100)

sqlComm.Parameters.AddWithValue("@search", info.search)

with info.search = "dunn".

The sql query returns the appropriate row but the vb.net with parameters returns nothing.

Community · Accepted Answer · 2017-05-23 12:31:40Z

2

Move your wildcards (i.e. your % characters) into your VB.net string before you pass it as a parameter.

For example, do this in VB.net code...

sqlComm.Parameters.AddWithValue("@search", "%" + info.search + "%");

And in your SQL when you use the @search parameter in the LIKE statement, don't add in wildcards, like shown below...

WHERE building LIKE @search

See this SO post which is essentially the same question...

How to use wildcards in SQL query with parameters

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Apr 25, 2014 at 18:13

Jim

6,88113 gold badges48 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

willJk Over a year ago

I'm up voting this and placing it as the answer. I have seen the parameters set both ways though. it also helps to make the test db mirror the live. Thanks.

sovemp Over a year ago

This is better though as it prevents SQL injection.

SearchForKnowledge Over a year ago

Do I also have the same issue?

Collectives™ on Stack Overflow

SQL parameterized query not returning correct results

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related