Programmer to ProgrammerTM  
Wrox Press Ltd  
   
  Search ASPToday Living Book ASPToday Living Book
Index Full Text
 
ASPToday Home
 
 
Home HOME
Site Map SITE MAP
Index INDEX
Full-text search SEARCH
Forum FORUM
Feedback FEEDBACK
Advertise with us ADVERTISE
Subscribe SUBSCRIBE
Bullet LOG OFF
                         
      The ASPToday Article
January 22, 2002
      Previous article -
January 21, 2002
   
 
   
   
   
Form and Action design and implementation via SQL   Miljan Mitrovic  
by Miljan Mitrovic
 
CATEGORIES:  Data Access, XML/Data Transfer  
ARTICLE TYPE: Tutorial Reader Comments
   
    ABSTRACT  
 
Article Rating
 
   Useful
  
   Innovative
  
   Informative
  
 7 responses

While creating an intranet solution for a daughter firm in his holding corporation, Miljan Mitrovic encountered a problem. He was requested to allow field operatives and other personnel to input large quantities of information using over 100 different forms. That, in itself, was not such a problem, however the same mechanism was to be used in all of the firms branch offices; each of which had their own local intranets and local SQL servers. The only link between them being a daily data replication through a dial–on–demand line.



In this article Miljan Mitrovic presents a viable solution to this problem using SQL server to assist in each and every step of the way. Thus, when a new form is requested, only its definition needs to be created, and a simple data replication would propagate new forms to all branch offices. This would reduce the need for coding hours, remove the threat of an unreliable link and simplify project maintenance.

   
                   
    Article Discussion   Rate this article   Related Links   Index Entries  
   
 
    ARTICLE

While creating an intranet solution for a daughter firm in our holding corporation, we came across a very serious problem. We were requested to allow field operatives and other personnel to input large quantities of information using over 100 different forms. That, in itself, is not such a problem, however the same mechanism was to be used in all of the firms branch offices; each of which had their own local intranets and local SQL servers. The only link between them being a daily data replication through a dial-on-demand line.

While evaluating the basic solution, to manually create over 200 pages and make sure they are properly deployed to all branch offices, we became understandably unhappy. It would require a lot of man-hours just to complete the pages, and then, on top of everything physically deploy them to all servers. Needless to say, this was way too prone to errors. Since the dial-on-demand line was very unstable, we could not rely on this channel for page replication, as we did not have extra staff we could use to monitor this process and test results at remote locations. Even if all these problems were solved, we were still facing the good chance that tomorrow a new form would be requested, which would start this cycle over again.

We had these resources/obstacles at hand:

The only viable solution we had was to somehow use SQL server to help us in each and every step of the way. Thus, when a new form is requested, we would only have to create its definition, and a simple data replication would propagate new forms to all branch offices. This would reduce the need for coding hours, remove the threat of an unreliable link and simplify project maintenance. For this, we created only two ASP pages, one that would create the form presented to user, the other receiving data from POST package and providing necessary updates. To simplify form definition and create basic rules for displaying form elements, we set a rule that all visual elements should be mapped to an x, y coordinate system. With these simple rules, everything was solved in a very small time margin.

Preparations

This solution leans on familiar techniques, since it uses common ASP/ADODB combination based on "OLEDB provider for MSSQL". The project was developed on Win2000 platform using IIS5 for web and MSSQL7 for database, however pages and scripts used here should work with older versions, as well as with SQL Server 2K.

Through the making of this solution we focused on Internet Explorer as the client and used event handler mapping provided by IE to avoid cross browser problems (more on this issue will be said in the HTML section of this article)

Aside from this, no further setting is needed, only keep in mind that all tables and stored procedures used here were placed in the same database as the data collected (for simplicity). If separate databases were required for security or some other reason, extra changes would have to be made (separate connections are being opened).

Basic philosophy

The idea is rather simple and based on common practice implemented in most of the forms that can be found on the Web:

So in a nutshell, we start from a basic visual form of HTML table, each table having rows and columns (easily mapped to [row, column] coordinates) and having its maximum extent. If we were to have a list of elements (each with its coordinate defined in advance) we could easily place them on the screen. We only need to extend this model with a master list, which would (using non-identifying 1-to-many relationship) group items into separate forms.

As far as handlers are concerned, they can be hooked both on form and on its elements, so a third SQL table is needed to store all required handlers. In order for them to be available to form and elements alike, it would have to be a non-identifying (zero or 1-to-zero, one or many) child of both SQL tables mentioned above. Finally we came to a model (ERD) that looks something like this:

