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 :D

[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 & "%'"