Retrieving Records

Now that you know several ways of connecting to an Access database in an ASP page, you will learn how to create an ASP page from scratch and execute a simple SQL statement to return some data from a table. The example ASP code in Hands-On 28-7 retrieves only customer names from the Customers table.

® Hands-On 28-7: Creating an ASP File to Retrieve Records

If you don't feel like typing, locate the GetCustomers.asp file from the downloadable files and copy it to your Learn_ASP folder.

"C:\Learn_ASP\Northwind.mdb" Set rst = conn.Execute("SELECT CompanyName FROM Customers") Do While Not rst.EOF

Response.Write rst("CompanyName") & "<BR>" rst.MoveNext Loop

2. Save the file as GetCustomers.asp in your Learn_ASP folder, and close Notepad.

The GetCustomers.asp file shown above begins by specifying a scripting language for the page by using the ASP directive <%@ LANGUAGE= VBScript %>. The script contained in the <% and %> is Visual Basic script. This script performs the following actions: ■ Creates an instance of the ADO Connection object

Opens the connection to the Northwind database using the Microsoft Access driver (this is the DSN-less connection that was discussed earlier)

The SQL SELECT statement retrieves the values in the CompanyName field from the table named Customers into a Recordset object named rst. The SELECT statement is executed with the Execute method of the Server Connection object. Notice that the instance of the Recordset object is created implicitly when the SQL statement is executed.

The Do While loop is used to output all the rows from the recordset to the browser.

5Ó8 I Chapter 28

Part V

The Write method of the Response object outputs the value of a specific string or expression to the browser. Here, the value of the CompanyName field is written to the browser with the Response.Write statement like this:

Response.Write rst("CompanyName") & "<BR>"

The rst("CompanyName") retrieves the value of the CompanyName field from the Recordset object. You can output the values from the Recordset object by using any of the following statements:

Because the Fields collection is the default collection of the Recordset object, you can omit the word Fields.

The HTML <BR> tag is used to produce a carriage return effect after the value of the CompanyName field is output to the browser. Thanks to this tag, all company names are displayed on separate lines. The MoveNext method moves to the next record in the Recordset.

Now that you know what the code does, let's proceed and request this page in the browser.

Figure 28-20: The ASP page created in Hands-On 28-7 displays the names of customers from the Customers table in the Northwind database.

Taking Your VBA Programming Skills to the Web

Breaking Up a Recordset When Retrieving Records

In the preceding section, you worked with the ASP pge that retrieved 91 records from the Customers table in the Northwind database. When you need to display more than a few records, it is a good idea to break up the recordset by dividing the list into multiple pages. This allows the user of your application to view a limited number of records at a time.

In Hands-on 28-8 you will create an ASP page that displays 12 customer names per page. The user will be able to move between the pages of data by clicking on the appropriate page number listed at the bottom of the page. To make the ASP page more useful, you will display the customer names as hyperlinks. Clicking on the customer name will call another ASP page displaying the customer's address as listed in the Customers table.

The PageMe.asp file scripting section begins with the declaration of variables. Because all variables are variants in Active Server Pages, it is convenient to list them on one line:

Dim conn, rst, mySQL, currPage, rows, counter

Following the declaration of variables, the Connection object is created and the connection to the Northwind database is opened using the Microsoft Access driver.

Next, the Recordset object is created. For Recordset paging to work properly, the CursorType must be set to adOpenStatic. Notice that the script uses the literal value (3) instead of the constant name adOpenStatic. By default, ADO enumerated constants are not defined in VBScript. However, a list of constants used with ADO is defined in the Adovbs.inc file (for VBScript) or in the Adojavas.inc file (for JScript). These files are installed in the \Program Files\Common Files\System\ado folder. To use constant names instead of their values, you can add a reference to the Adovbs.inc file at the top of your ASP page by using the #INCLUDE FILE directive, as shown below:

<!— #INCLUDE FILE="adovbs.inc" -->

