Online Database Editing Program, Part 2By Richard Chisholm
In Part 1 we examined how to create an ASP page with two frames, one at 100% and the other at 0%. We also discussed the initial form page. In this part we will discuss the remainder of the application!
Recall that once the form is submitted, the
logic.asp page runs. First, it checks against a hard
coded array of authorized users (I did this because only a few people use this, so it is the most efficient
way). (Read Are Session Variables Really Evil? for more information!)
I won't go into it here, but if the user is not authorized an alert function tells them such, and the login
text is reloaded. Otherwise a Sub named
Database is called, which uses the
to find all databases in a directory (hard coded into the script, but usually all databases are in the same place).
(To learn more about the
FileSystemObject be sure to read the
In the above, the
if loops find the database files, and create the HTML in a
text. Then we dump the HTML in
text into the text span on the
display page, and you get a radio-button list of databases. Note two things:
1.) you must use
Server.URLEncode, or database/table names with spaces in them will not get fully passed by the querystring.
saying "go to the frames document (the top of the page hierarchy), then go to the logic frame to find the JS
get()code, because there are a few things to point out:
for...loop will run through the radio buttons and grab the value for the checked one.
However, this does not work when there is only 1 button, hence the
if statement following it. In
the last line the code is reloading the
logic.asp page and giving it new data, and the following
is done in the
Here the database is saved into a session variable, including its path. The line with
is one of two places in these files where you will need to hard code something (the other being already
mentioned). This is necessary since the location of the databases will vary depending on the server. This
part uses the OLEDB 4.0 Jet because the type of schema used here doesn't allow ODBC.
(To learn more about OLEDB, be sure to check out OLEDB For Me.) So be
sure you have this installed, or you will get a Provider error. (If you don't have this, you can go to MS's
web site to download it. The URL is http://www.microsoft.com/ado/.)
This particular schema looks up the primary keys on all the tables in the database, which we need identify
since Roman's editor requires the primary key column. However, there is one problem: just because a column is
a primary key doesn't necessarily mean it will work, so make sure before hand that all your tables have
AutoNumber in the primary key. I did find a way to check for
AutoNumbers, but it
involves using another schema and sometimes generates a runtime error.
Email me if you want to know... Here
Again the program builds a list of radio boxes using all the tables in the selected database. This time both
the table name and the primary key column are embedded in the
value="" field of each box. This
will be passed back to
logic.asp when the user selects a table. At this point we are almost
ready to load Roman's db editor, but first the user encounters one last page. This one parses the two values
in the last submission and saves them into the Session, and then generates a drop-down box that gives them
three choices of how they want to enter the editor:
1.) Search for a specific record
2.) Add a new record
3.) view all the records.
This is especially helpful for large databases, and when you need to edit specific records. If the user wants to search, they select that choice, enter a parameter, and hit submit; otherwise they can just select one of the other two choices, hit submit, and they're in. Now that we are finally in Roman's db editor, here are the significant changes.
- Added a search box at the top of the page
- Commented out the section where Session variables are assigned from the
- Now using DSN-less db connection instead of DSN
- Added a "search" option, which runs inside the
Here is the search coded added to
Ok, here is what's going on. If the action is to search, we grab the search parameter from the
and start our select statement, and if not then the select statement to list every record is run. This is
where it gets a little tricky. We need to search ALL the columns in this table to be sure we have complete
results, but we also need to do it on the fly. This is another place where the Schema object comes in handy.
One problem with
adSchemaCollumns is that although it does work, unfortunately it also pulls out
every column in a database, not just a specific table. So we get around this by checking the table name and
the data type, so that the SQL statement only searches columns with text in them. The
130 is the
constant for text fields in Access (found in the Appendix to ADO 2.0 Programmers Reference). When the
Do...Loop is finished, we cut off the last three characters, being "
SELECT statement is executed. (To learn more about database schemas, be sure to read
Listing the Tables and Columns of a Database.)
I have tried to cover all of the non-basic stuff here in this article. The included pages also include a lot
So, to wrap things up, this can be a very useful tool. I realize that some of the coding can get hairy and
down right ugly, but if you plan on having other people use it then the effort is worthwhile because to an
average user the transitions are seamless. I haven't explained everything, but what I didn't cover here
should be easy to figure out. Hopefully this app will also increase your understanding about schemas and
how they can be used, a part of ASP/ADO that doesn't get a lot of coverage. I have left all of my
Response.Write code, which may also help you delve into things like
Richard Chisholm is the Webmaster of a large California law firm, as well as an independent developer.