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:
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
Next, we need to establish a connection to our database using the
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.
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
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
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:
Neat, eh? Remember that this is the only database insertion script! All of our
HTML Forms will have the
ACTION property set to
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:
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
OnSale. Note that when we have a
bit field, which requires a checkbox, we must set the
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
To create an HTML Form for the above scenario, our code would look like:
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:
Mode is either
Insert. Creating such a powerful script would make
all of the administration pages squeeze into one ASP page!