 |
|
8000XXXX Errors Alerts ASP.NET 2.0 Classic ASP 1.0
Databases Access DB & ADO General SQL Server & Access Articles MySQL Other Articles Schema Tutorials Sql Server 2000 Sql Server 2005 General Concepts Search Engine Optimization (SEO)Search
ASP FAQ Tutorials :: Databases :: Other Articles :: How do I build a query with optional parameters?
How do I build a query with optional parameters?
Given this search page:
The following ASP script will split up the search terms and perform an AND or OR search appropriately:
Note that you may want to make your search case sensitive (for SQL Server, see Article #2152). You also might want to have a list of noise words, such as 'a' and 'the' - particularly if you don't use an EXACT PHRASE search. For example, a search for 't' might return your entire table! Yes, that's the user's fault, but it's still YOUR server doing all that extra work. See Article #2502 for some sample code, and see these search results to see that code in action!
| <form method=post action=find.asp> <input type=text name=keywords> <p> <select name=kind> <option value=' AND '>All words <option value=' OR '>Any word(s) </select> <p> <input type=submit> </form> |
The following ASP script will split up the search terms and perform an AND or OR search appropriately:
| <% SQL = "SELECT <column_list> FROM table" keywords = trim(replace(Request.Form("keywords"),"'","''")) if len(keywords)>0 then sqlExtra = " WHERE " kind = Request.Form("kind") keywordArray = split(keywords) for i = 0 to ubound(keywordArray) keyword = keywordArray(i) if i > 0 then sqlExtra = sqlExtra & kind ' if you want to match entire words only - note spaces! sqlExtra = sqlExtra & " (column LIKE '% " & keyword & " %')" ' if you want to find each string inside of other words ' sqlExtra = sqlExtra & " (column LIKE '%" & keyword & "%')" next end if response.write sql & left(sqlExtra,len(sqlExtra)-len(kind)) %> |
Note that you may want to make your search case sensitive (for SQL Server, see Article #2152). You also might want to have a list of noise words, such as 'a' and 'the' - particularly if you don't use an EXACT PHRASE search. For example, a search for 't' might return your entire table! Yes, that's the user's fault, but it's still YOUR server doing all that extra work. See Article #2502 for some sample code, and see these search results to see that code in action!
Related Articles
How do I calculate the median in a table? How do I create a store locator feature? How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns? How do I deal with multiple resultsets from a stored procedure? How do I debug my SQL statements? How do I determine if a column exists in a given table? How do I enable or disable connection pooling? How do I enumerate through the DSNs on a machine? How do I find a stored procedure containing <text>? How do I get a list of Access tables and their row counts? How do I get the latest version of the JET OLEDB drivers? How do I handle alphabetic paging? How do I handle BIT / BOOLEAN columns? How do I handle error checking in a stored procedure? How do I ignore common words in a search? How do I page through a recordset? How do I present one-to-many relationships in my ASP page? How do I prevent duplicates in a table? How do I prevent my ASP pages from waiting for backend activity? How do I prevent NULLs in my database from mucking up my HTML? How do I protect my Access database (MDB file)? How do I protect my stored procedure code? How do I protect myself against the W32.Slammer worm? How do I remove duplicates from a table? How do I rename a column? How do I retrieve a random record? How do I return row numbers with my query? How do I send a database query to a text file? How do I simulate an array inside a stored procedure? How do I solve 'Could not find installable ISAM' errors? How do I solve 'Operation must use an updateable query' errors? How do I temporarily disable a trigger? How do I use a SELECT list alias in the WHERE or GROUP BY clause? How do I use a variable in an ORDER BY clause? Should I index my database table(s), and if so, how? Should I store images in the database or the filesystem? Should I use a #temp table or a @table variable? Should I use a view, a stored procedure, or a user-defined function? Should I use recordset iteration, or GetRows(), or GetString()? What are all these dt_ stored procedures, and can I remove them? What are the limitations of MS Access? What are the limitations of MSDE? What are the valid styles for converting datetime to string? What datatype should I use for my character-based database columns? What datatype should I use for numeric columns? What does "ambiguous column name" mean? What is this 'Multiple-step OLE DB' error? What is wrong with 'SELECT *'? What naming convention should I use in my database? What should I choose for my primary key? What should my connection string look like? When should I use CreateObject to create my recordset objects? Where can I get this 'Books Online' documentation? Where do I get MSDE? Which database platform should I use for my ASP application? Which tool should I use: Enterprise Manager or Query Analyzer? Why are there gaps in my IDENTITY / AUTOINCREMENT column? Why can I not 'open a database created with a previous version...'? Why can't I access a database or text file on another server? Why can't I use the TOP keyword? Why do I get 'Argument data type text is invalid for argument [...]'? Why do I get 'Not enough space on temporary disk' errors? Why does ASP give me ActiveX errors when connecting to a database? Should I use COALESCE() or ISNULL()? Where can I get basic info about using stored procedures?