Using System Objects in MS SQL-Server to Create a Web-Based Database Admin Interface, Part 2

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 (from 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!)

<% strSQL = "SELECT syscolumns.name, syscolumns.type, syscolumns.length, " & _ "syscolumns.isnullable FROM sysobjects " & _ "INNER JOIN syscolumns ON sysobjects.id = syscolumns.id " & _ "where sysobjects.name = '" & strTableName & _ "' ORDER BY syscolumns.colid" Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open strSQL, objConn '... Much code removed for brevity! ... %> <FORM ACTION="AddData.asp" METHOD="post" onSubmit="return frmValidate(this);"> <TABLE BORDER="0" CELLSPACING="1" CELLPADDING="1" ALIGN="center"> <% 'First you want to dynamically generate a form from the database fields Do Until objRS.EOF If objRS("length") > 20 Then strLength = 20 Else strLength = objRS("length") End If Response.Write vbTab & "<TR>" & vbNewLine & vbTab & vbTab & _ "<TD><STRONG>" & Replace(objRS("name"), "_", " ") & _ ": </STRONG></TD><TD>" Select Case objRS("type") ' Check for text area (blob field) Case 35 Response.Write "<TEXTAREA COLS=""17"" ROWS=""5"" NAME=""" & _ objRS("name") & """></TEXTAREA>" ' Image field chokes Case 34 Response.Write "Please speak to database " & _ "administrator to edit this field." ' See if it is a small number Case Else Response.Write "<INPUT TYPE=""text"" NAME=""" & _ objRS("name") & """ SIZE=""" & strLength & _ """ MAXLENGTH=""" & objRS("length") & """>" End Select Response.Write "</TD>" & vbNewLine & vbTab & "</TR>" & vbNewLine objRS.MoveNext Loop %> <TD COLSPAN="2" ALIGN="center"> <INPUT TYPE="submit" NAME="Submit"VALUE="Add New Data!"> </TD> </TR> </TABLE> </FORM>

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 (type), the 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 (strTableName, which 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:

name	type	length	isnullable
CustomerID	47	10	0
CompanyName	39	80	0
ContactName	39	60	1

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 input type="text" field). 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 sa 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!

Happy Programming!