Visual Basic Concepts

Step Six: Write Code to Retrieve and Display Customer IDs

Now that you've connected the ShowAllCustomers button to the ShowIDs custom webitem, it is time to write the code that will retrieve the customers list and display it to the user. You do this in the ShowIDs_Respond event.

Working with data is the same in a webclass as it is in any other type of Visual Basic project. In this sample, you will use ADO to open your database connection and retrieve records. The part of the process that differs from standard projects is how you display the retrieved records to the user. You will use an Active Server Pages object (the Response object) to dynamically generate an HTML page that displays the results.

All code shown in this step is part of the same procedure — ShowIDs_Respond. See the fully commented code in the Support1.vbp sample for additional information.

For More Information For more information on the Active Server Pages Response object, see "The Object Model for IIS Applications" in the "Developing IIS Applications with Webclasses" section of the Components Tools Guide, in the MSDN Library.

To create a reference to the ADO Type Library

On the Project menu, click References.
Confirm that the Microsoft ActiveX Data Objects 2.0 Library reference is checked, then click OK.

To create the database connection code

Access the code window.
Add the following statements to the general declarations of the webclass code:
```
Private cn1 As New ADODB.ConnectionPrivate rs1 As New ADODB.Recordset
```

Define the following function in your code:

Private Sub ConnectMe(objConnection As ADODB.Connection)    objConnection.Provider = "Microsoft.Jet.Oledb.3.51"    objConnection.Open "c:\yourdirectory\contact.mdb", "admin", ""End Sub

Note You must replace the path in the Open statement with the location of the contact.mdb database on your machine.

To write the code to retrieve and display all customer IDs

Insert the following code into the Private Sub ShowIDs_Respond() procedure. This code calls the procedure we created above to open a database connection. The cn1 connection will be used throughout the application, so it is best to check the connection state to avoid errors:
```
If cn1.State = adStateClosed Then
    ConnectMe cn1
End If
```

Add the following code to retrieve a recordset:

rs1.Open "select * from Contacts", cn1, adOpenStatic, adLockReadOnly

Add this code to use the Response.Write method to create an HTML page and a line of text on it:
```
Response.Write "<HTML><BODY><H1>Customers in the System</H1><HR>"
```

Add this code to create the first row of table data, containing labels for each column:

With Response   .Write "<table border=1 width=90% cellpadding=3>"
   .Write "<TR><TH>Contact ID</TH><TH>Name</TH><TH>Company</TH>" & _
        "<TH>Phone</TH>"

Add the following loop to dynamically create the rest of the table rows and populate them with the data from the recordset.

Do While rs1.EOF = False
  .Write "<TR><TD>" & rs1("contactID") & "</td>"
  .Write "<TD><a href=""" & URLFor("Detail", CStr(rs1("contactID"))) _
        & """>" & rs1("firstname") & " " & rs1("lastname") & "</a></td>"
  .Write "<TD>" & rs1("companyname") & "</td>"
  .Write "<TD>" & rs1("workphone") & "</td></tr>"
  rs1.MoveNext
Loop
End With

Note This code builds the hyperlink (<a href>) HTML using the URLFor Method provided by Visual Basic. This will be explained in Step Nine. For now, just insert that line as shown.

Add the following code to close the table and the HTML page, then close the database connection:
```
Response.Write "</table></BODY></HTML>"cn1.Close
```
Save your webclass project.