How To Create an Updateable Grid by Using the Visual InterDev 6.0 Grid DTC (229672)


The information in this article applies to:

  • Microsoft Active Server Pages
  • Microsoft Visual InterDev 6.0
  • Microsoft Internet Information Server 4.0
  • Microsoft Internet Information Services 5.0
This article was previously published under Q229672

IN THIS TASK

SUMMARY

The nature of the Visual InterDev 6.0 Grid Design-Time Control (DTC) is that it is "read only." That is, it is designed to display information in a tabular format and provide navigation controls. However, the information displayed cannot be updated.

This article shows you how to leverage the features of the Visual InterDev 6.0 Grid DTC, enabling users to both display and update data.

The feature of the Grid DTC that accomplishes this task is the Field/Expression field, which is available for each column displayed by the Grid DTC. The Field/Expression field allows you to generate your own output (using JScript) and include bound data in that output.

Using the capability to generate custom output, you will output standard HTML <INPUT> tags that contain the data bound to the Grid DTC. Since pages that use the Visual InterDev 6.0 Scripting Object Model (SOM) are HTML forms, your custom <INPUT> tags will be submitted in the Request.Form collection every time the page is entered. You can then use this data to update the recordset information.

back to the top

Requirements

This sample uses the Northwind database. Before you begin, open a Visual InterDev 6.0 project with a Data Connection to the Northwind database, which is called "nwind." The Northwind database can be installed as an option with Microsoft Access or Microsoft Visual Basic and can be found at \Program Files\Microsoft Office\Office\Samples\Northwind.mdb or \Program Files\Microsoft Visual Studio\VB98\Nwind.mdb, respectively.

IMPORTANT: This sample works only for Active Server Pages (ASP) with the Scripting Object Model enabled with a DTC Scripting Platform of "Server (ASP)."

back to the top

Create the ASP Page and Add the Design-Time Controls

  1. Add an ASP page to your project.
  2. To add a Recordset DTC, click the View menu, select Toolbox, select Design-Time Controls, and drag a Recordset DTC onto the ASP page in the <BODY> section.
  3. Add a Grid DTC to the <BODY> section after the Recordset DTC.
back to the top

Set the Properties of the Recordset DTC

  1. Select the Recordset DTC (Recordset1).
  2. Set the Connection property to nwind (see "Before You Begin" earlier in this article), the Database Object property to Tables, and the Object Name property to Products.
back to the top

