html textbox & sql like search

[a8le]

Hi all,

I am a newbie to SQL and need some advice as to how to make a html textbox work with a sql like search.

Actually, I don't know what exactly I am looking for... What I am trying to do is allow users to search a table's text column and return all row with the submitted keywords.

I have honestly spent to last two days looking for such a tutorial but there are none. Please help.

I am also trying to do all this using a stored procedure. Is this possible?

-a8le

 

[jmcilhinney]

If you want to search on partial strings then you need to use wildcards, e.g.

SELECT Name FROM People WHERE Name LIKE 'S%'

If you want to let the user enter a search value in a TextBox then you would use a parameter:

SELECT Name FROM People WHERE Name LIKE @Name

and add the parameter to your Command object:

myCommand.Parameters.Add("@Name", VarChar)

Whenevr you wanted to execute a search you would set the parameter Value from the TextBox:

myCommand.Paramaters("@Name").Value = myTextBox.Text

The decision you have to make is how you want to add the wildcard. If you don't include a wildcard the "LIKE" behaves just like "=". You need to decide whether you want the user to enter the wildcards themselves in the TextBox, or you're going to add them in code. If you're going to add them in code, are you going to add one at the start and one at the end of the string or just one at the end.

 

[a8le]

this works...

hi jmcilhinney,

you are right, a friend just recently help me to get it work. This is what he suggested...

Text LIKE '%' + @strSearch + '%'

he rather busy for me to ask him to explain himself...

may i ask you what adding ' + ... +' does? because I tried

Text LIKE '% @strSearch %'

and that didn't work.

Also, when i search for "do", even "mod" comes up. why is that?

-a8le

 

[jmcilhinney]

The plus means you are concatenating strings. By putting the @strSearch inside the single quotes you are actually searching for strings that literally contain thos characters. @strSearch is supposed to be a variable, so by following your friends advice you will be creating a string that starts and ends with a "%" symbol and has the CONTENTS OF THE @strSearch VARIABLE in the middle, rather than the actual string "@strSearch". As an example, assuming that the @strSearch parameter contains the value "Hello", your friends code produces this:

Text LIKE '%Hello%'

while yours literally produces:

Text LIKE '% @strSearch %'

 

[a8le]

Thank you.