[Foreword]
We often encounter multiple query problems, and long SQL statements often make people confused. Especially when the client partially fills in the query conditions, it will be even more difficult to use ordinary methods.
The following cleverly uses the identity where 1=1 (in fact there are many, just let it value TRUE) to solve this problem.
[Summary of text]
'subject information title
'company The name of the company publishing the information
'content content of published information
'address company address
'infomation Company Profile
'note related instructions
The above values are submitted by FORM, and then the corresponding values are obtained through: subject=trim(Request.Form(subject)), etc.
<%
'This function is critical! --------------------------
Function sql(a,b,sqls)
if b<> then 'If the client does not submit this value, the corresponding SQL statement will not be generated.
sqls=sqls & and & a & like '% & b & %'
end if
sql=sqls
End Function
'-----------------Call the database
Set conn=Server.CreateObject(ADODB.Connection)
DBpath=Server.MapPath(/database/mydb.mdb)
Conn.Open driver={Microsoft Access Driver (*.mdb)};pwd=;dbq= & DBpath
Set rs=Server.CreateObject(ADODB.Recordset)
sqls=select * from mytable where 1=1
'Just call the above function below, you can call it many times (theoretically any)
sqls=sql(subject,subject,sqls)
sqls=sql(company,company,sqls)
sqls=sql(content,content,sqls)
sqls=sql(address,address,sqls)
sqls=sql(infomation,infomation,sqls)
sqls=sql(note,note,sqls)
sqls=sqls & order by id desc
rs.open sqls,conn,3,2
%>
[Conclusion]
If there is no key function Function sql (a, b, sqls), we can imagine how many judgments are needed one after another!