Condition | combination Query <!--
Frog Frog Recommendation: Multi-conditional combination query implementation in ASP
Multi-conditional combination query is very useful in many places, this paper uses a simple example to implement a combination query
Make sure that you have sqlserver2000 and its default database Norhtwind installed before the example.
The code is very intuitive, plus the key part I made comments, so it's easy to understand.
Some of the issues that need to be noted are:
1. Note that when the string is connected, the first character of the second string in the two string that needs to be connected should be preceded by a space so that the end of the two string is not connected to a single word.
The structure of the left function of 2.righ is case-sensitive, and if you're using ' and ' in your string, and you're judging with ' and ', the program won't run as you think.
3. Note JS, History.go ( -1), History.back (), Location.reload,location.href difference and practical application
Remind everyone to do some more data structure and algorithm training, write the basis of the program is very important, do not underestimate some string processing functions and regular expressions and other applications, many large projects are composed of a number of small functions together formed.
-->
<%
If Trim (Request.Form ("submit")) <> "Then"
Dim Arr_wawa,rs_wawa,strconn,sql
Set rs_wawa=server.createobject ("ADODB. RecordSet ")
strconn= "Driver={sql Server};server=192.168.0.110;database=northwind;uid=sa;pwd=sa;"
' Get the form variable, where only the spaces are filtered, but in practical applications, more dangerous characters need to be filtered for security.
Employeeid=trim (Request.Form ("EmployeeID"))
Titleofcourtesy=trim (Request.Form ("TitleOfCourtesy"))
Firstname=trim (Request.Form ("FirstName"))
Lastname=trim (Request.Form ("LastName"))
Title=trim (Request.Form ("Title"))
City=trim (Request.Form ("City"))
' The following is a dynamically generated SQL query string
'*************************************
Sql= "SELECT employeeid,titleofcourtesy,firstname,lastname,title,city from Employees WHERE"
If employeeid<> "" Then sql=sql& "employeeid=" &EmployeeID& "and"
If titleofcourtesy<> "" Then sql=sql& "titleofcourtesy= '" &TitleOfCourtesy& "' and"
If firstname<> "" Then sql=sql& "FirstName like '%" &FirstName& "% ' and"
If lastname<> "" Then sql=sql& "LastName like '%" &LastName& "% ' and"
If title<> "" Then sql=sql& "Title like '%" &Title& "% ' and"
If city<> "" Then sql=sql& "city like"% "&City&"% ' and "
If Right (sql,5) = "WHERE" then Sql=left (SQL,CLNG (len (SQL)-5) ' resolves all arguments to be empty
If Right (sql,3) = "and" Then Sql=left (SQL,CLNG (len (SQL)-3) ' Removes the last and
sql=sql& "ORDER by EmployeeID DESC"
' Response.Write SQL ' debug statement
'**************************************
Rs_wawa. Open sql,strconn,1,3
If not (Rs_wawa. Eof and Rs_wawa. BOF) Then
Arr_wawa=rs_wawa.getrows () ' Take out the offline Recordset, remove the recordset, and then close the recordset, saving resources
Else
Response.Write ("<script>alert (' no record '); History.go ( -1);</script>")
End If
Rs_wawa.close ()
Set rs_wawa=nothing
End If
%>
<form name= "Form1" method= "Post" action= "" >
<table width= "border=" 1 "align=" center "cellspacing=" 1 ">
<tr>
<TD colspan= "2" align= "right" ><div align= "center" >search employees</div></td>
</tr>
<tr>
<TD width= "50%" align= "right" >EmployeeID:</td>
<TD width= "50%" ><input name= "EmployeeID" type= "text" id= "EmployeeID" ></td>
</tr>
<tr>
<TD width= "50%" align= "right" >TitleOfCourtesy:</td>
<TD width= "50%" ><select name= "TitleOfCourtesy" id= "TitleOfCourtesy" >
<option value= "" >==select==</option>
<option value= "Ms." >Ms.</option>
<option value= "Dr." >Dr.</option>
<option value= "Mrs." >Mrs.</option>
</select></td>
</tr>
<tr>
<TD width= "50%" align= "right" >FirstName:</td>
<TD width= "50%" ><input name= "FirstName" type= "text" id= "FirstName" ></td>
</tr>
<tr>
<TD width= "50%" align= "right" >LastName:</td>
<TD width= "50%" ><input name= "LastName" type= "text" id= "LastName" ></td>
</tr>
<tr>
<TD width= "50%" align= "right" >Title:</td>
<TD width= "50%" ><input name= "title" type= "text" id= "title" ></td>
</tr>
<tr>
<TD width= "50%" a
