SQLProcess()

Use of the SQLProcess function is very simple. The form page is created as usual. The name attribute of the submit input must be "btn" and the value must be either Search, Add, Update, or Delete. The connection object should be named "conn" since it referenced and used within the script as "conn". I include a file called "process.asp" that contains the scripts used here on the form. The adovbs.inc file should also be included on the form. Additional hidden inputs should be included on the form as described below. The SQL statements generated will operate on a single table. Joins aren't supported. Tables should include a numerical key field that is not an autonumber field. This function was developed using Access '97.

-tableName
-idyourfieldName
-add
-op
-logOp
-postLogOp
-paren

 

 

-tableName
<input type="hidden" name="-tableName" value="yourTableNameHere">


This tag is required to follow the form tag on any form using the SQLProcess function.

 

 

-idyourfieldName
<input type="hidden" name="-idyourFieldName" value=""> (for Add actions)
<input type="hidden" name="-idyourFieldName" value="fieldValue">(for Update and Delete actions)

This input tag is required for Add, Update and Delete actions. It should be included on forms following the -tableName tag. The field yourFieldName should be an integer field with no auto entry options enabled. The SQLProcess function will increment the value entered for new records. This field is used as a key field for updating and deleting records. For Add actions, you simply need to include the "value" attribute as an empty string.


These first two tags are really all that are required for Add, Update and Delete actions. The remainder of the tags are all used for creating SELECT statements.


 

 

-add
<input type="hidden" name="-addfieldname" value="fieldvalue">

This tag is useful if you use the same form page for adding and searching records. In some cases you may want to include a default value such as a creation date as a hidden input on an add form. However, the hidden value would also automatically be included on any searches submitted and make searching impossible from the same form. By adding "-add" in front of the field name, the value will be omitted from SELECT statements and only used to add the value to new records.

 

 

-op
<input type="hidden" name="-op" value="operator">

In building SQL statements, the default comparison operator is always equals for name/value pairs. fieldName = value. The -op tag is used immediately before the field input to specify a comparison operator other than equals. The available operator values are:

ne - not equal
bw - begins with
ew - ends with
cn - contains
lt - less than
le - less than or equal to
gt - greater than
ge - greater than or equal to

This example:

<input type="hidden" name="-op" value="cn">
<input type="text" name="city" value="Minneapolis">

will return

     ...WHERE city LIKE '%Minneapolis%'...

in the SQL statement. The text input by itself without using the -op tag would return:

     ...WHERE city = 'Minneapolis'...

 

 

-logOp
<input type="hidden" name="-logOp" value="or">

The SQLProcess function uses AND as the default logical operator following name/value pairs. Such as:

     ...WHERE city = 'Minneapolis' AND state = 'MN'...

The -logOp tag is used to change the logical operator to OR. As in:
<input type="hidden" name="-logOp" value="or">
<input type="text" name="city" value="Minneapolis">
<input type="text" name="state" value= "MN">

     ...WHERE city = 'Minneapolis' OR state = 'MN'...

This tag should preceed the form input whose values the operator is to follow.

 

 

-postLogOp
<input type="hidden" name="-postLogOp" value="operator">

Multiple instances of a field name may be submitted from a form, such as with a select list where multiple selections are possible or with multiple values submitted with checkboxes for the same field. The SQLProcess function puts all instances of like field names together in parentheses in the SQL statement. The -postLogOp tag determines the logical operator that follows the closing parenthesis. Again, AND is the default value and requires no tag. For example:

<input type="hidden" name="-op" value="cn">
<select name="city" size="3" multiple>
<option value="Minneapolis">Minneapolis
<option value="Brainerd">Brainerd
<option value="St. Paul">St. Paul
</select>
<input type="hidden" name="city" value="">

would result in the following segment if the first two options are selected on the form.

     ...WHERE (city LIKE '%,Minneapolis,%' AND city LIKE '%,Brainerd,%') AND...

