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.

1. Start Windows Notepad and enter the ASP code as shown below.

<TITLE>Retrieving a Recordset</TITLE> </HEAD>

Set conn = Server.CreateObject("ADODB.Connection") conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &_

"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:

Response.Write rst.Fields("CompanyName") Response.Write rst.Fields("CompanyName").Value Response.Write rst.Fields(1) Response.Write rst.Fields(1).Value Response.Write rst(1) Response.Write rst("CompanyName")

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.

3. Open your web browser and type http://localhost/NorthDB/ GetCustomers.asp in the address bar.

4. Press Enter or click Go. When you request the GetCustomers.asp file in the browser, you get the results shown in Figure 28-20.

3 Retrieving a Recordset - Mterosoft Internet explorer

Favorites Tocfc Help xj

Address j http://localhost/NorthDB/GetCustomer5. asp o Links w

Alfreds Futterkiste

Ana Trujillo Emparedados y helados

Antonio Moreno Taquería

Around the Horn

Berglunds snabbkßp

Blauer See Delikatessen

Blondelpére etfäs

Bólido Comidas preparadas

Bon app'

Bottom-Dollar Markets B's Beverages Cactus Comidas para llevar Centro comercial Moctezuma Chop-suey Chinese Comércio Mineiro Consolidated Holdings Drachenblut Delikatessen

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.

©Hands-On 28-8: Creating a Multi-Page ASP File

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

1. Start Windows Notepad and enter the ASP code as shown below.

<TITLE>View Few at a Time</TITLE> </HEAD>

Dim conn, rst, mySQL, currPage, rows, counter Set conn = Server.CreateObject("ADODB.Connection") conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &_

"c:\Learn_ASP\Northwind.mdb" Set rst = Server.CreateObject("ADODB.Recordset")

rst.CursorType = 3 'adOpenStatic rst.PageSize = 12

mySQL= "SELECT * FROM Customers ORDER BY CompanyName" rst.Open mySQL, conn

If Request.QueryString("CurrPage")="" Then currPage=1

Else currPage=Request.QueryString("currPage") End If rst.AbsolutePage=currPage rows = 0

Response.Write "<H2>Northwind Customers</H2>" Response.Write "<I>Displaying page" & currPage & " of " Response.Write rst.PageCount & "</I>" Response.Write "<HR>"

Part V

Do While Not rst.EOF And rows < rst.PageSize

Response.Write "<A HREF=""Address.asp?CustomerId=" & _

Response.Write rst("CompanyName") & "</A><BR>" rows = rows + 1 rst.MoveNext Loop

Response.Write "<HR>" Response.Write "<B>Result Pages: </B>"

For counter = 1 To rst.PageCount

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

Response.Write counter & "</A>" Response.Write Chr(32)

Next rst.close

Set rst = Nothing conn.Close

Set conn = Nothing

2. Save the file as PageMe.asp in the Learn_ASP folder.

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:

Response.Write "<I>Displaying page" & currPage & " of " Response.Write rst.PageCount & "</I>"

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:

Response.Write "<A HREF=""Address.asp?CustomerId=" & rst("CustomerId") & >"

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

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:

Response.Write "<A HREF='Address.asp?CustomerId='" & rst("CustomerId") & ">" Response.Write rst("CompanyName") & "</A><BR>"

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.

3. Open your browser and type http://localhost/NorthDB/PageMe.asp in the address bar.

4. Press Enter or click Go. You should see the listing of Northwind customers spanning multiple pages (Figure 28-21).

5. Navigate to different pages by clicking on a page link.

^^ Note: Clicking on the company name does not work yet. You must create another ASP page to display the selected customer's address (see Hands-On 28-9).

Taking Your VBA Programming Skills to the Web

3 View Few at A Time

BBS

File Edit View Favorites Toots Help

o^ - ©• a a<&|/>

Search T ^ Favorites

Address ¿0?} httpif/focahost/NorthDB/PageMe.asp

v H Go

Northwind Customers

Displaying page 1 of 8

Alfreds Futterkiste

Ana TrniiUo Emparedados v helados

Antonio Moreno Taquería

Around the Horn

Ber«lunds snabbkop

Blauer See Delikatessen

Blondel pére et fils

Bólido Comidas preparadas

Bon app'

Bottom-Dollar Markets

B's Beverages

Cactus Comidas para llevar

H Result Pages: 1 2 34 5 678

^J Local intranet

Figure 28-21: The result of running the ASP page titled PageMe.asp is a list of Northwind customers that is both easy to examine and use.

Figure 28-21: The result of running the ASP page titled PageMe.asp is a list of Northwind customers that is both easy to examine and use.

© Hands-On 28-9: Creating an ASP File for Loading from a Hyperlink

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.

1. Start Windows Notepad and enter the ASP code as shown below.

<HEAD><TITLE>Lookup Results</TITLE></HEAD>

Dim mySQL, myPath

CustomerId = TRIM(Request.QueryStnng("CustomerId"))

myPath = "C:\Learn_ASP\Northwind.mdb"

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath

