Many situations require that a form be of the 'form/subform'
style where there is a main form with fields and a subform which the user can
use to enter multiple lines. A classic example is a company order form where the
company details are in the main form and any number of line items can be entered.
In order to achieve a Web-based 'form with
subform' we need to create a form that combines both a standard Web form and
a data grid to allow the user to create an order in an intuitive way. The task
for a programmer is to execute the appropriate form in a way that guarantees
data integrity but offers a high degree of usability for the user. The main
issue here is that when the user is entering the multiple lines we don't want
to save them beck to the 'Order Lines' table in the back end database.
There are two reasons for this. The first is that, at the time that the user
is entering the lines, we have not yet saved the main part of the order. Why
not, you might say. Well the answer is that, if we save the order before any
lines are entered and the user changes her mind and aborts by just going away
we end up with a non-confirmed order in our database with no order lines. My
preference here is to refrain from saving any part of the order until the customer
confirms the order by clicking a 'Save' button.
The second reason is closely related to the first reason and that, since we
have not saved the main part of the order we do not have an 'OrderID' to save
in the 'order lines' table as the related field.
To provide a foundation for such an operation I will describe a spare parts
ordering system developed in VB.Net. In this scenario, third party service personnel
can place an order for spare parts required to repair an item of equipment.
In this situation the order requires the company details of the ordering organisation
along with invoice and delivery addresses and a host of other information associated
with the order itself, In addition, one, or more, lines need to be attached
to the order describing the items to be on the order.
Download Source Code
You can download the source code for this article
The customer needs to be able to create new line items and fill them with the
necessary information. These line items will have a Part No, a description,
a quantity. The ideal way to execute this section of the order is the use of
a data grid.
So we end up with a single Web form with the main order details in the appropriate
fields complete with an embedded data grid for the order lines (See Figure 1).
Figure 1 The spare parts order form
One thing to notice about this form is that there are form fields both above
and below the data grid provided for the order lines. The reason for this is
that, when the user is working with the order lines grid a series of post backs
will occur and .Net will redraw the page each time. If the data grid is too
far down the page the form will be positioned at the top of the screen requiring
the user to scroll down to where she was working - this is highly irritating.
Placing the data grid as high up the form as possible causes the page position
to be the same after each post back (I'm sure there are ways to overcome this
but that is beyond the scope of this article)
The user can fill in the main form fields and add lines to the data grid. Each
time an order line is added and need to persist the changes to the data grid
in some way other than inserting them into the database until we are in a position
to save the order. We can then save the order, retrieve the ID created for it
and then save the lines with the ID linking field in place. As it turns out
it is surprisingly easy to do this.
In this scenario scalability is not an issue (an industrial
company order placement is not likely to be bombarded with thousands of simultaneous
transactions) so the easiest solution is to persist the order lines to the Session
object. This is done by creating a datatable with the appropriate columns and
saving it to the Session object. The 'makeTable()' code below shows how this
Code Listing A
A datatable object is instantiated, a column for each field in the order line
is added and the object is persisted to the session object. This Sub is called
during the page load process when the page is not being posted back.
Each time the parts order lines data grid is rebound (bindGrid()) the datatable
is retrieved from the Session space and is set as the data grid's datasource.
So the grid is always drawn with the latest state of the persisted datatable
Once we have such an object stored in the Session space we need to be able
to add rows to it, and to update it to keep it synchronised with the data grid
on the form. To do this we need a process to add a new row, a process to update
a row, a process to edit a row and one to persist the datatable back to the
Session space. The routines below show how this is done.
Code Listing B
The addRowToTable routine gets the datatable object from the Session space,
creates a datarow, populates each of the fields in the row with default data,
adds the row to the datatable and pushes it back to the Session space. Once
this is done it rebinds the datgrid to display the grid with its new row.
As is the normal situation with such a grid each line contains an 'Edit' hyperlink.
Clicking the 'Edit' link causes the grid to go into the 'Editing' mode as usual
and changes the links to 'Update' and 'Cancel'.
When the user clicks the 'Update' link the 'doUpdate(…..)' Sub (see below)
Code Listing C
This procedure retrieves the persisted datatable from the Session space (this
will be a mirror of the data in the data grid when it was last loaded) and updates
the appropriate data fields for the row pointed to by the Data gridCommandEventArgs
To keep everything synchronised the routine persists the new state of the datatable
to the Session object, sets the EditItemIndex to -1 to get the grid out of editing
mode and rebinds the grid to display the latest state of the data.
If the user clicks the 'Cancel' link the doCancel(…….) Sub shown
below is called.
Sub doCancel(ByVal source As Object, ByVal e As Data gridCommandEventArgs)
Data grid1.EditItemIndex = -1
You will notice that this is the standard 'Cancel' process applicable to this
situation in any data grid. We don't need to do any more because canceling the
edit process causes the data grid to return to the same state as the datatable
currently persisted to the Session space. Thus everything returns to the synchronised
Saving the Order
All that remains is to save the order and its lines when the user clicks 'Save'.
The issue here is that we need to save the new customer and retrieve the new
customer ID so the ID can be saved with the order to associate this order with
this customer. Then, once we have saved the main order we need the ID of that
order to link each of the order lines to it.
There are a number of ways to do this. If you are using SQL Server you can
use an autonumber field for the IDs and retrieve the @@IDENTITY value from the
stored procedure as a return parameter. If you are using MSAccess, as I was
here, the simplest way is to set the Order ID field to a Long (not AutoNumber),
retrieve the last ID from the table using a query based on a MAX clause and
add one to it prior to saving the new order with this new ID.
The Access query will look like that below…
SELECT Max(tblCustomers.CustomerID) AS MaxOfCustomerID
SELECT Max(tblPartsOrder.PartOrderID) AS MaxOfPartOrderID
Of course this does mean that you will need to place a 'Seed' row in the database
tables with an ID of 0 to prevent an error when the first order is entered.
Just make sure that any query you use against the order table excludes the row
with the ID = 0.
So the process is to retrieve the last ID, add one to it and use this value
to insert the new order into the database. Once this is done you can retrieve
the datatable from the Session object and walk through it inserting each row
into the parts order lines table using the retrieved ID in the linking field
(See the saveOrder() routine below.
Code Listing D
Notice that, when you walk through the datatable saving the rows, the Command
object is simply cleared of its parameters each time and reused in each loop
of the 'For'.
There is a minor irritation with this technique and it revolves around the
issue of validation of required fields in the main form. When the user clicks
the 'Add New Row" button all 'Required Field' validators will be activated.
If you place any required fields below the order lines data grid it is not intuitive
to the user that they have to complete these fields prior to adding rows to
the grid section. It is intuitive to the user that she has to complete any fields
above the grid before adding the line items and this is seen as quite a natural
progression down the page. Given that the data grid needs to be positioned as
high up the form as possible to avoid the form jumping around while the user
is interacting with the data grid, you still need to place all required fields
above the grid so the user fills them in prior to adding the line items.
The last things I have included here is the sending of an email to the to the
company owning the site to alert them to the fact that an order has been placed
and redirecting the user to a message page that indicates to her that the order
was correctly saved.
This article has shown you one technique that can be used to implement a 'form/subform'
type form in a Web environment. This form allows a user to complete an order
consisting of a set of fields on the main form and ad as many line items to
the order as required. It shows how the 'Session object' can be used to persist
the line items until you are ready to save the contents of the form and the
subform as a complete entity.
Ray Gudgeon has been a professional developer for eight years doing integrated
database and commerce driven Web applications in ASP. Over the last two years
he has migrated to VB.net and C#.Net backed by SQL Server 2000 and MSAccess. Ray
is the principal of his own consultancy – Ray Gudgeon and Associates.