Using System Objects in MS SQL-Server to Create a Web-Based Database Admin Interface, Part 2By Joseph Anderson
In Part 1 we looked at our generic database administration page from a high-level, conceptual view. In this part we'll dig into the code and examine how it all comes together!
This is where SQL Server can help you get the necessary information about the table your user wants to
manipulate, without you going in and modifying the tool code. The
syscolumns table in SQL Server
keeps all sorts of useful information that can be queried to determine aspects. The below snippet of code
AddData.asp) illustrates a very generic way to display the form fields for a database
gathered from information from the
sysobjects table. (The complete code for this application
can be downloaded at the end of this article!)
Then, on the page that accepts the data that was submitted, you could check the validity of the data entered.
Since the name of each element of the form is EXACTLY the same as the column name in the table, you know that
the form element
Request.Form("" & objRS("name") & "") corresponds to the column of the same name.
If you are new to this stuff don't worry, I can assure you I have given myself numerous headaches trying to
get this stuff strait myself ;).
Perhaps there is still some confusion as to what this is doing, so (if for no other sake than my own)
I will try to give a more precise description of what this is doing. If you look at the SQL statement above
you will see that we are pulling all the column names (
name), the data types for each field
maximum length for the field (
length), and the binary true/false value that states whether or
not the field is required (
isnullable) for the specified table (
one of the variables pre set in the
params_functions.asp, set to a valid table name within the database). So the record set might
look something like this:
Therefore when it is generating the HTML form, it looks at the
type to determine the kind of
input to use (such as blob fields having a
textarea, and all others assigned an
Note: with a little modification, you could have it output bit fields as a checkbox, or a true/false
dropdown list! It will then look at the
length to determine the maximum size of the input field.
And finally it will name the input field the same as the
name from corresponding record returned by
the query. So then when you submit the form, it will know that each form element value is added into the
database field corresponding with the form element name! Pretty neat, huh?
A few side notes: I wanted to add the global parameters into the
global.asa file, but figured
that if I just made 1 variable file and included it in all the tool pages, the tool would be a little more
self-contained. And to make configuring this tool even easier, I have built an interface into the tool itself
(on the index page) to configure all the variables from your web browser (rather than editing the
params_functions.asp page directly). Also, I used an admin login to the database and am not sure
how the tool will react if a user login with confined permissions is used (instead of the admin
login). And finally, please note that for the config tool (
index.asp) to work properly, the
system must have write permissions to the folder where this tool is located on your server, otherwise you can
just open up the
params_functions.asp page and manually configure it (it is thoroughly commented,
so it shouldn't be too hard to set the variables this way :).
I urge you to look through the source code (as it is pretty thoroughly commented), and I hope that you can find a use for this, or at the very least a use of some of the techniques described!