.Net Developer Professional Technical Resources US-WA-Bellingham Post A Job | Post A Resume

Imagine that you need to create an on-line interface to your company's database. This database may contain a listing of products that your company sells, and you want to allow your web visitors to browse the database on-line. Say you've knocked this out in no time flat, and now your boss would like an on-line administration page, so managers can add new products to the database through an HTML Form.

This is a fairly trivial task - simply create an HTML page to render a Form which, when submitted, redirects the user to an ASP page that inserts a new record into the database. While this is easy, it is time-consuming, requiring two pages for each database table you need in the administration table. If you have 30 tables you'd like to add to the administration pages, that's 60 new ASP/HTML pages you have to create! Granted, with cutting and pasting, it wouldn't take that long, but why not create one generic database insertion script, and then 30 HTML pages that contain the appropriate Forms? After all, 30 web pages are much easier to manage than 60.

Creating a generic database insertion script has one major drawback - it's generic! That means this single script will have a hard time handling special cases, tables that have foreign keys, or tables that have constraints placed on them. For vanilla database tables, ones free of foreign keys and constraints, this generic script will work well!

When creating our HTML Forms, we need to take a couple of precautions. First off, we will need to pass the generic insertion script some variable information, such as the connection string to connect to the database we want to insert into, and the table to which we wish to add a new record. This information will be passed through HIDDEN Form variables. The Form variables into which the user will enter information should have a one-to-one correspondence with the columns in the particular table. We must take special care when naming these Form fields. In fact, we'll name each non-HIDDEN Form field with the following convention: preColumnNameInDatabase. Therefore, if we have a Text data type in our database named ProductName, we would create a text box in our Form named colProductName. We'll examine this naming convention in more detail later on in the article.

Our generic insertion script, which we'll name GenericDBInsert.asp, will begin with declaring the ADO constants we need, and reading in these HIDDEN Form variables.

<% Option Explicit %> <% 'Needed ADO Constants 'Const adCmdTable = &H0002 'Const adLockPessimistic = 2 %> <% 'Read in our form variables Dim strConnectionString, _ strTableName, _ strColumnName, _ strRedirURL strConnectionString = Request("ConnectionString") strTableName = Request("TableName") strRedirURL = Request("RedirURL")

Next, we need to establish a connection to our database using the strConnectionString variable. Also, we need to create a Recordset object, and Open the table we are interested in. The following code, which is continued from above, will accomplish just that! Note the second line from the bottom, which sets MaxRecords equal to 1. Usually, when opening a table, all of the rows are returned. However, we can limit to only having one row returned by specifying the MaxRecords property. This will save time, especially if the table is very large. For more information, be sure to read this post to learn more about the reason MaxRecords is used.

'Create a connection to our database Dim objRS, objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open strConnectionString 'Open the recordset, grabbing the requested table Set objRS = Server.CreateObject("ADODB.Recordset") objRS.MaxRecords = 1 objRS.Open strTableName, objConn, , adLockPessimistic, adCmdTable

Now, we need to use the AddNew method to add a new record to our table. Once we add a new record, we want to iterate through the Request.Form collection. We are looking for variables that are prefixed with col. If we find such a Form field variable, we want to first check to make sure it contains information. For example, if the user is suppose to enter a string, but fails to, we do not want to add a blank string to the column in the database. Rather, when designing your database, set the Default value to whatever you want it to be when someone does not enter a value.

The following code will iterate through the Request.Form collection, picking out those Form field variables that begin with col.

'Add a new record objRS.AddNew 'Now, for each form element that begins with "col" 'we need to add it to the table! For Each strColumnName in Request.Form If UCase(Left(strColumnName,3)) = "COL" then 'We need to update the column to the vaule 'entered by the user IF the user entered a 'value If Len(Request(strColumnName)) > 0 Then 'We need to hack of the col part to correspond to 'the correct table column objRS(Mid(strColumnName,4,Len(strColumnName))) = Request(strColumnName) End If End If Next objRS.Update 'needed to solidify our changes!

Once we've iterated through each of the Form field variables, we must call the Update method to solidify our changes. Once we've added our record, we are ready to clean up our ADO objects, and wisk the user on to the redirection URL specified:

'Clean up, and redirect the user objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing Response.Redirect strRedirURL %>

Neat, eh? Remember that this is the only database insertion script! All of our HTML Forms will have the ACTION property set to GenericDBInsert.asp, using METHOD=POST. So, what will our HTML Forms look like? That depends on what the table design looks like. We must provide three HIDDEN Form variables: ConnectionString, RedirURL, and TableName. We also must provide a Form field for each non-Autonumber column in the table.

For example, if we had a table that contained the following structure:

Our Form would contain three HIDDEN Form variables, and three form fields - a text box for ProductName, a text box for ProductPrice, and a checkbox for OnSale. Note that when we have a Yes/No or bit field, which requires a checkbox, we must set the VALUE of the checkbox to True. If the user does not check the checkbox, the default value for the Yes/No field will be entered into the database, which is False.

To create an HTML Form for the above scenario, our code would look like:

<HTML> <BODY> <FORM METHOD=POST ACTION="/generic/GenericDBInsert.asp"> <INPUT TYPE=HIDDEN NAME="ConnectionString" VALUE="DSN=ProductInfo"> <INPUT TYPE=HIDDEN NAME="RedirURL" VALUE="/generic/ThankYou.asp"> <INPUT TYPE=HIDDEN NAME="TableName" VALUE="Product"> Name: <INPUT TYPE=TEXT NAME=colProductName><BR> Cost: <INPUT TYPE=TEXT NAME=colProductPrice><BR> On Sale: <INPUT TYPE=CHECKBOX NAME=colOnSale VALUE=True><BR> <P><INPUT TYPE=SUBMIT> </FORM> </BODY> </HTML>

That's all there is to it! You now have a very robust database insertion script! Before I go, let me leave you with a challenge: try to create an ultra-generic database insertion script. This ultra-generic script would be one ASP page, and would do two things: display the form for a particular table, and insert the data into the database. This script can have two variables passed through the querystring: TableName and Mode, where Mode is either Edit or Insert. Creating such a powerful script would make all of the administration pages squeeze into one ASP page!

Happy Programming!

Attachments:

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info

Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers