Published: Wednesday, March 01, 2000
SQLProcess - A Powerful SQL Statement Generator
By John Sanborn
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:
<form action="addResponse.asp" method="POST">
<input type="hidden" name="-tableName" value="myTable">
<input
type="hidden" name="-idkeyField" value="">
<input type="text" name="fieldOne">
<input type="text"
name="fieldTwo">
<input type="text"
name="fieldThree">
<input type="submit" name="btn" value="Add">
</form>
|
Use the -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 Add.
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
coding.
I usually put my connection information in an include file. Also note that
the connection object should be named conn.)
<%
Dim conn
Set conn = Server.CreateObject
("ADODB.Connection")
conn.ConnectionString = "sqltest"
conn.Mode =
adModeReadWrite
conn.open
Dim queryString
Dim strSQL
queryString = Request.Form 'Put the querystring
into a variable
strSQL = SQLProcess(queryString) 'This function will
return an INSERT
statement
'based
on the information submitted from the form.
conn.Execute
strSQL 'Execute the SQL
statement
conn.Close 'Clean up objects
Set conn = Nothing %>
|
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.
<% Dim queryString
Dim strSQL
Dim rs
queryString = Request.Form
'Put the querystring into a variable
strSQL = SQLProcess(queryString)
'This function will return an INSERT
statement
'based
on the information submitted from the form.
conn.Execute strSQL
'Execute the SQL statement
Set rs = conn.Execute("SELECT *
FROM table WHERE keyfield = " &
intRecID)
conn.Close 'Clean up objects
Set conn = Nothing
%>
|
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 Update and Delete.
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
page.
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
the SQLProcess() function. The first page we'll call searchAdd.asp,
the second will be
updateDelete.asp.
<!-- #include file="openConn.asp" -->
<% Dim queryString
Dim strSQL
queryString = Request.Form
If Len(queryString) > 0 Then
strSQL =
SQLProcess(queryString)
conn.Execute strSQL
End
If
conn.Close
Set conn = Nothing %>
<html>
<head><title>Search/Add</title></head>
<body>
<form
action="updateDelete.asp" method="POST">
<input type="hidden"
name="-tableName" value="myTable">
<input type="hidden"
name="-idkeyField" value="">
<input type="text" name="fieldOne">
<input type="text"
name="fieldTwo">
<input type="text"
name="fieldThree">
<input type="submit" name="btn" value="Search">
<input
type="submit" name="btn"
value="Add">
</form>
</body>
</html>
|
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.
<!-- #include file="openConn.asp" -->
<% Dim queryString
Dim strSQL
Dim rs
Dim
actionPerformed
actionPerformed = Request.Form("btn")
queryString =
Request.Form
strSQL = SQLProcess(queryString)
If actionPerformed = "Add"
Then
conn.Execute strSQL
Set rs = conn.Execute("SELECT
* FROM table WHERE keyfield = " &
intRecID)
Else
Set rs =
conn.Execute(strSQL)
End If
conn.Close
Set conn =
Nothing %>
<html>
<head><title>Update/Delete</title></head>
<body>
<%
While Not rs.EOF %>
<form action="searchAdd.asp"
method="POST">
<input type="hidden" name="-tableName"
value="myTable">
<input type="hidden"
name="-idkeyField "value="<%= rs("keyField")%>">
<input type="text" name="fieldOne "value="<%= rs("fieldOne")
%>">
<input type="text"
name="fieldTwo "value="<%= rs("fieldTwo") %>">
<input type="text" name="fieldThree"
value="<%= rs("fieldThree") %>">
<input type="submit" name="btn" value="Update">
<input
type="submit" name="btn" value="Delete">
</form>
<%
Wend
rs.Close
Set rs = Nothing
%>
</body>
</html>
|
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
use SQLProcess() instead. Read carefully the included documentation
to see what other hidden inputs are available and how to use them.
Attachments:
Download process.asp in text format
Documentation:
Read the detailed SQLProcess documentation