Set rst = Server.CreateObject("ADODB.Recordset")

rst.CursorType = 3 'adOpenStatic mySQL= "SELECT * FROM Customers WHERE CustomerId='" & CustomerId &

rst.Open mySQL,conn

<H1>Address Lookup</H1>

<I>Displaying address for <B><%=rst("CompanyName")%></B></I> <HR>

<TABLE colspan=2 align="Center"> <TR>

<TD><input type="text" name="CustomerId"

value="<%=rst("CustomerId")%>" size="5"> </TD>

Part V

<TD><input type=MtextM name="Address"

value="<%=rst("Address")%>" size="60"> </TD> </TR> <TR>

<TD><input type=MtextM name="City"

value="<%=rst("City")%>" size=M15M> </TD> </TR> <TR>

<TD><input type=MtextM name="Region"

value="<%=rst("Region")%>" size=M15M> </TD> </TR> <TR>

<TD><input type="text" name="Country"

value="<%=rst("Country")%>" size="15"> </TD> </TR> <TR>

<TD><input type="text" name="PostalCode"

value="<%=rst("PostalCode")%>" size="10"> </TD> </TR> <TR>

<TD><input type="text" name="Phone"

value="<%=rst("Phone")%>" size="24"> </TD><BR> </TR> <TR>

<TD><input type="text" name="Fax"

value="<%=rst("Fax")%>" size="24"> </TD><BR> </TR> </TABLE> <BR> <BR>

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

rst.close

Set rst = Nothing conn.Close

Set conn = Nothing

Taking Your VBA Programming Skills to the Web

2. Save the file as Address.asp in the 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.

3. Open your browser and type http://localhost/NorthDB/PageMe.asp in the address bar.

4. Press Enter or click Go. You should see the listing of Northwind customers spanning multiple pages.

5. Click a company name of your choice to view its address information. When you click a company name in the browser, the Address Lookup screen appears as illustrated in Figure 28-22.

Part V

I 3 Lookup Results

- Microsoft Interne! Explorer ; i

Fife Edt View

Favorites Tcxtfs Help

4-* Back » ^ "

J E t3 1 â-ï^tf' G*J Favorite! ^rieila >3 fe-sâ^ - H ¿J>

Address http://localhost/NorthDB/Addres5.osp?Custo«merId-ALFK[ ¿¿Go Links w

Address Lookup

Delaying address jor Alfreds Futterkiste

Customer Id: |ALFKI

Street

|Obene Sir. 67

City:

iBerlin

Region

1

Country

Germany

Zip;

|12Z03

Phone:

|i 3 0-0074321

Fax

|03Û-OQ7SB^5

[ Go Bade 1

J

m

Locsi ritranet

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.

1. Start Windows Notepad and enter the ASP code as shown below.

<HEAD><TITLE>Fast Retrieve</TITLE></HEAD>

Dim conn, rst, strSQL, myPath, fld, allShippers, RowCounter, ColCounter Dim NumOfCols, NumOfRows, currField

Taking Your VBA Programming Skills to the Web strSQL = "SELECT * FROM Shippers ORDER BY ShipperId"

myPath = "C:\Learn_ASP\Northwind.mdb"

Set conn = Server.CreateObject("ADODB.Connection")

conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath

Set rst = conn.Execute(strSQL)

Response.Write "<TABLE Border=1><TR>" & VbCrLf For Each fld In rst.Fields

Response.Write "<TD><B>" & fld.name & "</B></TD>" & VbCrLf

Next

Response.Write "</TR>" & VbCrLf allShippers = rst.GetRows rst.Close

Set rst = Nothing conn.Close

Set conn = Nothing

NumOfCols = UBound(allShippers, 1) 'columns returned NumOfRows = UBound(allShippers, 2) 'rows returned For RowCounter = 0 To NumofRows Response.Write "<TR>"& VbCrLf For ColCounter = 0 To NumOfCols currField = allShippers(ColCounter, RowCounter) If IsNull(currField) Then currField = currField & "<BR>" ElseIf currField= "" Then currField="." End If

Response.Write "<TD Valign=Top>" Response.Write currField Response.Write "</TD>" & VbCrLf

Next

Response.Write "<TR>" & VbCrLf

Next

Response.Write "</TABLE>"

2. Save the file as FastRetrieve.asp in the 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.

3. Open your browser and type http://localhost/NorthDB/FastRetrieve.asp

in the address bar.

4. Press Enter or click Go. You should see the listing of three shipping companies placed in a table (Figure 28-23).

File Edit view Feworltes Tools Help

Figure 28-23: The FastRetrieve ASP page fetches records from the Shippers table using the fast GetRows method.

File Edit view Feworltes Tools Help fl Speedy Egrets |(503) 555-9S31

[2 United Package |(5C3) 555-3199

fl Speedy Egrets |(503) 555-9S31

[2 United Package |(5C3) 555-3199

3 Federal Shipping ¡(503) 555-5931

.¿JOcrie loçel Intranet

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.

0 0

Post a comment