However, submitting the form with the first two options selected and preceding the set of select tags with the -postLogOp tag with value="or" would return:

     ...WHERE (city LIKE '%,Minneapolis,%' AND city LIKE '%,Brainerd,%') OR ...

<input type="hidden" name="-postLogOp" value="or">
<input type="hidden" name="-op" value="cn">
<select name="city" size= "3" multiple>< BR><option value="Minneapolis">Minneapolis< BR><option value="Brainerd">Brainerd
<option value= "St.Paul">St. Paul< BR> </select>
<input type="hidden" name="city" value="">

Notice the "hidden" input tag at the end with the "value" attribute set to an empty string. This is necessary to include after an input where multiple values for the same field can be submitted. The "contains" operator (-op) tag should also be used when searching fields that contain multiple values. Also notice the commas that are placed before and after the city names in the SQL statement. The commas are used to assist in searches. If three values were selected in a select list, or three checkboxes checked where the checkboxes have the same field name, the value returned by "request.form("fieldName")" would be something like "1, 10, 100". This is not a good arrangement for searching values in fields where multiple values exist. Using a SQL statement with "WHERE fieldName LIKE '%1%'" would return fields with 1, 10, or 100. Likewise, searching for 10 would return 10 and 100.

When the SQLprocess function builds an INSERT statement with multiple values submitted for a single field, the values are put into a comma separated list like ",1,10,100,". Therefore, when a search is submitted for "1", the function puts commas around the search value preventing records being returned that have a value where the search value is a subset.

 

 

-paren
<input type="hidden" name="-paren" value="open/closeParen">

The -paren tag is used to enclose two or more separate inputs. The "value" parameter is simply an open or close parenthesis. A likely use for this tag is where OR is used between two values and the two values need to be separated by parentheses from the rest of the SQL statement. The following example uses the -paren tags:

<input type="text" name="city" value="Minneapolis">
<input type="hidden" name="-paren" value="(">
<input type="hidden" name="-logOp" value="or">
<input type="text" name="county" value="Hennepin">
<input type="hidden" name="-paren" value=")">
<input type="text" name="state" value="MN">

The above example would return the following SQL segment:

     ...WHERE city = 'Minneapolis' AND (county = 'Hennepin' OR state = 'MN') AND...

Use of the -paren tag requires that a value is submitted for the field following the opening parentheses and for the field preceeding the closing parentheses. Otherwise, an error in the SQL syntax will likely occur.

 


There are many configurations that can be arranged for adding, updating, deleting and searching records from a web page form. One suggestion is to use an include file with the following code and include it on the response page to any form (the page indicated in the form's "action" attribute):

Dim strBtn
Dim queryString
Dim strSQL
Dim rs

strBtn = Request.Form("btn")
Select Case strBtn
   Case "Search"
      queryString = Request.Form
      strSQL = SQLProcess(queryString)
      Set rs = conn.Execute(strSQL)
   Case "Update", "Delete", "Add"
      queryString = Request.Form
      strSQL = SQLProcess(queryString)
      conn.Execute(strSQL)
End Select

This will pass all the request information to the function wich will return a SQL statement that is then executed. If the submit button selected was Search then the recordset returned is referenced by "rs". For other actions the SQL statement is simply executed. If, after executing an Add or Update, you wish to code a SELECT statement to retrieve the record just acted on, you can find the record with the variable "intRecID". "SELECT * FROM table WHERE idfieldname = " & intRecID
That should return the record just added or updated.

Likewise, if the page to display the search results only needs code for a Search action, the following code will suffice:

Dim queryString
Dim strSQL
Dim rs

queryString = Request.Form
strSQL = SQLProcess(queryString)
Set rs = conn.Execute(strSQL)

No matter how large the search form is, this is all the code needed to build the SELECT statement, execute it and return the recordset.

Other modifying clauses can easily be appended to the end of the returned statement if required. For example:

strSQL = SQLProcess(queryString)
strSQL = strSQL & " ORDER BY someField "

*****