For the #INCLUDE FILE directive to work, you must copy the Adovbs.inc file to the Learn_ASP folder. When you add the above directive, you will be able to use the ADO constants instead of literal values in your VBScript. Using the enumerated constants will make your code easier to understand.

Taking Your VBA Programming Skills to the Web

Use the PageSize property of the Recordset object to specify how many records are to be displayed on a page. The page is set to display 12 records:

rst.PageSize = 12

The SQL SELECT statement retrieves all the records in the Customers table into the recordset. We store this statement in the mySQL variable and proceed to open the recordset using the connection that we set up earlier:

rst.Open mySQL, conn

Next, the script retrieves the page you are currently on. If the contents of the currPage variable is an empty string (" "), then you are on the first page.

The AbsolutePage property of the Recordset object is used to move to a particular page after opening the recordset. The AbsolutePage property identifies the page number on which the current record is located. AbsolutePage equals 1 when the current record is the first record in the recordset.

Next, the rows variable is initialized to zero (0). This variable limits the number of records that are displayed on a particular page.

Next, we use the Write method of the Response object to write a little HTML code that formats the page. For example, to format the page title we use the HTML second level heading tag <H2> and its ending companion tag </H2> like this:

Response.Write "<H2>Northwind Customers</H2>"

The next two Response.Write statements will inform the user about the page number being displayed and the total number of available pages:

The HTML <I> tag will cause the text to appear in italics. You get the page number from the currPage variable and obtain the total number of pages from the PageCount property of the Recordset object.

Before we display the data, we want to draw a horizontal line on the page. This is done with the HTML <HR> tag.

Now comes the Do While loop that iterates through the recordset, counting the rows (records) as they are being retrieved and making sure that the number of records displayed per page is less than the specified page size. Company names are written to each page as hyperlinks using the HTML <A> anchor tag. The anchor tag uses the HREF attribute to designate a target page and forwards data to the target page when the user clicks the company name link:

The target page (Address.asp) is created in the next hands-on in this chapter. A question mark (?) separates the target page from the data. The data attached to the hyperlink is a field name followed by an equals sign and the field value. When you use Response.Write to write the links you must pay attention to the quotes. Notice the pairs of double quotes inside the string. Each pair of double

Part V

