Dynamic | Conditions when dealing with SQL Server databases with ASP, query statements are essential. The SQL Server database itself provides rich query statements, but how do you implement a multiple conditional dynamic query for a SQL Server database in ASP? The author has solved this problem when developing a website based on SQL Server with ASP, this paper introduces the implementation method.
Definition of the database
Define a database in SQL Server named "Comm_server". Define a table in the database named "operator" that contains the fields shown in the following table (for example, 5 fields in a program only):
Field Name Note
Name varchar (20), defined as the primary key
Educationallever Education Varchar (10)
Grade title Varchar (10)
State now status Varchar (10)
Time record datetime
Enter the design of the Web page
Define a form in the Index_people.htm Web page, where the standard user interface elements used are as follows:
Name: Education: SELECT title: Choose now Status: Select
The TML program code is as follows:
The 〈!--form method is set to post, and the form is submitted for processing by the people_seek.asp program--〉
〈form method= "POST" action= "people_seek.asp"
〈pre〉
〈font size= "2" Name: 〈/font〉
〈input type= "text" name= "Txt_name" size= "10"
〈font size= "2" Education:
〈select name= "Sel_xueli"
〈option value= "Choice" Select 〈/option〉
〈option value= "Secondary school" secondary school 〈/option〉
〈option value= "College" college 〈/option〉
〈option value= "undergraduate" undergraduate 〈/option〉
〈option value= "Master" master 〈/option〉
Dr. 〈/option〉 〈option Value= "Dr."
〈option value= "postdoctoral" postdoctoral 〈/option〉
〈/select〉
Title:
〈select name= "Sel_zhicheng"
〈option value= "Choice" Select 〈/option〉
〈option value= "UNIDO" UNIDO 〈/option〉
〈option value= "Engineer" engineer 〈/option〉
〈option value= "senior Engineer" senior engineer 〈/option〉
〈/select〉
Now the situation:
〈select name= "Sel_zhuangkuang"
〈option value= "Choice" Select 〈/option〉
〈option value= "Reign" 〈/option〉
〈option value= "Leave" Leave 〈/option〉
〈option value= "on business trip" 〈/option〉
〈/select〉
〈input type= "Submit" Name= "Btn_seek" value= "Search"
〈input type= "reset" name= "Btn_cancel" value= "Cancel"
〈input type= "Submit" Name= "Btn_browse" value= "Browse"
〈/font〉〈/pre〉
〈/form〉
Implementation of multi-conditional dynamic query
The People_seek.asp program code is as follows:
〈!--defines the following two functions to connect SQL Server databases via ADO--〉
〈％
Function getsqlserverconnection (Computer, UserID, Password, Db)
Dim Params, Conn
Set getsqlserverconnection = Nothing
Params = "Provider=SQLOLEDB.1"
Params = Params & ";D ata source=" & Computer
Params = Params & "; User id= "& UserID
Params = Params & "; Password= "& Password
Params = Params & "; Initial catalog=" & Db
Set conn = Server.CreateObject
("ADODB.") Connection ")
Conn. Open Params
Set getsqlserverconnection = conn
End Function
Function Getsqlserverstaticrecordset (conn, source)
Dim RS
Set rs = Server.CreateObject ("ADODB.") Recordset ")
Rs. Open Source, Conn, 3, 2
Set Getsqlserverstaticrecordset = rs
End Function
％〉
〈html〉〈body bgcolor= "#ffffff"
The following is the processing of form forms:
〈!--If you click the "Search" button in the form--〉
〈％
If Request ("Btn_seek") 〈〉empty Then
〈!--get the query input information--〉
Seek_name=trim (Request ("Txt_name"))
Seek_xueli=trim (Request ("Sel_xueli"))
Seek_zhicheng=trim (Request ("Sel_zhicheng"))
Seek_zhuangkuang=trim (Request
("Sel_zhuangkuang"))
〈!--If the query does not enter anything--〉
if ((seek_name= "") and (seek_xueli= "select")
and (seek_zhicheng= "select") and
(seek_zhuangkuang= "select")) Then%〉
〈center〉〈h2〉〈font color= "#ff0033"
〈%response.write "You did not enter the query criteria!" "%〉〈br〉〈br〉
〈%response.write "Please enter the criteria after the query!!! "%〉〈br〉〈br〉
〈/font〉〈/h2〉
〈input type= "button" Name= "Btn_goback" value= "return" onclick= "Javascript:history.go (-1)"
〈/center〉
〈%response.end%〉
〈% End If
〈!--defines the SQL statement to query--〉
sql_text= "SELECT * from operator where"
〈!--See if you entered the name--〉
If Seek_name= "" Then
〈!--If you don't enter a name--〉
Sql_text=sql_text
else〈!--If you enter a name--〉
Sql_name= "name = '" &seek_name& ""
Sql_text=sql_text+sql_name
End If
〈!--See if you have selected a diploma--〉
If seek_xueli= "select" Then
〈!--If you don't have a choice of qualifications--〉
Sql_text=sql_text
else〈!--If you choose a diploma--〉
if (seek_name〈〉 "") Then
〈!--entered the name of the person to be queried before--〉
Sql_xueli= "and" + "Educationallever = '" &seek_xueli& "" "
Else
sql_xueli= "Educationallever =" "&
seek_xueli& "'"
End If
Sql_text=sql_text+sql_xueli
End If
〈!--See if you have selected a title--〉
If seek_zhicheng= "select" Then
〈!--If you do not select a title--〉
Sql_text=sql_text
else〈!--If you choose a title--〉
if ((seek_name〈〉 "") or (seek_xueli〈〉 "select") Then
〈!--entered a person's name or selected a degree in front--〉
Sql_zhicheng= "and" + "grade = '" &seek_zhicheng& "" "
else〈!--selected only the title--〉
Sql_zhicheng= "grade =" "&
seek_zhicheng& "'"
End If
Sql_text=sql_text+sql_zhicheng
End If
〈!--Check to see if the current status is selected--〉
If seek_zhuangkuang= "select" Then
〈!--If you don't choose the situation now--〉
Sql_text=sql_text
else〈!--chose the current situation--〉
if (seek_name〈〉 "") or (seek_xueli〈〉 "select") or (seek_zhicheng〈〉 "select") Then
〈!--entered a person's name in front or selected a degree or selected a title--〉
Sql_zhuangkuang= "and" + "state = '" "&seek_zhuangkuang&" "
else〈!--only selected the current situation--〉
sql_zhuangkuang= "state =" "&
seek_zhuangkuang& "'"
End If
Sql_text=sql_text+sql_zhuangkuang
End If
〈!--display--〉 by record time in reverse order
sql_text=sql_text+ "ORDER BY Time Desc"
else〈!--If you click the "Browse" button in the form--〉
sql_text= "SELECT * from operator ORDER BY time Desc"
End If
myself = Request.ServerVariables ("Path_info")
〈!--connects the SQL serve database, the machine name is "Comm_server", the database name is "Comm_server", and the password is null as "sa"--〉
Set rs = Getsqlserverstaticrecordset (Getsqlserverconnection ("Comm_server", "
