How Do You Emulate a Continous subform for Data Entry

ASP Free Forums Sponsor:

May 15th, 2000, 07:10 AM

Steve Schofield

Contributing User

ASP Free God 20th Plane (14500 - 14999 posts)

Join Date: Dec 2002

Posts: 14,575

Time spent in forums: < 1 sec
Reputation Power: 22

How Do You Emulate a Continous subform for Data Entry

Originally posted by : KevinM (mullarkey@fs5.ee.umist.ac.uk) OK... In a one to many db relationship in MS Access you woud typically have a main form and a continuous.cascading subform. But how do you emaulate this on an asp page? While it's easy enough to loop through the 'many' recordest and display them in a table, what's the best method in entering this data via an asp page and then retrieving it for updating. Do you have to do one line at a time or can you have a table with say 10 rows all with the text boxes with the same name. What I have at the moment is a table of 10 rows and each field ends in a number, i.e. in row one there is a field called Description1 and in the second row Description2 and so on up to 10. This doesn't seem the ideal way to do it although it does work. I would rather loop through one field name i.e . Description 10 times but am not sure if this would work and if each row would get entered correctly. I have also tried exporting the for from Access but it on displays the main form ! Many Thanx Kevin M

May 16th, 2000, 04:27 PM

Steve Schofield

Contributing User

Join Date: Dec 2002

Posts: 14,575

Time spent in forums: < 1 sec
Reputation Power: 22

