Coding a 'One-to-Many' Form with ASP.NET

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 from here.

---

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 is done.

Code Listing A
Sub maketable() ' create a data table to hold the data grid order lines Dim objDt As New DataTable() objDt = New DataTable("PartsLines") With objDt .Columns.Add("StockCode", GetType(String)) .Columns.Add("Description", GetType(String)) .Columns.Add("qty", GetType(Long)) End With Session("tbl") = objDt End Sub

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 object.

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
Sub addRowToTable(ByVal source As Object, ByVal e As EventArgs) ' get the table from the session object, add a row to it and put if back Dim objDt As New DataTable() objDt = Session("tbl") Dim objDataRow As DataRow ' add a row to it objDataRow = objDt.NewRow objDataRow("StockCode") = ""; objDataRow("description") = ""; objDataRow("qty") = 1<br> objDt.Rows.Add(objDataRow) Session("tbl") = objDt bindGrid() End Sub

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) is called.

Code Listing C
Sub doUpdate(ByVal source As Object, ByVal e As Data gridCommandEventArgs) ' put the new data back in the datatable Dim objDt As New DataTable() ' fill the table from the session object objDt = Session("tbl") ' update the values in the appropriate row objDt.Rows(e.Item.ItemIndex).Item("StockCode") = CType(e.Item.FindControl("txtStockCode"), TextBox).Text objDt.Rows(e.Item.ItemIndex).Item("Description") = CType(e.Item.FindControl("txtDescription"), TextBox) objDt.Rows(e.Item.ItemIndex).Item("qty") = CType(e.Item.FindControl("txtqty"), TextBox) ' save the table back to the session Session("tbl") = objDt ' set the index and rebind Data grid1.EditItemIndex = -1 bindGrid() End Sub

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 parameter.

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
bindGrid()
End Sub

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 state.

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
FROM tblCustomers;

And…

SELECT Max(tblPartsOrder.PartOrderID) AS MaxOfPartOrderID
FROM tblPartsOrder;

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
Sub saveOrder(ByVal source As Object, ByVal e As EventArgs) Dim objConn As New OleDbConnection() Dim objCmd As New OleDbCommand() Dim objCmd1 As New OleDbCommand() Dim objReader As OleDbDataReader Dim intID As Integer Dim objDt As New DataTable() Dim intI As Int16 Dim intCustomerID As Int16 objConn.ConnectionString = Application("gblstrConn") objConn.Open() ' we need the next Parts order number objCmd.CommandText = "spGetLastPartsOrderNo" objCmd.CommandType = CommandType.StoredProcedure objCmd.Connection = objConn objReader = objCmd.ExecuteReader objReader.Read() intID = CInt(objReader(0)) + 1 objReader.Close() ' get the next customer number objCmd.CommandText = "spGetLastCustomerID" objCmd.CommandType = CommandType.StoredProcedure objReader = objCmd.ExecuteReader objReader.Read() intCustomerID = CInt(objReader(0)) + 1 objReader.Close() ' save the order objCmd.CommandText = "spinsPartsOrder" objCmd.CommandType = CommandType.StoredProcedure objCmd.Parameters.Add("PID", intID) objCmd.Parameters.Add("CID", intCustomerID) objCmd.Parameters.Add("ONo", Me.txtOrderNo.Text) objCmd.Parameters.Add("SO", Me.txtServiceJobNo.Text) objCmd.Parameters.Add("DAS", Me.txtDelStreet.Text) objCmd.Parameters.Add("DSb", Me.txtDelSuburb.Text) objCmd.Parameters.Add("DStat", CLng(Me.cboDelStates.SelectedItem.Value)) objCmd.Parameters.Add("DPost", Me.txtDelPostcode.Text) objCmd.Parameters.Add("DCount", CLng(Me.cboDelCountries.SelectedItem.Value)) objCmd.Parameters.Add("Phone", Me.txtPhone.Text) objCmd.Parameters.Add("Email", Me.txtEmail.Text) objCmd.Parameters.Add("FirstName", Me.txtFirstName.Text) objCmd.Parameters.Add("LastName", Me.txtLastName.Text) objCmd.Parameters.Add("SN", Me.txtSerialNo.Text) objCmd.Parameters.Add("Not", Me.txtNotes.Text) If Me.chkUrgent.Checked Then objCmd.Parameters.Add("Urg", True) Else objCmd.Parameters.Add("Urg", False) End If objCmd.Parameters.Add("CName", Me.txtCompany.Text) objCmd.Parameters.Add("AccNo", Me.txtAccountNo.Text) objCmd.Parameters.Add("Desc", Me.txtDescription.Text) objCmd.ExecuteNonQuery() objCmd.Parameters.Clear() ' get the order lines objDt = Session("tbl") ' walk through and save each line objCmd1.CommandText = "spinsPartsOrderLine" objCmd1.CommandType = CommandType.StoredProcedure objCmd1.Connection = objConn For intI = 0 To objDt.Rows.Count - 1 objCmd1.Parameters.Add("STK", objDt.Rows(intI).Item("StockCode")) objCmd1.Parameters.Add("DES", objDt.Rows(intI).Item("Description")) objCmd1.Parameters.Add("Q", objDt.Rows(intI).Item("qty")) objCmd1.Parameters.Add("PID", intID) objCmd1.ExecuteNonQuery() objCmd1.Parameters.Clear() Next objConn.Close() ' send an email to company Dim msg As New MailMessage msg.From = "Web Site" msg.Subject = "Parts order posted to www.company.com.au" msg.To = "service@company.com.au" msg.Body = "A spare parts order has been placed on the Web site by " & Me.txtFirstName.Text & " " & Me.txtLastName.Text & " - " & Me.txtEmail.Text & " from " & Me.txtCompany.Text & vbCrLf & vbCrLf SmtpMail.Send(msg) Response.Redirect("usermessage.aspx?message=Your order has been saved successfully&redirect=partsorder.aspx") End Sub

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.