Set the Properties of the Grid DTC

  1. Right-click the Grid DTC (Grid1) and select Properties.
  2. In the General tab, set the Style Name to Inset Gray and change the Width from Pixels to Percentage.
  3. Select the Navigation tab and set the records/page property to 10.
  4. Select the Data tab, and set the Recordset property to Recordset1.
  5. In the Available Fields window, select ProductID, ProductName, QuantityPerUnit, and Discontinued.

    ProductID
    The ProductID field supports two functions. First, it displays the ProductID as a non-updateable field. Second, it contains a hidden field that references the absolutePosition of the current bound record.

    1. In the Grid Columns dialog box, select ProductID.
    2. Change the Header value to Product ID and the Field/Expression value to the following: 
      ="<font color=navy>" + [ProductID] + "</font><input type=hidden name=abspos value=" + Recordset1.absolutePosition + ">"
      Setting the Field/Expression value to this expression will result in the Grid DTC generating the following HTML output: 
      <font color=navy>3</font><input type=hidden name=abspos value=3>
      The displayed ProductID value (here it shows as "3") is the bound value of the current ProductID field of the current record. This is done by supplying [ProductID] in your Field/Expression expression. The value of the hidden field (here it shows as "value=3") contains the absolutePosition value of the current record. Do this by supplying Recordset1.absolutePosition in your Field/Expression expression. Note that the value of the ProductID and the absolutePosition are equal in this instance by coincidence.
    3. Click Update.
    ProductName
    ProductName will be one of the updateable fields.
    1. In the Grid Columns dialog box, select ProductName.
    2. Change the Header value to Product Name and the Field/Expression value to the following: 
      ="<input type=text size=40 maxlength=40 name=ProductName value=\\\"" + Server.HTMLEncode([ProductName]) + "\\\">"
      Setting the Field/Expression value to this expression will result in the Grid DTC generating the following HTML output: 
      <input type=text size=40 maxlength=40 name=ProductName value="Chef Anton's Cajun Seasoning">
      The displayed ProductName value (here it shows as "Chef Anton's Cajun Seasoning") is the bound value of the current ProductName field of the current record. This is done by supplying [ProductName] in your Field/Expression expression. Note that [ProductName] is enclosed in the Server.HTMLEncode() method. This ensures that the entire ProductName value is properly bound, even if it contains double-quotes (").

      Note that "value=" is followed by \\\". This ensures that a double-quote surrounds the ProductName value in the HTML output. \\\" represents two JavaScript escape sequences: \\ and \". \\ will return a single backslash and \" will return a double- quote. Why do you use a backslash and a double-quote when you only want a double-quote? This is because your Field/Expression expression will actually be evaluated twice. The first time it is evaluated the two escape sequences evaluate to \", which will evaluate to your lone double-quote when the expression is evaluated a second time.
    3. Click Update.
    QuantityPerUnit
    Follow the same instructions as ProductName, but supply Qty. Per Unit for the header and use [QuantityPerUnit] in the expression. The size and maxlength properties of the <input> tag should be 20, and the name of the <input> tag should be QuantityPerUnit.

    Discontinued
    Discontinued is a different type of field because it represents a yes/no selection or a choice. With ProductName and QuantityPerUnit, you will notice that the field names of the <input type=text> fields are the same for every column of the Grid. When the values are posted back to the Active Server Page, the values will be placed in an array referenced by that single name (more on this in Step 4).

    With a field that represents a choice (such as a checkbox or a radio button), you will be unable to use this "single name" technique for an array of values. This is because checkboxes and radio buttons already use a single name to create a single choice object. For example, if you want a radio button that gives you a "Yes or No" choice, you would code the following: 
    <input type=radio name=MyRadio value=Yes> Yes  <BR> 
<input type=radio name=MyRadio value=No>  No
    If you used the single name technique, you would end up with a single checkbox or radio button for the entire column, rather than an array of values. This being the case, use instead the absolutePosition property to "bind" your choice to your particular row and ensure that the name of the choice is unique.

    You will also be employing a different technique to write out your custom HTML from the Grid DTC's Field/Expression field. Since you will need to make a decision as to which choice will be checked (using the CHECKED property), you will create a function that will output your HTML, and you'll call that function from the Field/Expression field. Follow these steps:
    1. In the Grid Columns dialog box, select Discontinued.
    2. Change the Header value to Discontinued? and the Field/Expression value to the following: 
      =WriteDiscontinued([Discontinued], Recordset1.absolutePosition)
    3. Click Update. Click OK to close the Properties page of the Grid DTC.
    4. In the <HEAD> section of the page, place the following code: 
      <SCRIPT LANGUAGE=vbscript RUNAT=Server>
 
Function WriteDiscontinued(blnDiscontinued, numPos) 
  If blnDiscontinued Then
    CheckedYes = "CHECKED"
  Else
    CheckedNo  = "CHECKED"
  End If 
   
  OutVal = "<input name=Discontinued" & numPos & " value=""1"" type=radio " & CheckedYes & "> Yes<BR>"
  OutVal = OutVal & "<input name=Discontinued" & numPos & " value=""0"" type=radio " & CheckedNo & "> No<BR>" 
 
  WriteDiscontinued = OutVal 
End Function 

</SCRIPT>
    Now the bound column will call the function WriteDiscontinued and pass it the bound value for Discontinued (a true/false value) and the absolutePosition of the current record. Use the Boolean value for the discontinued field to decide which radio button is checked and use the absolutePosition value to create a unique name for your radio button that is bound to the specific record. You will return the generated HTML to the Field/Expression field and the Grid DTC will write your output as a part of the Grid.
back to the top

Update Pages Using thisPage_onenter ()

The final step in making your Grid DTC updateable is to perform the updates where necessary. You can do the updates every time the page is accessed by using the thisPage_onenter() event. In the <HEAD> section of the page, place the following code: 
<SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server>

Sub thisPage_onenter()
  If Request.Form.Count > 0  Then
    Set tempRS = Recordset1.getRecordSource()
 
    For i = 1 to Request.Form("abspos").Count 
      var = Request.Form("abspos")(i)
 
      tempRS.absolutePosition = CInt(var)
    
      'Test for change and update the ProductName field
      If tempRS("ProductName") <> Request.Form("ProductName")(i) Then
        tempRS("ProductName") = Request.Form("ProductName")(i)
      End If
       
      'Test for change and update the QuantityPerUnit field (QuantityPerUnit allows nulls)     
      If (tempRS("QuantityPerUnit") <> Request.Form("QuantityPerUnit")(i)) Or _
         (IsNull(tempRS("QuantityPerUnit")) And Request.Form("QuantityPerUnit")(i) <> "") Then
        tempRS("QuantityPerUnit") = Request.Form("QuantityPerUnit")(i)
      End If
 
      'Test for change and update the Discontinued field
      If tempRS("Discontinued") <> Request.Form("Discontinued" & var) Then
        tempRS("Discontinued") = Request.Form("Discontinued" & var)
      End If
 
    Next
  
    tempRS.Update
  
    Recordset1.setRecordSource(tempRS)
  End If
End Sub

</SCRIPT>
Note that when you set the Field/Expression values for the Grid DTC, the <INPUT type=text> and <INPUT type=hidden> fields have the same name value for each record that appears (except for the Discontinued column). As a result, when the Request.Form collection is created, you will have a single entry for each column that contains an array of the displayed values in order by row. The thisPage_onenter() event loops through these arrays to retrieve the values and update them if necessary.

The Discontinued field will be referenced differently than the other fields since it is a choice field. Notice that the other fields are referenced by array ordinal reference, where the Discontinued field is referenced by unique name (for example, "Discontinued3").

Note, also, that you will use the absolutePosition value to navigate to the specific row to determine if the values require updating. You will first create a local ADO Recordset object by using the Recordset DTC's getRecordSource() method. Then you'll perform the updates. You will then reset the Recordset DTC's underlying ADO Recordset to your updated recordset using the Recordset DTC's setRecordSource() method. back to the top

Adding an Update Button (optional)

You may have noticed that your application updates the data only when one of the navigation buttons is clicked. This is because the updates occur in the thisPage_onenter() event, which fires only when the page is requested or one of the navigation buttons is clicked.

You may want to provide your users with a button to allow them to update immediately without requiring them to navigate to another record. Use the following steps to do this:
  1. Place a Button DTC on the page after the Grid DTC.
  2. Right-click the Button DTC and select Properties.
  3. Set the Name to btnUpdate and the Caption to Update, and click OK.
  4. From the View menu, select Other Windows and click Script Outline.
  5. Expand Server Objects & Events. Expand btnUpdate, and double-click onclick. This will place the following code in your page: 
    Sub btnUpdate_onclick()

End Sub
You do not need to place any code in the btnUpdate_onclick() event. Simply clicking the button causes the page to be reentered, and thisPage_onenter() will fire and perform the updates.

back to the top



Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbCodeSnippet kbCtrl kbDatabase kbhowto kbHOWTOmaster KB229672 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.