Originally posted by : tony (tbell@bell-consulting.com) This is what I found. You'll have to edit it to fit you needs. To see this in a better format please email me personally and I can send you the code and documentation in a word file. Tony Build dynamically expanding order entry pages by Brad Wist If you've read about the W3C's plans for the future of HTML forms, then you know that dynamically expanding rows are soon to be part of XHTML. However, with Internet time the way it is, you probably don't have the luxury of waiting half a year for the W3C to implement XForms. Fortunately, if your work is intranet-based, you can use VBScript to create a very flexible and extensible data entry system. In this article, we'll show you how to build an order entry page in which the rows grow to accommodate any amount of data. Fixed row dilemma Figure A shows a classic order entry page. Figure A: We'll use DHTML to add a new row to the existing page. As you can see, a single buyer can enter in many orders. Most database programs such as Microsoft Access and SQL Server make setting up such an input form a breeze. However, in an ASP page, things get a little trickier. For instance, we could build a page with 10 lines, for 10 separate orders. But what happens if the wholesaler wants to order 11 items? The page would have to force them to a second order form, and then somehow tie the two pages together. While this is possible, a better alternative is to allow the form to dynamically grow, as required, so that it will hold as many lines as the buyer needs. Expanding rows In a nutshell, we'll create a server-side variable that contains an HTML template for a single row. We'll then use the InnerHTML method to dynamically add this blank row to a area. Of course, each new row will need an ID number so we can differentiate it in the client-side script. We'll use the Replace method to insert the appropriate row number in place of a custom identifier in the HTML row template. Server-side data retrieval As our first step, we'll need to retrieve buyer and product data from our database for use in the Web page. Listing A shows the ASP code for this task. We've provided the OrderEntry MDB file in this month's download. Listing A: Server-side data retrieval <%@ Language=VBScript %> <% option explicit Response.Expires=0 dim sTable 'Name of the Main Table dim aSubTables 'Array of Subtable Names dim iSubCount 'Number of subtables sTable = "Order" aSubTables = Array("orderItem") '---------- ' Get the Number of Subtables for use later ' if there aren't any subtables, the ubound ' function will cause an error '---------- on error resume next iSubCount = ubound(aSubTables) if Err.number<>0 then iSubCount=-1 Err.Clear end if on error goto 0 %>  <% 'INCLUDE contains ADO constants '---------------------------------------- ' Pull the Lookup information from the DB '---------------------------------------- dim adConn, rsShopper, rsProduct, sSQL, QQ, i QQ = chr(34) set adConn = server.CreateObject("ADODB.Connection") adConn.Open "Provider=Microsoft.Jet.OLEDB.3.51;" _ & "Data Source=" & server.mappath("orderentry.mdb") sSQL = "SELECT id, txtLastName, txtFirstName, " _ & "txtCompany FROM buyers ORDER BY txtLastName, " _ & "txtFirstName" set rsShopper = server.CreateObject("ADODB.recordset") rsShopper.Open sSql, adConn, adOpenForwardOnly, _ adLockReadOnly, adcmdText sSQL = "SELECT prodID, txtProdName, txtProdDesc, " _ & "amPrice FROM product" set rsProduct=server.CreateObject("ADODB.Recordset") rsProduct.Open ssql, adconn, adOpenStatic, _ adLockReadOnly, adCmdText In addition, the server-side script creates the blank-row template, and adds the data from the database. Listing B shows the code for this operation. As you can see, this script defines the sServerRow variable. It builds a table row cell by cell. It also includes a select box, which holds a list of products. The value of each product in the select box consists of its ID along with its cost, which are separated by a dollar sign ($). Listing B: Creating the HTML row template '---------------------------------------- ' Definition of the Dynamic Row '---------------------------------------- dim sServerRow sServerRow = "<table width='100%'><tr><td width='5%'>" _ & ":#</td><td width='5%'><input name='_1qyProduct' " _ & "maxlength='5' size='5' language='vbscript' " _ & "onchange='DoTotal' value='0'></td><td width='50%'>" _ & "<select name='_1idProduct' language='vbscript' " _ & "onchange='DoPrice(:#)'>" sServerRow = sServerRow & "<option value='0$0'>" _ & "SELECT PRODUCT" Do While Not rsProduct.EOF sServerRow = sServerRow & "<option value='" _ & rsProduct("prodID") & "$" & rsProduct("amPrice") _ & "'>" & rsProduct("txtProdName") & " (" _ & rsProduct("txtProdDesc") & ")" rsProduct.MoveNext Loop sServerRow = sServerRow & "</select></td>" _ & "<td width='20%'><input name='_1amPrice' " _ & "maxlength='10' size='10'></td><td width='20%'>" _ & "<input name='_1amCost' maxlength=10 size=10>" _ & "</td></tr></table>" %> Notice that we prefixed the name of each field in the row template with an underscore and a one (_1). This naming convention indicates that these fields belong to the first subtable on this page. The basic HTML body Listing C shows our Web page's <body> section. If you study the common area, you'll see that the page stores the table name and the number of subtables in the HTML form so that server-side script can enter the data into the database. However, that functionality is beyond the scope of this article, and we'll cover it in a future article. Listing C: The order entry page's body section <body> <form action="inputData.asp" id="frmInput" name="frmInput" method=post>  <input type=hidden name="_!table" value="<%=sTable%>"> <% if iSubCount >= 0 then For i = 0 to iSubCount %> <input type="hidden" id="_!subtable<%= cstr(i+1) %>" value="<%=aSubTables(i)%>"> <% Next End If %>  <table><tr><td>Buyer:</td> <td><select name="idShopper" onchange="DoReset"> <% Do While Not rsShopper.EOF Response.Write "<option VALUE=" & QQ _ & rsShopper("id") & QQ & ">" _ & rsShopper("txtLastName") & ", " _ & rsShopper("txtFirstName") & "| " _ & rsShopper("txtCompany") rsShopper.MoveNext Loop rsShopper.close rsProduct.close set rsShopper=Nothing set rsProduct=Nothing set adConn = Nothing %> </select></td></tr> </table>  onmouseover="window.event.srcElement.style =>.cursor = 'hand'" onmouseout="window.event.srcElement.style =>.cursor=''"><h3>Add Row</h3> <table width=100%> <tr><th width=5%>#</th> <th width=5%>Qty</th> <th width=50%>Product</th> <th width=20%>Price</th> <th width=20%>Cost</th></tr> </table> <%=replace(sServerRow,":#","1")%>   <table width="100%"> <tr><td width="80%">SubTotal: </td> <td width="20%"><input name="amSubTotal" maxlength="10" size="10"></td></tr> <tr><td width="80%">Shipping: </td> <td width="20%"><input name="amShipping" maxlength="10" size=10></td></tr> <tr><td width="80%">Tax: </td> <td width="20%"><input name="amTaxes" maxlength="10" size="10"></td></tr> <tr><td width="80%">Total: </td> <td width="20%"><input name="amTotal" maxlength="10" size="10"></td></tr></table>  <input type="submit" value="Submit Order"> <input type="button" id="btnReset" onclick="DoReset" value="Reset"></form> </body> Notice that in the product area, we used the span's onmouseover and onmouseout events to change the cursor, providing a visible clue to the user that he can click this link. In addition, the span's click() event calls a client-side script routine called AddRow that adds a new HTML row template. Let's add the client-side script next. Adding rows to the page As we mentioned, to dynamically expand the page, adding order entry lines on the sheet as necessary, we'll use Dynamic HTML. Specifically, we'll expand subtable's innerHTML property with a new blank row. Listing D shows the code for this operation. Add it to the page's <head> section. Listing D: The AddRow sub procedure <script language="vbscript"> dim giRowNum, sRowOrig sRowOrig = "<% =sServerRow %>" giRowNum = 1 Sub AddRow on error resume next iLen = window.frmInput.elements("_1qyProduct") _ .length if iLen Then blnQuantity = (window.frmInput.elements _ ("_1qyProduct")(iLen-1).value > 0) blnProduct = (window.frmInput.elements _ ("_1idProduct")(iLen-1).value <> "0$0") else blnQuantity = (window.frmInput.elements _ ("_1qyProduct").value > 0) blnProduct = (window.frmInput.elements _ ("_1idProduct").value <> "0$0") end if If blnProduct and blnQuantity Then dim sLocalRow giRowNum = giRowNum + 1 sLocalRow = "<%=sServerRow%>" window.subtable.innerHTML = window.subtable _ .innerHTML & replace(sLocalRow,":#",giRowNum) End If End Sub </script> After initializing several variables, this script tracks how many rows you've added. The value uniquely identifies each row on the page, so that calculations can be performed dynamically, as we'll discuss in the next section. Another local variable, sLocalRow, stores the HTML row to insert. The routine then uses the innerHTML property to add the new row. Notice that the VBScript Replace() function inserts the current row number into the variable marker :# in the HTML row template. The code also ensures that the two fields, _1qyProduct (quantity) and _1idProduct (product) contain data before adding a new row. Extracting selected product prices Now that we have the code to dynamically build the page, we need code to extract the selected item's price. To do so, we'll call the DoPrice subroutine from _1idProduct's onchange() event, as shown in Listing E. This routine takes a parameter, i, which identifies the row for which to extract the price information. Listing E: The DoPrice script <script language="vbscript"> sub DoPrice(i) dim v, l v = window.event.srcElement.getAttribute("value") on error resume next l = window.frmInput.elements("_1amPrice").length if Err.number then 'there is only one line Err.Clear if left(v,1)="0" then window.frmInput.elements("_1amPrice").value = "" exit sub end if if instr(v, "$") then v = mid(v, instr(v, "$")+1) window.frmInput.elements("_1amPrice").value = v if window.frmInput.elements("_1qyProduct") _ .value = 0 then window.frmInput.elements("_1qyProduct") _ .value = 1 end if else window.frmInput.elements("_1amPrice").value = "" end if else if left(v,1)="0" then window.frmInput.elements("_1amPrice")(i-1) _ .value = "" exit sub end if if instr(v, "$") then v = mid(v, instr(v, "$")+1) window.frmInput.elements("_1amPrice")(i-1) _ .value = v if window.frmInput.elements("_1qyProduct") _ (i-1).value = 0 then window.frmInput.elements("_1qyProduct") _ (i-1).value = 1 end if else window.frmInput.elements("_1amPrice")(i-1) _ .value = "" end if end if DoTotal End Sub </script> Initially, the code determines the value of the select box that triggered the event. Then, it checks if the form contains more than one row. To do so, the code checks the length of of the _1amPrice control collection. This property returns an error if the form contains only one _1amPrice text box. Otherwise, the property returns the number of text boxes in the collection. Once the code determines this number, it parses the current select box value and places it in the price text box. Next, the procedure initiates the DoTotal procedure, which calculates the subtotal and grand total. Dynamic totals Listing F shows the code to calculate totals on the fly. As you can see, it loops through the form's rows, calculating the appropriate values as it goes. Listing F: The DoTotal procedure <script language="vbscript"> sub DoTotal dim i, j, LineTotal, SubTotal, Shipping, Tax SubTotal=0 LineTotal=0 on error resume next l = window.frmInput.elements("_1qyProduct").length if Err.number then 'only one line LineTotal = csng(window.frmInput.elements ("_1qyProduct").value) * csng(window.frmInput _ .elements("_1amPrice").value) window.frmInput.elements("_1amCost").value = LineTotal SubTotal = csng(SubTotal) + csng(LineTotal) Err.Clear else for i = 0 to window.frmInput.elements("_1qyProduct") _ .length-1 LineTotal=csng(0) LineTotal = csng(window.frmInput.elements _ ("_1qyProduct")(i).value) * csng(window.frmInput _ .elements("_1amPrice")(i).value) window.frmInput.elements("_1amCost")(i) _ .value = LineTotal SubTotal = SubTotal + LineTotal next end if window.frmInput.amSubTotal.value=clng(SubTotal*100 )/100 'apply shipping rules Shipping = 5 window.frmInput.amShipping.value=clng(Shipping*100 )/100 'apply tax Tax = SubTotal * 0.045 window.frmInput.amTaxes.value=clng(Tax*100)/100 'calculate total window.frmInput.amTotal.value=clng( (csng(SubTotal) _ + csng(Tax) + csng(Shipping)) *100)/100 end sub </script> Resetting the page Of course, in addition to adding rows to the page, we need a way to remove them, such as when a user changes from one buyer to another, or when he clicks the Reset button. To do so, we use a few simple lines of code Sub DoReset '*** Place code to save ' current data here! window.subtable.innerHTML = _ Replace(sRowOrig, ":#", "1") giRowNum = 1 document.frmInput.reset End Sub Validating the data Once you build the page, you'll need to adapt your form validation routines to take into account field collections. We adapted the ValidateDataTypes() function, which we covered in January 2000's article "Data validation strategies," to take such collections into account. We've included this updated validation code with this month's download. Extending the order entry page No doubt, you can see from our example that you can build a flexible, dynamic Web page that can expand as needed. In addition to the technique we've shown you in this article, you can expand this page even further to implement data entry Web pages that mimic those found in database programs ------------ KevinM at 5/15/2000 4:10:27 AM OK... In a one to many db relationship in MS Access you woud typically have a main form and a continuous.cascading subform. But how do you emaulate this on an asp page? While it's easy enough to loop through the 'many' recordest and display them in a table, what's the best method in entering this data via an asp page and then retrieving it for updati g. Do you have to do one line at a time or can you have a table with say 10 rows all with the text boxes with the same name. What I have at the moment is a table of 10 rows and each field ends in a number, i.e. in row one there is a field called Description1 and in the second row Description2 and so on up to 10. This doesn't seem the ideal way to do it although it does work. I would rather loop through one field name i.e . Description 10 times but am not sure if this would work and if each row would get entered correctly. I have also tried exporting the for from Access but it on displays the main form ! Many Thanx Kevin M

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: