How to make a query based on user input?

Question

I am trying to make a program using Visual Basic 2008 express edition, that would allow the user to enter in a search word and the program would then return any matching results from an Access database.

I can't figure out how to make it so that the query searches based on what the user has typed in say.. TextBox1. I have tried several variations of code.

For example:

SELECT...
FROM...
WHERE column1 = TextBox1.Text

and

WHERE column1 = "TextBox1.Text"

and

WHERE column1 = '& TextBox1.Text &'

and even

WHERE column1 = '" & TextBox1.Text & "'"

Depending on how I try to write it, it will either come up blank, or will tell me "No value given for one or more required parameters."

Is there some way I can write this differently so it does the search using whatever is in TextBox1? If not, what else can I do that would give me the same results?

Answer 1

You need to quote the closing single-quotes and close the quote on the end of the string introducing the concatenated value of TextBox1.Text:

WHERE column1 = '" & TextBox1.Text & "'"

e.g.:

Dim sql AS String = "SELECT * FROM table WHERE column1 = '" & TextBox1.Text & "'"

Answer 2

WHERE column1 = '& TextBox1.Text &'

That one is the one that should work, and I suspect is the one that returns blank results as opposed to the 'no value' message.

Perhaps CASE is an issue. Try putting .toUpper or .toLower on search strings.

(TextBox1.Text).toString.toUpper

Another idea is to use the LIKE keyword instead of '='....

WHERE column1 LIKE '& TextBox1.Text &'

hope that helps

Answer 3

Also try using a variable instead of Textbox1.text.