Visual Basic Concepts

Creating an HTML Page for Viewing and Updating Data

To work with existing data in a SQL Server database, you can create an HTML page that lets you view and edit the data. You can use the DHTML Page designer to create a new HTML page or modify an existing page. Then write code that uses ADO and the BindingsCollection object to handle data processing.

This topic shows how to create an HTML page to view and edit data in the Customers table you created and populated in the previous topic in this scenario. It shows how to use a data environment Command object, ADO code, and the BindingsCollection object to directly update SQL Server data from the HTML page.

To create an HTML page for viewing and update data

1. Add labels and TextField elements to a new HTML page.

2. Modify the data environment Command object to allow data updating.

3. Add code to display records on the HTML page.

4. Add Button elements to navigate through records.

5. Load the HTML page.

Note   This topic is part of a series that walks you through creating a simple database application that interacts with data in an SQL Server database. It begins with the topic Creating a DHTML Application that Interacts with SQL Server Data.

Add Labels and TextField Elements to a New HTML Page

The first step in creating an HTML page for viewing and updating data is to open a new HTML page, by selecting Add DHTML Page from the Project menu.

You can use the DHTML Page designer to add labels and TextField elements to the HTML page. For example, to create an HTML page for viewing and updating data in the Customers table in the Pubs sample database, follow the steps in the Creating a Data Entry HTML Page topic.

Modify the Data Environment Command Object to Allow Data Updating

When you create a Command object using the Data Environment designer, the Command object by default allows you to read but not update the Command object's data source. To allow data updating, you must change the Command object's CursorType, CursorLocation, and LockType properties.

For example, you can change the Customers Command object's CursorType, CursorLocation, and LockType properties to allow data in the Customers table to be updated directly from your HTML page. In the Data Environment designer, select the Customers Command object and click the Properties button on the Data Environment toolbar. Click the Advanced tab, and then set the CursorLocation property to Use server-side cursors, the CursorType property to Keyset, and the LockType property to Optimistic.

Add Code to Display Records on the HTML Page

Using the data environment, ADO, and the BindingCollection object, you can bind elements on an HTML page to SQL Server data when the page loads. You can then edit the data, and add code to update the underlying recordset or navigate through the records. For example, you can use a BindingCollection object with an ADO Recordset object to bind the TextField elements on your HTML Page to fields in the Customers table.

First, add a reference to the BindingCollection object's type library to your project. To add the reference, select References on the Project menu, then select Microsoft Data Binding Collection in the References dialog box. Then declare a BindingCollection object variable in the HTML Page object's Declarations section:

Dim colBind As BindingCollection

To bind the elements to the table fields when the HTML page loads in your browser, add code to the DHTML Page object's Load event procedure. As you saw in the "Creating a Data Entry HTML Page" topic, your data environment can provide the "middle tier" between the table and the HTML page.

Use the BindingCollection object to bind the elements on the HTML page to fields in the recordset:

Private Sub DHTMLPage_Load()

   ' Create a BindingCollection object, then set its
   ' DataSource property to your data environment and its
   ' DataMember property to the Customers command object.
   Set colBind = New BindingCollection
   With colBind
      Set .DataSource = MyDataEnvironment
      .DataMember = "Customers"

      ' Bind the Value property of elements on the HTML page
      ' to fields in the Customers recordset.
      .Add CustomerID, "Value", "CustomerID"
      .Add CompanyName, "Value", "CompanyName"
      .Add Address, "Value", "Address"
      .Add City, "Value", "City"
      .Add Region, "Value", "Region"
      .Add PostalCode, "Value", "PostalCode"
      .Add Country, "Value", "Country"
   End With

End Sub

For More Information   For more information see in the Language Reference.

Add Button Elements to Navigate through Records

After you've established data binding between your data environment and the object on the HTML page, you can easily create Button elements for navigating through records by using the recordset's MoveNext and MovePrevious methods. For example, to create a Next button for your HTML page, use the DHTML Page designer to add a Button element to the HTML page. Change its id and Name properties to Next. Then add the following line to the Button element's Next_onclick event procedure:

MyDataEnvironment.rsCustomers.MoveNext

Similarly, you can create a Previous button by adding a Button element to the HTML page and change its id and Name properties to Previous. Then add the following code to the Button element's Previous_onclick event procedure:

MyDataEnvironment.rsCustomers.MovePrevious

When you load the HTML page, Visual Basic displays the records in the Customers table and lets you move forward and backward through the recordset.

Load the HTML Page

To view the finished page in Internet Explorer, press the F5 key or click the Start button on the Standard toolbar. You can also explicitly make a dynamic-link library and HTML page for the project by setting properties on the Make tab of the Project Properties dialog box, then selecting Make .dll from the File menu.

Step by Step

This topic is part of a series that walks you through creating a simple DHTML application that interacts with data in an SQL Server database.