How to search for a keyword in a text

[D.M]

Hello everyone, I have a DB here that has an attribute "Status" it's a text ... I wanna make a search field in the form application that when the user enters a certain key word the application displays all the records containing that keyword.
Thanks in advance,
D.

[Ribeyed]

Hi,

is a bit vague which part your stuck on.

The string class provides IndexOf method that returns the location of the first match in a string.

Vb

Dim Position as Integer = 0
Dim StringtoSearch as string = "your text here.."
Dim SearchFor as string = "text"

Position = StringtoSearch.IndexOf(SearchFor, Position)

That would return a integer if its found or -1 if its not found.


To retrieve records based on a substring of text for a particular field then you would do the following SQL:

Select column1, column2 from table1 where column2 LIKE '%The Text%'

That would retrieve records that have The Text anywhere in column2.

regards

Ribs

[D.M]

yes, but the problem is...instead of "The Text" , I want "The Text" is what the user want to search for (Take the string from the user and apply it in the sql statement)...and it's a c# application by the way but it doesn't matter

[Ribeyed]

C#
            int Position = 0;
            string StringToSearch = "your text here...";
            string SearchFor = TextBox.Text;

            Position = StringToSearch.IndexOf(SearchFor, Position);

Select column1, column2 from table1 where column2 LIKE '%' & Textbox.Text & '%'

regards


Ribs

[D.M]

the sql statement is not working :S

[Ribeyed]

sorry but u ain't going to get much help with posts like that.
If does help to show us what code u have tried.

[D.M]

This "@" is to take the value from the user:

SELECT * FROM Patient WHERE Patient_Status LIKE @disease

it's working fine but to be a match...the user's inpout must be exactly=the text in the databse. what I have tried:

SELECT * FROM Patient WHERE Patient_Status LIKE '%' & @disease & '%'

the above statement error: The datatypes varchar and text are incompatible in the '' operator

SELECT * FROM Patient WHERE Patient_Status LIKE '%' & '@disease' & '%'

also error

SELECT * FROM Patient WHERE Patient_Status LIKE '%@disease%'

this is working, no errors, but it assumed the text to search for is "@disease"

SELECT * FROM Patient WHERE Patient_Status LIKE @%disease%

above statement error: error in WHERE clause near '%'

[Ribeyed]

Not sure what u mean by @ takes the value from the user.

You said the user inputs the search criteria in a textbox. So whatever you called ur textbox you get the value using:

String thevalue = Textboxname.text

Then make sure ur single and double quotes are correct.

"select col1 from table where col1 LIKE '%" & thevalue & "%'"