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:

<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: