TableAdapter FillBy Query with parameters doesn't work with LIKE operator.

Question

Hi All,

Banging my head against a wall here. I have a query that looks like this.

SELECT FirstName, LastName, Address
FROM Members
WHERE FirstName LIKE 'JOE%'

That works absolutely fine in query wizard and the DataTablePreview data window. However, when I do this.

SELECT FirstName, LastName, Address
FROM Members
WHERE FirstName LIKE ?

I get nothing when I run the fillby method. If I change the LIKE to =.

SELECT FirstName, LastName, Address
FROM Members
WHERE FirstName = ?

Everything works great. I need to get LIKE working though so I can wildcard search.

I'm using the SQL server OLE db connections if that means anything.

UPDATE

Using the LIKE operator doesn't work at all. When I just swap out = for LIKE. Nothing is returned.

Answer 1

Can you not add the % to the param field?

Found at

• Using 'like' in TableAdapter SQL statement with parameter
• using the LIKE statement in a table adapter

Answer 2

You'll need to convert your query to :

WHERE FirstName LIKE '%' + ? + '%'

If you pass % within the parameter itself, I think it will interpret it as a string value rather than a wildcard and just work the same as FirstName = 'JOE%'

Answer 3

So it turns out that something in the OLEDB connection was hosed up. When I created a new connection and a new table adapter, everything started working fine.

EDIT

What I actually did was use the SQL server adapter instead of the SLQ server OLEDB adapter.

DB