Visual Basic Concepts

Creating an HTML Page that Runs a Stored Procedure

You can increase the power and flexibility of your HTML applications by enabling them to run SQL Server stored procedures. You can create a stored procedure in the Data View window, then create Command objects and elements that use the stored procedure to view or update data.

This topic shows how to create a stored procedure that returns customer address records by country from the Customers table created earlier in this scenario. It shows how to add a Select element to the HTML page you created in the previous topic. The Select element uses the stored procedure to display address records based on the selected country.

To create an HTML page that runs a stored procedure

1. Add a stored procedure to the database.

2. Create a data environment Command object based on the stored procedure.

3. Add Labels and TextField Elements to a new HTML Page.

4. Add a Select element to the HTML page.

5. Add code to run the stored procedure and display the results.

6. 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 a Stored Procedure to the Database

You can add a stored procedure to the database by right-clicking the Stored Procedures folder in the Data View window and selecting the New Stored Procedure command. The Data View window displays the New Stored Procedure window, where you enter the SQL Create Procedure statement that defines the stored procedure.

For example, you can add a spCustByCountry stored procedure to the Pubs database that accepts an input parameter, Country, and returns customer address information for the specified country. Enter the following Create Procedure statement in the New Stored Procedure window:

CREATE PROCEDURE spCustByCountry @Country varchar(20)
AS
SELECT * FROM Customers
WHERE Country = @Country

When you save the stored procedure, it is added to the database.

For More Information   For more information on using the Data View window to create a stored procedure, see Stored Procedures in the SQL Editor.

Create a Data Environment Command Object Based on the Stored Procedure

In the "Create a Data Entry HTML Page" topic, you created a data environment Command object based on the Customers table in the Pubs sample database. You can also create a data environment Command object based on a stored procedure.

For example, you can create a data environment Command object based on the spCustByCountry stored procedure. Follow the steps in the "Interacting with Data in a Microsoft Jet/Microsoft Access Database" scenario, Chapter 5 of the Data Access Guide, to create the Command object, setting the following properties:

Add Labels and TextField Elements to a New HTML Page

After you've created the command that will provide the data source for your HTML page, you can create the page. Select Add DHTML Page from the Project menu. As you saw in the previous topics in this scenario, you can use the DHTML Page designer to add labels and TextField elements to the HTML page.

Add a Select Element to an HTML Page

You can use the DHTML Page designer to add a Select element to your HTML page. A Select element, similar to a combo box, lets you select an item from a list.

For example, to add a Select element called SelectCountry to the page you created in the previous topic, drag the element onto the page. Then set the element's Name and id properties to SelectCountry.

You can specify the list items to be displayed in the Select element by adding code to the HTML page's Load event procedure. For example, to display a list of countries included in the address information from the Customers table, add the following code:

Private Sub DHTMLPage_Load()
   ' Declare Select element, connection, recordset, and string variables.
   Dim selElement As HTMLSelectElement
   Dim cnn As ADODB.Connection
   Dim rsCountries As ADODB.Recordset
   Dim strSQL As String

   ' Assign the list of countries from the Customers table
   ' to the recordset variable.
   strSQL = "SELECT DISTINCT Country FROM Customers;"
   Set cnn = New ADODB.Connection
   cnn.Open MyDataEnvironment.Connection1
   Set rsCountries = New ADODB.Recordset
   rsCountries.Open strSQL, cnn1

   ' Fill the Select element's list with countries from the recordset,    ' setting each list element's Text property.   With rsCountries
   Do While Not .EOF
      Set selElement = DHTMLPage.Document.createElement("OPTION")
      selElement.Text = !Country
      SelectCountry.Options.Add selElement
      .MoveNext
   Loop
   End With

End Sub

For More Information   For more information on creating a Select element, see Working with Lists in the Page Designer in Building Internet Applications in the Component Tools Guide.

Add Code to Run the Stored Procedure and Display the Results

Using the data environment, ADO, and the BindingCollection object, you can easily add code to a Select element's onafterupdate event procedure to run a Command object that is based on a stored procedure. You can then update the data displayed in an HTML page.

For example, you can display address records on your HTML page for customers from a country selected in the SelectCountry element. First, declare a BindingCollection object variable in the HTML Page object's Declarations section:

Dim colBind As BindingCollection

Then add code to the element's onchange event procedure to run the cmdCustByCountry Command object using the value of the element as an input parameter:

Private Sub SelectCountry_onchange()

   Dim rsResults As ADODB.Recordset
   Dim intIndex As Integer

   ' Run the CustByCountry Command object using the value
   ' of the SelectCountry element as an input parameter.
   intIndex = SelectCountry.selectedIndex
   MyDataEnvironment.CustByCountry SelectCountry.Options(intIndex).Text

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

      ' Bind the Value property of elements on the HTML page
      ' to fields in the CustByCountry 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

   ' Assign the recordset to the rsResults recordset variable.   Set rsResults = MyDataEnvironment.rsCustByCountry      ' Add HTML code to dynamically create a table on the page
' showing each customer record.
DHTMLPage.Document.body.insertAdjacentHTML "BeforeEnd", "<DIV><HR SIZE=2></DIV>"   With rsResults      Do While Not .EOF         DHTMLPage.Document.body.insertAdjacentHTML "BeforeEnd", _         "<TABLE BORDER CELLSPACING=1 CELLPADDING=7 ><TR HEIGHT=17>" & _
         "<TD WIDTH=70>" & rsResults!CustomerID & "</TD>" & _
         "<TD WIDTH=200>" & rsResults!CompanyName & "</TD>" & _
         "<TD WIDTH=300>" & rsResults!Address & "</TD>" & _
         "<TD WIDTH=100>" & rsResults!City & "</TD>" & _
         "<TD WIDTH=50>" & rsResults!Region & "</TD>" & _
         "<TD WIDTH=50>" & rsResults!PostalCode & "</TD>" & _
         "<TD WIDTH=50>" & rsResults!Country & "</TD></TR>" & _
         "</TABLE>"
         .MoveNext
      Loop
      .MoveFirst
      .Close
   End With

End Sub

Load the HTML Page

To view the finished page in Internet Explorer, press F5 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 clicking Make .dll on the File menu.

Step by Step

This topic concludes a series that walks you through creating a simple DHTML application that interacts with data in an SQL Server database. To start from the beginning, see Creating a DHTML Application that Interacts with SQL Server Data.