Image 1

You can find the forward engineer script (a sequence of SQL statements, whose sole purpose is to generate tables and relations according to a predefined model) in the support material file /sql/foreng.sql.

To further explain this model, in form_list we will keep a description of actual forms, the HTML page title and table caption that will appear above input elements, and maximum extent of the HTML table that will contain input elements. Table form_elem contains all the INPUT elements that would appear on the form page. I tried to use rather self-explanatory field names, so here are just a few quick pointers:

Element types

As you can see, it is a rather simple model, but sufficient nonetheless. For this demonstration, we will use only drop down elements ( SELECT), basic input box ( INPUT type=text), submit and reset buttons. You can always extend this model to allow different kinds of elements, or even subdivide existing ones (for example, use an input box for both text and numeric input, and provide centralized data validation for numeric fields). For our convenience we will map these types numerically from 1 to 4.

Generating form page

Before we can initiate form generation we need to collect all relevant data for the requested form. Since we reference different forms, using the form_list_id field, we will use the following four SQL statements. You can convert them into stored procedures if you wish (@form_list_id represents input parameter) and gain some performance benefits.

-- to collect form info
select * from form_list where form_list_id = @form_list_id;

-- to collect form elements
select * from form_element where form_list_id = @form_list_id;

-- to collect form's handlers
select * from form_handler where form_list_id = @form_list_id;

-- to collect form's handlers
select form_handler.*, form_element.s30_elem_html_id from form_handler 
inner join form_element on form_handler.form_element_id = form_element.form_element_id
where form_element.form_list_id = @form_list_id;

When all required record sets are ready, we can start building our form page. For the purpose of following this article, let us assume record sets were named rstForm , rstElem , rstFormHandlers , rstElemHandlers (respectively according to the order of scripts).

HEAD section

To avoid bloating up this article, we'll skip the source for creating actual record sets from previously mentioned statements (you can still find it in the support material), and move to the actual HTML part. The HEAD section should contain the page title (described in form_list) and all event handlers returned for both form and its elements. Before handler bodies are inserted into <script> segments we need to standardize on full handler names. This has to be done primarily because of IE's mechanism to link events named <element id>_<event name> without an explicit request. However we cannot safely use this technique if Netscape Navigator is to be used. So, effectively, we will prefix each form's handler name with ' Fform_ ' and all other handlers with ' A<element name>_ '. Then we can use regular calling technique (explicitly placing event calls inside element) completely avoiding cross browser issues, and avoiding situations with overlapping handler names (that could trigger events to fire twice in IE). Naturally, browser issues are not completely resolved by this solution, you still have to be vigilant with what you place in actual events.

As an example, the form's onSubmit event will have the name ' Fform_onSubmit ' while onChange handler for the s_desc element bears the name ' As_desc_onChange '.

According to the previous description, the actual code that generates the HEAD section looks like this:

