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
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.
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.
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:
| Property | Setting |
| Command Name | CustByCountry |
| Connection | Connection1 |
| Database Object | Stored Procedure |
| Object Name | dbo.spCustByCountry |
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.
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 = Me.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.
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.
Me.Document.body.insertAdjacentHTML "BeforeEnd", "<DIV><HR SIZE=2></DIV>" With rsResults Do While Not .EOF Me.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
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.
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.