quotes ("") can be replaced with a single quote (') to make it easier to read, like this:

The HTML <BR> tag ensures that each company name appears on a separate line.

When the value of the rows variable is greater than the page size, the records are output to the next page.

After all records are retrieved and placed on appropriate pages, a horizontal line is placed on the page using the HTML <HR> tag. Following the horizontal line, a list of links to the individual pages appears with the text "Result Pages:" formatted in bold (see the <B> and </B> HTML tags). Again, to write those page links we use the HTML <A> tag with the HREF attribute:

Response.Write "<A HREF=""PageMe.asp?currPage=" & counter & >"

Response.Write counter & "</A>"

The next statement uses the Chr(32) function to put a space between the page links:

Response.Write Chr(32)

Finally, the script segment ends by closing all objects and releasing the memory used. We announce the end of the file by writing two ending HTML tags:

Now that you know what the code does, let's proceed to request this page in the browser.

This hands-on is required for using the company name hyperlinks in the PageMe.asp file created in Hands-On 28-8. If you don't feel like typing, locate the Address.asp file from the downloadable files and copy it to your Learn_ASP folder.

The first VBScript code segment between the <% and %> delimiters connects to the sample Northwind database using the native OLE DB Provider. The SQL SELECT statement retrieves the record for the selected customer, and the information is output to the page. First, the internal title is written out and formatted using the HTML level 1 heading tag <H1>. The user is informed about the name of the customer whose information he or she is viewing. Next follows the horizontal line (see the <HR> tag) and the table structure that displays the customer information. The HTML tag <TABLE> denotes the beginning of a table. <TR> starts a new row, and <TD> indicates the table cell (where the data is displayed). Each of these tags is closed with an ending tag (</TD>, </TR>, and </TABLE> ). Using a table to format the results is more pleasing to the eye.

Once the data is written to the page, you should provide the user with a way to return to the previous page so that another customer record can be requested. The Go Back hyperlink at the bottom of the page performs the same action as clicking the Back button in the browser's toolbar:

<CENTER>[ <A HREF="VBScnpt:history.back(1)">Go Back </A> ]</CENTER>

The HTML <CENTER> tag positions the hyperlink centered between the page margins.

Now that you know what the code does, let's proceed to request this page in the browser.

Figure 28-22: When you click the company name on the PageMe.asp page (see Figure 28-21), you are presented with the web page that displays the selected company's address.

Figure 28-22: When you click the company name on the PageMe.asp page (see Figure 28-21), you are presented with the web page that displays the selected company's address.

Retrieving Records with the GetRows Method

Instead of looping through a recordset to retrieve records, you can use the GetRows method of the Recordset object to retrieve records into a two-dimensional array. You've already seen examples of using the GetRows method earlier in this book. Hands-On 28-10 uses the GetRows method to move the records from the Shippers table into an array. Once in the array, the records are written out to a table and displayed in a client browser. When you place records into an array, you can free up the Recordset and Connection objects earlier than in a loop, thus releasing valuable server resources.

® Hands-On 28-10: Quick Data Retrieval

If you don't feel like typing, locate the FastRetrieve.asp file from the downloadable files and copy it to your Learn_ASP folder.

The VBScript code above uses the OLE DB Provider to connect to the Northwind database. After executing the SQL statement, the Write method of the Response object is used to create a table:

Response.Write "<TABLE Border=1><TR>" & VbCrLf

The VbCrLf constant denotes a carriage return/linefeed combination. Because this constant is built into VBScript, you don't need to define it before using it. The HTML <TR> tag is used for adding a table row.

Next, the For Each.. .Next loop retrieves the fields from the recordset and places the field names as table headings in the first table row. Notice how the HTML tags are embedded within the VBScript code segment. After the headings are filled in, the procedure uses the GetRows method of the Recordset object and places all the fetched records in the variable named allShippers.

Part V

Because we already have all the data that we need, we close the recordset and the connection to the database.

At this point the records are in a two-dimensional array. Prior to writing them into table cells, you can use the VBA UBound function to check how many rows and columns were retrieved. The data is placed into table cells by using the For.. .Next loop.

Because some fields in a retrieved recordset may not have any data in them, you can end up with some missing HTML table cells. To avoid blank spaces in a table, the VBScript code places the HTML <BR> (break) tag in a table cell if the field contains a Null value:

currField = currField & "<BR>"

You can also use a non-breaking space (nbsp;) for this purpose:

currField = currField & "&nbsp;"

The above statement will make the cell border show up when the cell is empty. You can also write the following statement to ensure that there are no gaps in your table:

Response.Write "<TD>" & currField & "&nbsp;</TD>"

In addition, if a field contains a zero-length string (" "), the VBScript procedure places a dot in a table cell, so that you not only keep the structure of the table intact but also differentiate between information that does not exist (zero-length) and information that may exist (Null). Recall that by setting the AllowZeroLength property of a table field to Yes and the Required property to No, you can enter two double quotation marks to indicate that the information does not exist. Leaving the field blank by not entering any data in it indicates that the information may exist, but it is not known at the time of entry.

Taking Your VBA Programming Skills to the Web

5. Open the Northwind database located in the Learn_ASP folder and open the Shippers table in Design view. Click in the Phone field and change the Required property of this field to No and the Allow Zero Length property to Yes.

6. Save the Shippers table and open it in Datasheet view. Add Airborne Express as a new shipping company. Leave the Phone field for Airborne Express empty. Add DHL as a new shipping company. Enter two double quotation marks and a space (" ") in the Phone field for DHL. Upon saving the record, the quotation marks will disappear.

7. Close the Shippers table and exit Microsoft Access.

8. Return to your browser and press F5 to refresh the window or click the Go button to update the display.

Notice that the Phone cell is empty for Airborne Express and there is a dot in the Phone cell for DHL.