Occasionally one discovers a subroutine that greatly simplifies a complex ASP page, transforming spaghetti-like code into something that is easy to maintain. In this article I share one such function used when I need to produce an SQL query from a number of optional criteria posted by the user.
Building an SQL string from optionally posted criteria is tricky. You need to check if the user posted a value since only then is the criteria included in the query. Numeric entries are different from character entries and multiple-selection fields require additional processing to produce a valid SQL query. Fields that might contain single quotes need special attention to prevent syntax errors.
The AddCriteria function (listed later) elegantly encapsulates the logic to build an SQL statement by adding optional criteria only if the user posted data for a field. Here is an example of how one would use it:
The Code
SQL="select * from DataTable"
SQL=AddCriteria(SQL,"Status","=","Status","Chr")
SQL=AddCriteria(SQL,"PCode","=","Code","Num")
SQL=AddCriteria(SQL,"Price",">","LowPrice","Num")
SQL=AddCriteria(SQL,"Price","<","HighPrice","Num")
SQL=AddCriteria(SQL,"Category","in","Catg","Num")
SQL=AddCriteria(SQL,"City","in","City","Chr")
SQL=AddCriteria(SQL,"Name","like","Name","Chr")
The
SQL query is started by specifying everything up to the Where clause. Then the
AddCriteria function is used to assemble optionally posted values and append it
to the SQL expression. The AddCriteria parameters are almost self-explanatory:
Parameter 1: The SQL expression as constructed thus far.Parameter 2: The
database field name for this criterion. Parameter 3: The SQL comparison operator
for this criterion.Parameter 4: The form field name of the posted value.
Parameter 5: The database field's data type: numeric (Num) or character (Chr).
If one posted the following data to a page containing the code snippet
above, the following SQL statement would be generated: Posted
Data:Status=A&HighPrice=5&Catg=101&Catg=203&Name=Edward
SQL
Produced:select * from DataTable
where
Status = 'A' and Price < 5 and
Category in (101,203) and Name like '%Edward%'
Here is the source code for
the AddCriteria function:
Function
AddCriteria(SQL,DBField,Comparison,FormFld,DataType)
Dim
Value,Val,ValArray,ClauseStyle,Connector
AddCriteria=SQL
Value=Request.QueryString(FormFld)
if len(Value)=0 then Exit Function
Comparison=trim(Comparison)
DataType=trim(DataType)
ClauseStyle=ucase(Comparison
& ":" & DataType)
select case ClauseStyle
case "=:CHR"
Value="'" & Replace(Value,"'","''") & "'"
case "=:NUM",
">:NUM", "<:NUM"
if (not isNumeric(Value)) then Value=0
case
"IN:CHR"
Value=Replace(Value,",
",",")
Value=Replace(Value,"'","''")
Value="('" &
Replace(Value,",","','") & "')"
case
"IN:NUM"
ValArray=split(Value,",")
Value=""
for each Val in
ValArray
if isNumeric(Val) then Value=Value & "," &
trim(Val)
next
if len(Value)< 2 then Exit Function
Value=Mid(Value,2) '** remove leading comma
Value="(" & Value &
")"
case "LIKE:CHR"
Value="'%" & Replace(Value,"'","''") &
"%'"
case else
Err.Raise 1,"Function AddCriteria",_
("Missing case
for '" & ClauseStyle & "'")
end select
Connector=" where " '**
for first criterion only
if 0