<HEAD>
<TITLE><%=rstForm.Fields("s100_page_caption").Value%></TITLE>
<%
Dim hand_name, language, elem_name
rstFormHandlers.MoveFirst 
Do while not rstFormHandlers.EOF
       hand_name = rstFormHandlers.Fields("s30_handler_name").Value
       language = rstFormHandlers.Fields("s15_language").Value
       Response.Write "<SCRIPT language=""" & language & """>" & vbCrLf
       Response.Write "<!--" & vbCrLf
       Response.Write "function Fform_" & hand_name & "() " & vbCrLf
       If UCase(Left(language, 1)) = "J" Then
              Response.Write "{" & vbCrLf
       End If
       Response.Write rstFormHandlers.Fields("s4000_handler_code").Value
       Response.Write vbCrLf
       If UCase(Left(language, 1)) = "J" Then
              Response.Write "}" & vbCrLf
       Else 
              Response.Write "end function" & vbCrLf
       End If
       Response.Write "-->" & vbCrLf & "</SCRIPT>" & vbCrLf
       rstFormHandlers.MoveNext
Loop

rstElemHandlers.MoveFirst 
Do while not rstElemHandlers.EOF
       hand_name = rstElemHandlers.Fields("s30_handler_name").Value
       language = rstElemHandlers.Fields("s15_language").Value
       elem_name = rstElemHandlers.Fields("s30_elem_html_id").Value
       Response.Write "<SCRIPT language=""" & language & """>" & vbCrLf
       Response.Write "<!--" & vbCrLf
       Response.Write "function A" & elem_name & "_" & hand_name & "() " & vbCrLf
       If UCase(Left(language, 1)) = "J" Then
              Response.Write "{" & vbCrLf
       End If
       Response.Write rstElemHandlers.Fields("s4000_handler_code").Value
       Response.Write vbCrLf
       If UCase(Left(language, 1)) = "J" Then
              Response.Write "}" & vbCrLf
       Else 
              Response.Write "end function" & vbCrLf
       End If
       Response.Write "-->" & vbCrLf & "</SCRIPT>" & vbCrLf
       rstElemHandlers.MoveNext
Loop

%>
</HEAD>

BODY section

With the HEAD section done and ready, we can move to visual definitions. The first thing we need to do in the <BODY> section is to establish a FORM context. Since we always use the same action page, this will be hard coded. The same with the form name, it also bears the same name on every call. So we come to the obvious drawback of this solution, form data processing. The purpose of this solution was to allow personnel to insert bulk data into the database, so this was okay in our case. However, if special processing has to be done before updating to a database, it will have to be simulated through client side scripts and hidden fields (in the form's onSubmit handler for instance). Having many action pages (although feasible) was not acceptable to us, as it would force us to deploy all those action pages as well. If this is not a problem for you, you can always extend the model and dynamically assign the action page location.

Back to the project at hand, we said the action page and the form id are fixed. What's different every time is a list of client side handlers associated on form level, so this section of the code will be used to create needed calls:

<FORM action="DAction.asp" method="post" id="Form1" name="Form1"
<%

rstFormHandlers.MoveFirst 
Do while not rstFormHandlers.EOF
       hand_name = rstFormHandlers.Fields("s30_handler_name").Value
       language = rstFormHandlers.Fields("s15_language").Value
       If UCase(Left(language, 1)) = "J" Then
              Response.Write hand_name & "=""" & language & ": return Fform_" & hand_name & "();"" "
       Else 
              Response.Write hand_name & "=""" & language & ": Fform_" & hand_name & """ "
       End If

       rstFormHandlers.MoveNext 
Loop

%>
>
<INPUT type=hidden name="__comm" id="__comm" value="<%=form_id%>">

We must also provide a hidden field and set its value to form_id. This way the action page will know to which form the data belongs.

Now we need to create a carrier table and fill it with elements. In order to do this, we will scan all designated cells, locate labels and elements, and create them according to their type. Each type has its own special formatting so the task of creating HTML code for elements was displaced from inline code to the CreateElement sub:

Sub CreateElement
Dim typ
       typ = rstElem.Fields("int_elem_type").Value
       If typ=1 Then Call CreateSelect
       If typ=2 Then Call CreateInput
       If typ=3 Then Call CreateSubmit
       If typ=4 Then Call CreateReset
End Sub

As you can see, this "Sub" differentiates between types of elements and assigns actual creation to specialized functions. We will display CreateSelect here (as the most demanding one, you can find other "Subs" in the support material download):

Sub CreateSelect
Dim value, style, id, cmd2, rst2, sp_value, sp_text, s_val, s_text
       Set cmd2 = Server.CreateObject("ADODB.Command")
       Set cmd2.ActiveConnection = conn
       cmd2.CommandType = adCmdStoredProc
       cmd2.CommandText = rstElem.Fields("s100_select_sp").Value
       Set rst2 = Server.CreateObject("ADODB.Recordset")
       rst2.CursorLocation = adUseClient
       rst2.CursorType = adOpenStatic
       rst2.Open cmd2
       
       value = rstElem.Fields("s100_default_value").Value
       style = rstElem.Fields("s100_style").Value
       id = rstElem.Fields("s30_elem_html_id").Value
       
       Response.Write "<SELECT "
       Response.Write "id=""" & id & """ name=""" & id & """ style=""" & style & """ "
       Call HandlerCalls
       Response.Write ">" & vbCrLf

       Do while not rst2.EOF
              s_val = rst2.Fields(rstElem.Fields("s30_select_value").Value).Value
              s_text = rst2.Fields(rstElem.Fields("s30_select_text").Value).Value
              Response.Write "<OPTION value=""" & s_val & """>" & s_text & "</option>" & vbCrLf
              rst2.MoveNext
       Loop
       
       Response.Write "</SELECT>" & vbCrLf
       Set rst2 = Nothing
       Set cmd2 = Nothing
End Sub

As you can see, SELECT is a special HTML input element since it consists of multiple HTML tags, so we first create the main SELECT tag, assign name, id and style. Before we close the main tag we must generate all handler calls this element has, so this task (which is common to all elements) was placed in HandlerCalls sub. This sub's code is actually similar to the one used for creating handler calls assigned to form, only different naming is used, so I will not repeat it.

If we further examine CreateSelect we will see another access to a database. This time, we will call a stored procedure and later reference its resulting record set fields in a special way.

Form_element table has three special (NULLable) fields: s100_select_sp , s30_select_value and s30_select_text. We use these fields only for the SELECT element. The mechanism is simple; we take the stored procedure name, execute it and retrieve all the elements we need to create <OPTION> tags. For that we only need three parameters (stored procedure name, value and text to be displayed). The only consideration is that, in this sample, we are limited to using only two fields from the resulting recordset, one to be assigned as an option value, the second to be used for display on screen. However, you are free to extend this model and provide extra information (custom OPTION attributes, for example)

Other elements are far simpler, so I will only list the source for the submit button, the rest of the element types differ only in the INPUT=xxxx section.

Sub CreateSubmit
Dim value, style, id
       value = rstElem.Fields("s100_default_value").Value
       style = rstElem.Fields("s100_style").Value
       id = rstElem.Fields("s30_elem_html_id").Value
       Response.Write "<INPUT type=""submit"" value=""" & value & """ "
       Response.Write "id=""" & id & """ name=""" & id & """ style=""" & style & """"
       Call HandlerCalls
       Response.Write ">"
End Sub

Finally, as all elements are mapped to their coordinates, we get a functional form display, with all required elements, and with all of them being nicely tied to their handlers:

Image 2

Data mapping

With the form page safely behind us, it's time to consider what to do with the POST package coming from it. To make things simple, we will not create any visual representation in this page; it will consist of ASP code only and will end with a redirection to previous page. You are by no means limited to this outcome; you can say provide some visual representation of POST ed data before doing the actual update.

Before any mapping can be done, recordsets must be opened returning form elements as in the form page. Only this time we have no need for client-side handlers, so we will skip them and use only rstForm and rstElem (described earlier)

In the same way as we used special stored procedures to fill the SELECT tag, we will use one stored procedure to do the actual insert/update when we POST the form. We will toy a bit, for the purpose of this article, using the form you see in the diagram. It displays drop downs with all the elements designated to form1 , and an input box that can be used to specify a new label for the selected element.

Note: To use the above form, deploy the entire database ( /sql/fullDB.sql) and import the three text files ( /sql/form_list.txt , /sql/form_element.txt , /sql/form_handler.txt) located in the support material. Import the data in the sequence listed here to avoid FK violation.

We will now build the stored procedure that will receive POST ed data and update the form_element table:

Create Procedure t1_update_sp
(       @param1 int,
        @param2 varchar(100)
)
As
begin
       update form_element
              set s100_label = @param2
       where form_element_id = @param1;
       return 
end

@param1 and @param2 , used in this stored procedure are assigned to drop down and input element, so we must now map POST ed values with matching parameters in the stored procedure:

Set cmd2 = Server.CreateObject("ADODB.Command")
Set cmd2.ActiveConnection = conn
cmd2.CommandType = adCmdStoredProc
cmd2.CommandText = CStr(rstForm.Fields("s100_insert_sp").Value)
cmd2.Parameters.Refresh 

For each param in cmd2.Parameters
       param_name = param.Name 
       rstElem.MoveFirst
       Do while not rstElem.EOF
              if (param_name = rstElem.Fields("s100_param_name_sp").Value) Then
                     param.Value = Trim(Request.Form(rstElem.Fields("s30_elem_html_id").value))
              End If 
              rstElem.MoveNext 
       Loop
Next 
Call cmd2.Execute

As you can see I am using a simple trick provided by "OLE DB Provider for SQL". It can retrieve all parameters assigned to stored procedures with a single call to Parameters.Refresh. All we have to do now is to browse through parameters and map them. Although "stealing" parameter information bears its price (it can be resource intensive and slow), the benefits reaped from this mechanism far outweigh the costs. As you will see later, there are other methods to achieve this result, but we focused on this one as it made our ASP code simpler and less prone to errors.

For each parameter a matching element is found (using the s100_param_name_sp field from the form definition); then having its HTML id (placed in s30_elem_html_id field, which we previously used to create INPUT and SELECT elements) we retrieve the value from the POST package and assign it. As for data conversion, we can rely on OLE DB or we can implement validation using event handlers (consideration for extensions).

Once all the parameters are matched, we execute the procedure and return to the form. In our sample case, the label of the chosen element has changed:

Image 3

Image 4

Data mapping variations

You are by no means limited to this form of mapping; you can even implement this methodology on different database platforms, as far as on ones without procedure support. You can, for example, use a tagged script (or parameterized query) instead of stored procedure name (in form_list table) and do a custom mapping based on specified tags. However, do bear in mind that this can present a security risk, since a malicious user can break a script with a semicolon and insert damaging SQL commands (for example, specifying '; delete from form_handler;' in the input element), so extra vigilance is needed here. I deliberately kept this sample (and the mechanism itself) simple, so it can be more easily upgraded or ported to different DB platform.

Other expansion possibilities

Just as mapping can be expanded, so can other segments of this solution. You can, for example, focus on one client platform and use its special features. For instance, we have focused on IE5, which allowed us to use VBScript, which in turn allowed us to used setlocale 2074 , a special statement instructing VBS to use international locale ( 2074 representing LCID used in Yugoslavia) for data conversion from string to binary format and vice versa, making VBS accept dd.mm.yyyy as string representation of date, and so on. Effectively, that made our application locale aware in one single change. You could also create new types of input elements; provide dynamic links out of action page; even provide intrinsic data validation, both on server and client side. Even on the server-side, you could expand the model used; maybe add a fourth table describing default visual appearance for specific types of elements. Maybe incorporate XML? Security; limit users to specific forms using many-to-many relation between form_list and a table containing users.

The first thing, though, is to establish a safe way to fill these three tables with form definitions. We are using a tool developed in a RAD tool, but you are free to create your own; this model is simple enough to allow any form of inputting (providing that tool supports relations), so even a set of ASP pages can be used to review and create form definitions.

Conclusion

In situations that require a lot of forms being generated in a very narrow margin of time, this solution can save you both money and time, as it did for us. Initially this solution would take longer to complete and test, but after that it would save time for subsequent projects, since we reduced creating new forms to inserting data into a database, and can be easily distributed to as many locations as needed.

Article Information
Author Miljan Mitrovic
Technical Editor John R. Chapman
Author Agent Charlotte Smith
Project Manager Helen Cuthill
Reviewers Sean M. Schade, Phillip J. Sidari

If you have any questions or comments about this article, please contact the technical editor.

 
 
   
  RATE THIS ARTICLE
  Please rate this article (1-5). Was this article...
 
 
Useful? No Yes, Very
 
Innovative? No Yes, Very
 
Informative? No Yes, Very
 
Brief Reader Comments?
Your Name:
(Optional)
 
  USEFUL LINKS
  Related Tasks:
 
 
   
  Related ASPToday Articles
   
  • Applying Classes to Everyday Forms (January 4, 2001)
  • Enhance Input Forms Using XML (August 3, 2000)
  • Advanced Form Validation (July 8, 1999)
  •  
           
     
     
      Related Sources
     
  • VB Script Engine: http://msdn.microsoft.com/scripting
  • ADO: http://www.microsoft.com/data
  •  
     
           
      Search the ASPToday Living Book   ASPToday Living Book
     
      Index Full Text Advanced 
     
     
           
      Index Entries in this Article
     
  • Body element
  •  
  • Button controls
  •  
  • data entry form
  •  
  • data mapping
  •  
  • forms
  •  
  • forms, building
  •  
  • Header element
  •  
  • hidden form fields
  •  
  • HTML table
  •  
  • input element
  •  
  • input form, building
  •  
  • input forms, building
  •  
  • Internet Explorer
  •  
  • intranet
  •  
  • Netscape Navigator
  •  
  • OLE DB provider
  •  
  • Parameters collection
  •  
  • POST method
  •  
  • problems with
  •  
  • recordsets
  •  
  • Refresh method
  •  
  • Select lists
  •  
  • SQL Server
  •  
  • SQL statements
  •  
  • tables
  •  
  • web forms
  •  
     
     
    HOME | SITE MAP | INDEX | SEARCH | REFERENCE | FEEDBACK | ADVERTISE | SUBSCRIBE
    .NET Framework Components Data Access DNA 2000 E-commerce Performance
    Security Admin Site Design Scripting XML/Data Transfer Other Technologies

     
    ASPToday is brought to you by Wrox Press (http://www.asptoday.com/OffSiteRedirect.asp?Advertiser=www.wrox.com/&WROXEMPTOKEN=180659ZhEaANU51sACzT6SGvlO). Please see our terms and conditions and privacy policy.
    ASPToday is optimised for Microsoft Internet Explorer 5 browsers.
    Please report any website problems to webmaster@asptoday.com. Copyright © 2002 Wrox Press. All Rights Reserved.