[RESOLVED] VB .NET, Access, SQL, and LIKE operator

[kevin_10987]

Okay, as the title shows, I'm using VB .NET, Access, SQL, and the
LIKE operator.

I am experimenting with the binding source, dataset, and table adapter controls to get the program to behave in the same way that a data access
class would if I had written code in a DA class.

Okay, here is my problem. I have a sql command that isn't working.

SELECT        Title, Director, YearMade, Description
FROM            MyMovies
WHERE        (Title LIKE '%' + TitleTextBox.[text] + '%')

The TitleTextBox.text is a text box on my form and I want to use the
value of it at runtime to narrow the information in the database down
to only matching requests.

I created this query using the query builder while the program was not
in runtime mode.

This program is just a simple little program to store film information.
So if someone types in "Dumb" into the textbox then
"Dumb and Dumber" and "Dumb and Dumberer" should get returned
to the dataset. [I'm assuming that it is where it gets returned to,
when a query is performed on the database.]

Anyway, if anyone has a solution, please post.

Thanks!

[mickey_pt]

You can do that by opening the tab data sources, drag the title field to the form, click in the little black arrow in the text box and then select add query, the you can put there you're query...

This is one way... there are another ones...

[kevin_10987]

That is how I added the SQL query to the project. But the SQL query isn't working. I believe it has something to do with how I added in the LIKE operator into the SQL query. I believe my sql query is incorrect. I think it is having an issue with adding "TitleTextBox.text" to the query.

Maybe someone can help me resolve this issue.

[techgnome]

SQL is SQL.... it knows nothing about your application. It knows nothing about your form. It knows nothing about your text box. You have to supply the data to it.
1) Replace "(Title LIKE '%' + TitleTextBox.[text] + '%')" with "(Title LIKE '%' + @SearchFor + '%')"
2) Use the AddParameter method to add the value to the parameter
3) Run the command.

-tg

[nmadd]

After getting the code to work with tg's advice, just remember that if you have multiple parameters that the Access OleDb provider does not accept "named parameters." You can name your parameters as noted above but you must add them to your ParameterCollection in the correct order! They must be in the same order that they are in your SQL statement. There is a link in my signature if you want to read more.

[BillBoeBaggins]

One last footnote. Access you use "*" with LIKE, and in SQL Server you use "%".

Example
LIKE '%dumb%' ---SQL Servier
LIKE "*dumb*" ---Access

[techgnome]

Actually... it does support them.... it just doesn't care what the names are and will use positional reference to plug the parameters in. SQL Server on the other hand, fully supports named parameters and will complain if the names don't match.

-tg

[GaryMazzone]

The * with Access for wildcards is only appropriate if using DAO access methods. All other methods even for MS Access woud use the % sign as a wildcard.

[kevin_10987]

Adding parameters to the new query function was exactly what I was looking
for. Thanks everyone!