Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

Using System Objects in MS SQL-Server to Create a Web-Based Database Admin Interface
By Joseph Anderson

---

Have you ever wanted to set up a web based tool for you or perhaps users in your organization to be able to add, manipulate or delete records in a specific table in your database? This could be used for one of many different tasks such as adding/editing user accounts or even just allowing your boss to be able to search for specific information in a database, and then make changes to a specific user/vendor/employee, etc. without needing direct access to the database (or your time and effort to teach him/her the SQL commands to make the edits).

I have been put in charge of building several different administration tools for use internally within the company. A few months ago I was challenged with the task of creating a tool that would allow website config personal to add and edit members of that site (all information being kept in a Members table in the database). Not only did they want this tool built for one specific client, but they also wanted it built so that the code could be moved to other web sites and used as an admin page with a minimum of code changes. To keep this simple, I have assumed that all User/Member info is kept in a single table. The tool I have put together is pretty generic. It has three tool files, a single file that holds the parameters, and an index page. It is built so that all you need to do to get started using it is to set a few variables on the parameters page and bam! you have a functional (although generic) interface to any table in your database!

Most of the programming techniques used in this tool are explained in various articles here at the 4Guys web site. When building the administration tool I made a conscious effort to have the ASP pages make references to pre-set, configurable parameters instead of explicit references to items hardcoded within the code.

For example, say that you wanted to output the column names as headers in an HTML table... you may consider doing the following:

<% 'RS = open Recordset object Response.Write "<TABLE><TR>" Response.Write "<TH>" & RS("column1").Name & "<TH>" Response.Write "<TH>" & RS("column2").Name & "<TH>" Response.Write "<TH>" & RS("column3").Name & "<TH>" Response.Write "</TR></TABLE>" %>

Although this would work just fine, but every time you wanted to access a different table in your database, you would have to go back into this code and explicitly rename each of the columns! To make it a little more generic and not dependent on specific column names you could go with a more flexible solution:

<% 'RS = open Recordset object Response.Write "<TABLE><TR>" For i = 0 To RS.Fields.Count - 1 Response.Write "<TH>" & objRS.Fields(i).Name & "</TH>" Next Response.Write "</TR></TABLE>" %>

This way, not only does it not matter what the column names are, but it also doesn't matter how many there are! Okay, so that was pretty basic. I just wanted to throw that out there to give you an idea of how this tool as a whole needed to be created.

This next part deals with the system tables in SQL Server 7.0 (the database this tool was built around). (If you are unfamiliar with SQL Server's system tables, be sure to check out: Using the sysobjects Table and Programmatically Determining if a Table Exists!) Let's say you want a page that will allow authorized users to add new data to your table. You may say, "Well, no problem. Just build a form like the one above, and when the user hits submit have it enter all the form information into the table." If the table was always going to be the same, that would be fine.... but consider this; what if the user enters invalid data? What if it is the wrong data type, or what if it is too long to put into the field? Oh man, I suppose I could just write a bunch of JavaScript error checking every time I change this tool to use a different table.....but that kind of defeats the point, doesn't it?

In Part 2 we'll look at how to make the administration page very generic and reusable through SQL Server's system-level tables.

Read Part 2!

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article