CODEWITHSUNDEEP
sqlvb.net
kiLLua
kiLLua

Reputation: 471

how to find the specific name in DB from search textbox?

I have a textbox that stands as a search box:
Here is my code example: 

    Dim txt As String = txt_find.Text
    query = "SELECT empID AS 'ID', fname + ' ' + mname + ' ' + lname AS 'Name' FROM empInfo "
    query &= "WHERE empID LIKE '%" & txt & "%' OR fname LIKE '%" & txt & "%' OR lname LIKE '%" & txt & "%' OR mname LIKE '%" & txt & "%'"

It returns the correct value if the user entered just first name or last name of the employee.
Example:
Input: 28 Or Dennis Or Smith

But when the user Inputs the full name(e.g. Dennis Smith) the result returns nothing.

how should it be done?.

Upvotes: 0

Views: 1440

Answers (3)

user6729302
user6729302

Reputation:

Maybe you should go like this.

"WHERE LastName like '%" & LastName.text & "%' OR fname LIKE '%" & FirstName.text & "%'" and so on.

just use the important fields in searching. firstname or lastname will do.

Upvotes: 0

JPil
JPil

Reputation: 301

try this:

 "where Empid like '%" & txt & "%' OR  replace(fname + mname + lname,' ','') like replace('%" & txt & "%',' ', '')"

So if you enter 'Dennis Smith' or 'DennisSmith' it will catch the resultant rows

Upvotes: 2

Aethan
Aethan

Reputation: 1985

You could try:

"WHERE fname+' '+lname like '%" & txt & "' OR  fname+' '+mname+' '+lname like '%" & txt & "'"

That's just the idea, you just have to include there the possible name formats like LastName, FirstName MiddleName or if you can think of more, the better you can handle your search.

Upvotes: 1

Related Questions