101 Forms and 1 Database Script in ASP

101 Forms and 1 Database Script in ASP - How To Do It
(Page 2 of 4 )

First, in designing the web architecture for integrating a database, we assume a single data source (Conn).

To keep this straightforward, we have a standard ‘web form’ defining some input variables. Here is our ‘web form’:

1. In ALL web forms, take a recordset and output to a form object using HTML Textboxes to display the data. In the name property of the textbox put "name=<%=rs("columnName").name%>". In the value property of the same textbox write "value=<%=rs("columnName").value%>".
2. Insert a ‘hidden’ input type in the form. The name property is ‘Name=xSqlCommand’ and value=’INSERT’. This represents the sql command you wish to execute. Examples are INSERT, UPDATE, and DELETE.
3. Insert a ‘hidden’ input with the name property ‘name=xTABLE’ and value=’Tablename’. The Tablename is the database table from which the recordset originates. Often, TableName is server-side as it was used in the SQL statement to derive the recordset.
4. For UPDATE and DELETE statements, insert another ‘hidden’ input type in the form, ‘name=xPrimaryKey’ and value=’RowPrimaryKeyValue’. Often the FieldName primary key is ID or nameID or something like that.
5. For UPDATE and DELETE statements, insert another ‘hidden’ input type in the form, ‘name=xPrimaryKeyFieldName’ and value=’strPrimaryKFieldName’. The string value of the primary key column name.
6. Submit all web forms to the same ASP script. I.e. ACTION=udpatedb.asp

In the method below *NOTE* the little ‘x’ that will tell us that the requested value is not a database field name. The purpose of the ‘x’ is to separate our database fields from other inputs. Other inputs can be security, user, or application information used in script processing.

An additional comment before the code: This script requires you to have a solid understanding of programming and will NOT be a simple ‘cut and paste’. Read and make customizations to fit your software. This script has worked extremely well for me and I hope that you can make good use of it.

Here we are going to reconstruct the original SQL SELECT statement as a SQL UPDATE, DELETE or an INSERT statement from the REQUEST collection. By doing this, the script is completely dynamic based on its input parameters. My intent is to layout the architecture, but you've got to apply some modifications. In the script below, I have placed notations where some customization would be useful. Our basic script outline:

1. Retrieve a single record from the database table to be updated.
2. Place into an array the field names and data-types.
3. Read the submitted name / value pairs into strings.
4. Request the SQL command type.
5. Construct SQL statements from name / value strings and SQL command type.
6. Execute the SQL statement.

blog comments powered by Disqus