Create Dynamic HTML Forms with ADO, XML and XSL

In this article, we'll show you how to create a Web application with XML/XSL that will take data from any standard database and convert it to an HTML form and have that information feed back into the source table. For simplicity's sake, we'll use Access as the database so that you can easily download and test it out, but as the core will be ADO and XML, you'll be able to take this code and use it in your own applications without much alteration. Simply change the ADO ConnectionString in the example files to use an Oracle, SQL Server or another ADO-compliant database.

Because we're using XML and XSL, we'll be able to do everything with less code than we could using pure ASP with a lot of Response.Write commands. We'll also be able to make the HTML form data-driven. The database's table can, therefore, have as many fields as you wish, be in any type of database, and contain any type of data.

There are a few things you'll need before you can proceed with the techniques we'll be outlining, namely:

-IIS 4.0 or later
-Microsoft's XML Parser 3 on the client and server (which you can download for free at http://msdn.microsoft.com/xml/default.asp)

-IE 5.x or later on the client (version 5.5 is recommended)

By the time you've finished reading this article, you'll have learned all you need to begin using XML and XSL with ADO to create dynamic forms. In particular, you'll know how to convert data queried from a database into XML, as well as how to combine XML and XSL on the client and/or server in order to generate HTML. Finally, you'll learn how to create forms from data in a database using XML.

The code accompanying this article, summarized in Table A, is available in a Zip file on our ftp site. Simply unzip the contents into a folder on your IIS server and browse to the Default.asp page. It links to all the other pages. Each page is straightforward and can be easily copied and modified for your own use. Make sure the folder you place it into has read and write permissions so that the ASP pages can access the Microsoft Access database. To keep the samples simple, we've implemented very little error checking. In your production code you'll want to add a robust error handling system.

Table A: Files that comprise our sample application

Sample file............. Description
Default.asp............ Presents the index page of the sample application
AddCustomer.asp......... Adds the form data to the database
Database.mdb..........Represents the sample Access database
EnterData.asp ............Produces the XML from the database table structure
EnterData.xsl .............Transforms the XML data into an HTML form
General.css .........Provides a cascading stylesheet that maintains the look and feel
ViewData.asp ...........Produces the XML for viewing all the data in the database
ViewData.xsl ...........Transforms the XML from the database data into HTML

Getting started

Let's first look at the database structure of the data we wish to convert into an HTML form. For the sake of simplicity we'll use a standard table with four fields, as shown in Figure A. Of course, you can use as many fields as you wish. The table only requires a field called Key, and this field should be the index. The rest of the table is up to you. The beauty of this approach is that it will work with any table structure whatsoever. It's a solution for almost any simple form you might ever need to create.

Figure A: We're using a very simple table to create our HTML form.



As you can see in Figure A, this is a customer table holding contact information for various individuals. Our XML project will read in the data and generate a form to add people to this database based on the field definition data. We'll assume the fields are all text for now and that the first field, named Key, is the index key.

Step 1: Getting the data field definitions

Our first step is to retrieve the names of the fields from the database. Using ADO and a connection to the database, we retrieve a recordset, and then write out the definitions to the browser. Because we don't know the case of the field names, we'll force them all to uppercase. This is necessary because XML and XSL are both case-sensitive standards. The following section of the EnterData.asp page does the trick:

SAMPLE ONE: (below)

The output of the ASP page is pure XML. Before the XSL stylesheet transforms it into HTML, it looks as shown in Figure B.

Figure B: This is the raw XML before we transform it using XSL.



Notice how the XML tag contains the names of the fields in our database table. These are simple text fields. You could modify the source code to determine whether the fields should be Boolean radio buttons or dropdown boxes or some other data type. You'll have to edit the XML for that too, which is beyond the scope of this article.

To get the XML shown in Figure B to display as we wish, the following line must be added to EnterData.asp to point to our XSL stylesheet information:

SAMPLE TWO:(below)

When you don't specify the stylesheet, IE5 uses its default one. Viewing the XML representation in this default format is a good way to debug your XML before you send the data to the browser with a processing instruction to transform the XML into HTML with an XSL stylesheet because you can see the XML structure directly.

Step 2: Transforming XML into HTML

Once the EnterData.asp page fetches the data from the database and converts it to XML, the data is sent to an XSL stylesheet. Here, we convert the XML into an HTML form. This processing instruction, as shown in the previous paragraph, is a simple XML line.

The href specified is a relative URL to the XSL that's used to convert the XML. There's an important point to note here. The browser needs to be IE5.0 or higher with the XML version 3 parser installed. These are both free from Microsoft. If you're unsure about the browser your clients will be using, you can convert the XML into HTML server side by using the XML Document Object. We'll look at a simple example of that at the end of this article for reference. For now, let's assume you have IE5.x and the XML parser installed on client and server.Client-side processing

The XSL stylesheet is downloaded by the browser and is used to transform the XML. This is all done client-side. When you can be sure the client browser is up to the task, this is preferable, because you don't need to use the server's CPU to do XML transformations. Most desktop computers have several hundred megahertz sitting idle anyway.

The XSL stylesheet is quite simple. We won't pretend to give you a complete tutorial on XSL here, but for the most part the commands in this code example are sufficient to get you going. Let's walk through the basics behind it:



This code is the heart of the stylesheet. It loops through the data in the ROOT node of the XML document, which EnterData.asp sent to the browser. The XSL tag xsl:for-each acts just like a For Each loop in VBScript. The xsl:value-of tag extracts the value of the element in each iteration of the loop and displays it. The field names are used for the title of each of the form input rows and the input boxes of the form are given the names of the fields as well. As you can see, there's no limit to the size of the form this will create, and it's completely expandable and flexible. There's only one part of the code that might be a little strange to you if you're new to XSL. The tag in HTML doesn't ordinarily have a closing tag; however, in our code it does:



XSL is written in XML, which means the HTML within it must be well formed. All tags must be closed. The attribute XSL tag simply adds the attribute name to the input box and gives it a value.

After the XML has been transformed into HTML by the XSL stylesheet, it's displayed in the browser. This example produces a table with a simple form. We use a cascading stylesheet referenced in the XSL stylesheet to control the look and feel of the table, as shown in Figure C.

Figure C: Our HTML form is rendered through XSL.



Step 3: Adding the data to the database

The form created by the XML and XSL submits the data to the ASP page AddCustomer.asp. In this page the data is added to the same database that was used to create the form. This is the beauty of this entire system. Because the database table being submitted to is the same one from which the form was created, the code can be generic. You don't have to change the SQL at all in either page. Let's look at part of the AddCustomer.asp page to see how this is accomplished:

SAMPLE THREE: (below)

This code loops though the fields submitted by the form. For each field in the form, if the field isn't a SUBMIT button, the field's name and value is added to the SQL command to be processed. The SQL Insert command then looks like this when it's done:

SAMPLE FOUR: (below)

After the data is submitted to the database, the browser is simply redirected to the main page where the next item can be entered, or the data entered so far can be checked.

Viewing the results

We've included a simple page which displays the results of the form submissions, shown in Figure D. This is a nice example of how to use the default XML format that the recordset object saves and how to use this output in an XSL document. We use an XSL stylesheet to render the XML it produces into HTML client-side.

TO SEE COMPLETE ARTICLE: http://msdn.microsoft.com/library/default.asp?URL=/library/periodic/period01/asp0152.htm