Programmer to ProgrammerTM | |||||
|
|
|
|
|
|
|
|
|
|
|
| |||||||||||||||||||
The ASPToday
Article January 22, 2002 |
Previous
article - January 21, 2002 |
||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
ABSTRACT |
| ||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
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.
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).
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:
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:
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.
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).
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>
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:
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:
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.
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.
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.
|
| |||||||
|
| |||||||||||||||
|
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. |