To read the article online, visit http://www.4GuysFromRolla.com/webtech/082699-1.shtml

A Generic Database Record Insertion Script


The Newest Assignment: For your latest data-driven ASP application, create web-interfaced administration pages that allow folks to easily insert new records into a number of database tables.

Your Response: Crap! That will take a long time to write all those pages.

My Response: Nope, it's easier than you think!


You've probably had to do a task like this in the past. While it is not difficult to create the needed HTML forms and the accompanying ASP pages, it is annoying and time consuming. Say that this project required twenty administration pages: that means you've got to create twenty forms and twenty ASP pages that take the data from that form and insert the data into the database. Well, not anymore!! :)

Using the following script, you will only need to create the 20 forms. Each of these forms can specify this ASP script in the FORM's ACTION tag. All you will need to do is make sure that your INPUT tags' names are the same as the column names. For example, say we have a table that is defined as:

Contact
ContactIDAutonumber
NameText
PhoneNumberText
EmailText

All we'd need to do is create a form with text elements to enter the Name, Phone Number, and Email. Remember, we want our FORM's ACTION tag to point to our generic ASP script that will insert a new row into a table. Let's say that this ASP script is named globalUpdate.asp. We will also need to setup a HIDDEN variable (named TableName). This HIDDEN variable needs to store the name of the table that we want to insert a record into. (Contact is the name of the table, in this instance).

Now, let's look at our FORM code:

<FORM ACTION="/scripts/globalUpdate.asp" METHOD=POST> Name: <INPUT TYPE=TEXT NAME=Name SIZE=40> <P> Phone Number: <INPUT TYPE=TEXT Name=PhoneNumber SIZE=15> <P> Email Address: <INPUT TYPE=TEXT NAME=Email SIZE=25> <P> <INPUT TYPE=HIDDEN NAME=TableName VALUE="Contact"> <INPUT TYPE=SUBMIT> </FORM>

That's all there is to it for the form! Now, we need to look at our code for globalUpdate.asp.

<% Option Explicit 'Our ADO constants we'll need Const adOpenForwardOnly = 0 Const adLockOptimistic = 3 Const adCmdTable = &H0002 'Create our connection object and open a connection to our database Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=TestDB" objConn.Open 'Create a recordset object Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") 'Open a table view for the table name specified by Request("TableName") Dim strTableName strTableName = Request("TableName") objRS.Open strTableName, objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable 'Add a new record... objRS.AddNew 'Iterate through the fields of the table... Dim fld For Each fld in objRS.Fields 'If a value for the column name was passed in, 'set the column name equal to the value passed through the form... if Len(Request(fld.Name)) > 0 then fld.Value = Request(fld.Name) end if Next 'We're done, so update the record objRS.Update 'Clean Up... objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing 'Send the user to some confirmation page Response.Redirect "RecordAdded.htm" %>

That's all there is to it! What makes the script universal is the For Each loop that steps through each field in the recordset object (i.e., each column in the table). It determines if a value has been entered for the column name in the form. If it has, it sets the column equal to the value entered in the form. The reason the script checks to make sure the value was passed by the form is because some times you won't want to set a column value in a table. For example, we don't want to explicitly set the value of ContactID, since it is an Autonumber field. So, we simply didn't include it in our form, and we'll let our database set the autonumber value. Finally, once the script has set all the columns passed through the Request object, the record is updated. We then clean up by freeing our ADO objects, and then redirect the user to some type of confirmation page. That's it!

Since we pass in the name of the table, and since we step through the columns of the table in an ambiguous way, this script will work with any table! Well, I take that back; it will work with any table that doens't have a foreign key relationship with another table.

Well, I hope you have learned something new... at least I hope you find this script useful, and can use it in your projects!

Happy Programming!


Attachments:

  • The Code for globalUpdate.asp in text format


  • Article Information
    Article Title: A Generic Database Record Insertion Script
    Article Author: Scott Mitchell
    Published Date: Thursday, August 26, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/082699-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers