Search by 'contains...'

[christrinder]

Hello,

I want uses to be able to do a search on my database by typing in all, or part of the name they are looking for into a text box. How do I write the search criteria that looks for values 'containing' the input as opposed to exact matches? Hope that makes sense.

Thanks,

Chris

[Leeus]

Use a SQL like statement and append both sides of the string to have a %, for example, "%" & string & "%"

[BigDaddy]

SELECT * FROM table WHERE fieldname like '%" & searchby & "%'

In this example, "searchby" is the variable submitted from the textbox.


This will give you a recordset of any entries that have the word anywhere in the field.

[whammy]

Yup. But FYI, in case you're using Access instead of SQL Server, you'd use * in place of the % (only inside the LIKE statement) since they just have different wild card characters.

* still means ALL in both databases otherwise...

[christrinder]

Hi Guys,

Thanks for the help. I've tried what you suggested, but I get the following error.

Microsoft VBScript compilation error '800a03ea'

Syntax error

/ti/dirsearchresults.asp, line 34

strSQL = "SELECT name1, name2 FROM users WHERE name1 like '*" & searchby & '*"
---------------------------------------------------------------------------^

[Roelf]

Originally posted by christrinder
Hi Guys,

Thanks for the help. I've tried what you suggested, but I get the following error.

Microsoft VBScript compilation error '800a03ea'

Syntax error

/ti/dirsearchresults.asp, line 34

strSQL = "SELECT name1, name2 FROM users WHERE name1 like '*" & searchby & '*"
---------------------------------------------------------------------------^
quotes mixed up
strSQL = "SELECT name1, name2 FROM users WHERE name1 like '*" & searchby & "*'"

[christrinder]

Thanks, although I don't get an error message, it still dosn't work. Even if I type the exact name1 in the WHERE expression, I still don't get any results. If I try it with the % inplace of the *, it returns all the records, i.e. using no WHERE Criteria.

Any ideas?

[Roelf]

does searchby have a value? otherwise you search for all records containing %% in the name-field. That should return all records :D

[whammy]

Sounds like you're using SQL Server then - as I understand it the * is a wild card only in Access...

Anyway, roelf is right. What I would do is:

Response.Write(strSQL) : Response.End

And that way you can see the actual query you're running. I also think searchby is probably not getting a value.