Implementing multi-condition Fuzzy query SQL statement under ASP

condition | statement Author: Lu Haipeng
From: www.DeepTeach.com
Absrtact: This article is mainly aimed at some ASP programming experience and the basis of SQL statement enthusiasts how to write fuzzy query statements and multiple conditions query.

Many netizens ask how to write fuzzy query statements and multiple condition queries, today, netizens love again asked this question, just a few days ago to a unit to write an OA related to multiple conditions fuzzy query, the query more, more professional nouns, here I tidy up a bit, assuming the name, sex, telephone number ... As the field name in the database.

Usually write a simple fuzzy query SQL statement format can be the following example:


Note: 1, WHERE keyword followed by the filter data conditions, percent is a wildcard, can represent multiple any character, if the underscore _ on behalf of a single character.
Examples are as follows:
Sql= "SELECT * from table name where name like '%" &request.form ("name") & "% ' and sex like '%" &request.form ("Sex") & "% ' a" nd telephone like
'% ' &request.form ("call") & "%"
The above SQL statement uses three ambiguous query criteria: name, sex, telephone, and of course we can use a similar
The way to construct more conditions. In this way we have implemented a multiple-condition fuzzy query, the actual try, the problem came out!!! If the query field for the database has a value, it's fine, but if it's the bottom one:
Name Sex Telephone
Www.DeepTeach.com 87654321
When you fuzzy query: "Tel: 5432" will not be able to output the record, this is because the "gender" is not a value, so after two and operations after the result is false/0, there is no output. Obviously the three fields in the database must contain field values, otherwise the correct output will be omitted.
The correct input in the database should be this:
Name Sex Telephone
www.DeepTeach.com NULL 87654321

The logical value of the <null> value is 1, so the result is TRUE/1 after two and operations, and the record can be output.

So how to implement such a multiple conditional fuzzy query in practice? This is how I come to realize:

Name=request.querystring ("name") ' Name
Sex=request.querystring ("Sex") ' gender
Call=request.querystring ("call") ' Phone

Sql= "Select * from table name where 1=1" ' 1=1 error Avoiding all query fields being empty

If name <> "" Then
Sql= SQL & "and name like '%" & name & "%"
End If
If sex <> "" Then
Sql= Sql & "and sex = '" & Sex & "" ' This is not a fuzzy query.
End If
If call <> "" Then
Sql= SQL & "and" "%" & Call & "%"
End If
......
Here, you have to notice the name, sex, telephone these three fields in the database type should be "text" type, otherwise the query will appear "data type Mismatch" error.
If none of the three conditions are entered, click "Query" will display all the records in the database, perhaps this is what you do not want, you should add a judgment: when three conditions are not entered, display "Please enter query conditions" and interrupt the output to the customer browser (response.end) examples are as follows:

If Request.Form ("name") = "" and Request.Form ("sex") = "" and Request.Form ("call") = "" Then
Response.Write ("Please enter query criteria (ambiguous query)")
Response.End
End If
Remember: sql= "select * from table name where name ... Phone like ... "
Must be entered in one line, not a carriage return, because the VBS multiline is considered multiple statements, which is a common mistake many beginners make. If you want to write more than one paragraph, you can use the above method to gradually increase the various parts of the query statement on the basis of existing variables and implement it within the same variable. Before closing quotes or after opening quotes you need to add spaces so that you don't have to put a few words together when the string is connected.