By John Sanborn
SQLProcess - A Powerful SQL Statement Generator
Usually when I put together an ASP application to provide some dynamic content from a database to a web page, setting up the actual display of the data on the web page is the easy part. Most of the work ends up in putting together an administrative back-end, usually password protected, that allows adding and editing records in individual tables. Coding all the script to build SQL statements that add, update, search, and delete records for all of the tables that require individual attention can be a lot of work. This function takes care of much of that coding work by taking the query string and building a SQL statement based on the information submitted from the form. Using this function only requires the use of a few extra hidden input HTML tags.
Let's start with a simple "add" form to see what is required to make use of this function:
-tableName hidden input to pass the name of the table that you want
to add the record to. The
-idkeyField hidden input passes the
name of the key field, the field name appended to the text
-id, which should
be a number field with no auto entry options. (The script could easily be
modified to allow auto entry fields to increment themselves. You would just need
to remove the parts of the script that determine the next number entry and
include it with the
INSERT statement.) The only other requirement for this add
form is the submit button. The name must be
btn and the value must be
The other form elements are simply standard HTML form elements such as text
fields, radio buttons, checkboxes, select lists, etc. The form element names,
however, should be the same as the column names in the table that you want to
add the record to.
Now lets look at the
addResponse.asp page. Normally, if my add form had a dozen or
more inputs, then the top of my response page would begin with a lot of code to
determine which fields had data and to construct a SQL statement to insert the
new record. By using the
SQLProcess() function you only need a fraction of the
I usually put my connection information in an include file. Also note that
the connection object should be named
That is all that is required to create the INSERT statement regardless of how many fields are on the form or what data is entered into the form elements. The function works basically the same whether it's an INSERT, SELECT, UPDATE, or DELETE statement that is generated. The function determines which statement to create by the value of the submit button, which must be Add, Search, Update, or Delete.
I usually find that when I add a new record to a table,
I want to return the new record to display or edit the information just added.
For this reason, the variable
intRecID is declared outside of the function.
This makes the key field number of the new record available for a
SELECT statement. Therefore, I can add this line of code into the
code above and return a recordset with the new record.
Now I can use the recordset object to access the new record. I would use the exact same code if I were updating a record and wanted to get a recordset with the updated record.
Adding, updating, and deleting records is pretty simple and straightforward.
Updating and deleting would most likely be executed from the same edit form. You
simply need the hidden inputs described earlier and the submit buttons named
btn with values of
An edit form would also need the
value for the
-idkeyField hidden input included with
rs("keyField") %>. Creating a search form can be a bit more
complicated. There are several specific hidden inputs that can be used on a
search form that will determine how the
SELECT statement is created. Those are
fully explained in the documentation that can be downloaded with the script
The following is a simple example of how two forms can
be used to add, search, update, and delete records from a single table using
SQLProcess() function. The first page we'll call
the second will be
This first form provides fields for searching or adding
records. (Of course, you'll probably want to put your form in a table
and use field labels!) The code at the top is all that is needed to process any updates or deletes submitted
from the second page shown below. The code at the top of the second page
will process adds and searches. Note also that the
adovbs.inc file is required where the SQLProcess
function is used.
All of these actions can be set up in a variety of
different ways. Wherever you write tons of code to build SQL statements you can
SQLProcess() instead. Read carefully the included documentation
to see what other hidden inputs are available and how to use them.
process.aspin text format