Teach Yourself Active Server Pages in 24 Hours
By Christoph Wille and Christian Koller
Published by Sams Publishing
July 1999, 477 Pages, ISBN: 0672316129 |
 |
Related Links:
• Interview
with Christoph Wille, co-author of the book
• In-depth Review
of the book
Chapter 14: Retrieving Data from a Database
The topic of the last hour was data access basics. You learned
about the basics of the ADO object model, and you created an ODBC data source, which was
then used in the Active Server Pages (ASP) example to retrieve data from the Northwind
database.
However, you have the possibility to set options for a database
query to gain better performance or other advantages. These options and their usage are
explained in this hour.
You are introduced to the usage of the graphical query building
tools of Access 97/2000 and SQL Server 7.0.
When a query returns too many rows to be displayed on a single
Web page, you want to implement a method for paging through the recordset so the user can
leaf through the whole recordset. This is also a topic of this hour.
The highlights of this hour include
- Opening a recordset
- Using graphical tools to build a query
- Preparing recordset options
- Retrieving multiple recordsets data
Opening a Recordset
In the last hour's example (Listing 13.1), you created a
Recordset object using the Execute method of the Connection object. However, there are
other possibilities to create and open a recordset. Listing 14.1 shows you how to use the
Open method of the Recordset object to gain a recordset.
Listing 14.1 is an ASP script that performs a SQL statement
against the Northwind database, which ships with Access 97/2000 and SQL Server 7, to
obtain a recordset that includes all customers and the number of the orders of each
customer who has placed more than seven orders. Now, look at the script; some statements
are familiar, and some are new to you. After the listing, the output is shown and the
script is explained line by line.
Listing 14.1 Opening a Recordset Using the
Recordset.Open Method
1: <% @LANGUAGE = VBScript %>
2: <%
3: Option Explicit
4: Response.Expires = 0
5:
6: Dim objConn, objRS, strQ
7: Dim strConnection 8:
9: Set objConn = Server.CreateObject("ADODB.Connection")
10: strConnection = "Data Source=Northwind;"
11: strConnection = strConnection & "User ID=sa;Password=;"
12: objConn.Open strConnection
13:
14: Set objRS = Server.CreateObject("ADODB.Recordset")
15: Set objRS.ActiveConnection = objConn
16: strQ = "SELECT Customers.CompanyName, "
17: strQ = strQ & "COUNT(Orders.OrderID) AS NumOrders "
18: strQ = strQ & "FROM Customers INNER JOIN Orders ON "
19: strQ = strQ & "Customers.CustomerID = Orders.CustomerID "
20: strQ = strQ & "GROUP BY Customers.CompanyName "
21: strQ = strQ & "HAVING COUNT(Orders.OrderID) > 7 "
22: strQ = strQ & "ORDER BY COUNT(Orders.OrderID) "
23: objRS.Open strQ
24:
25: %>
26: <HTML>
27: <BODY>
28: The Company name of all Customers that have ordered more
29: than seven times, together with the number of their orders
30: are listed. The output is ordered ascending by the number
31: or their orders.
32: <BR><BR>
33: <%
34: While Not objRS.EOF
35: Response.Write objRS("CompanyName") & ": "
36: Response.Write objRS("NumOrders") & "
Orders<BR>"
37: objRS.MoveNext
38: Wend
39:
40: objRS.close
41: objConn.close
42: Set objRS = Nothing
43: Set objConn = Nothing
44: %>
45: </BODY>
46: </HTML>
The code in Listing 14.1 should produce the output in Figure
14.1.
OUTPUT
Figure
14.1
The output of Listing 14.1 is presented in the Internet Explorer 5.
ANALYSIS To enable the script in Listing 14.1 to execute
successfully on a server, the server must have set up an ODBC system data source named
Northwind, as described in Hour 13, "Data Access Basics," in the section
"Creating and Configuring an ODBC Data Source." The ODBC data source Northwind
connects the script to the Northwind database using the ODBC driver for OLE DB.
Lines 1-4 should need no explanation because they have been
explained in all previous listing descriptions.
The variables objConn, objRS, strQ, and strConnection are
declared in lines 6 and 7. ObjConn is used to store the reference to a newly created
Connection object, and objRS bears the reference to a new Recordset object. strQ contains
the query string (SQL statement) that is executed against the database, and strConnection
provides the connection string for opening a database connection.
The Connection object is created in line 9, and the connection
string is defined in lines 10-11. Line 12 opens the connection to the Northwind database
using the Open method of the Connection object. Lines 9-12 are as follows:
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Data Source=Northwind;"
strConnection = strConnection & "User ID=sa;Password=;"
objConn.Open strConnection
Then the Recordset object is created in line 14, which is shown
in the following:
Set objRS = Server.CreateObject("ADODB.Recordset")
This is normally done using the Server.CreateObject method with
the parameter ADODB.Recordset, which creates an instance of the server's ADO Recordset
object. The reference to this object is stored in the variable objRS using the Set
statement.
Until now the script has created a Connection object and a
Recordset object. However, to enable the Recordset object to use the connection defined in
the Connection object, the script must assign the connection to the Recordset object. Line
15 performs this assignment, whereby the Connection object is assigned to the
ActiveConnection property of the Recordset object objRS.
Set objRS.ActiveConnection = objConn
Because of the statement in line 15, the recordset is using the
connection stored in objConn for every action that needs a connection to a database, such
as opening a recordset, updating, inserting, or deleting recordset rows.
Lines 16-22 prepare the SQL statement for the database query and
store it in the variable strQ. The final SQL statement is presented in Listing 14.2.
Listing 14.2 The SQL Statement Used in Listing
14.1
SELECT Customers.CompanyName, COUNT(Orders.OrderID) AS NumOrders
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName
HAVING COUNT(Orders.OrderID) > 7
ORDER BY COUNT(Orders.OrderID)
The SQL statement selects the CompanyName from the Customers
table and the number of OrderIDs (named NumOrders) from the Orders table.
The OrderIDs are assigned to each company through the INNER JOIN
of the Customers and Orders tables. The join criterion is that the CustomerIDs in both
tables are identical for each resulting record row. The core statement of Listing 14.2
reads as follows:
SELECT Customers.CompanyName, Orders.OrderID AS NumOrders
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
This statement returns all record rows from the join of the
Customers and Orders tables. However, if you want to count the number of OrderIDs assigned
to a customer, you must use a so-called aggregate function. In Listing 14.2, the script is
using the COUNT function, which counts the number of all OrderIDs that are assigned to one
customer. To do so, the SQL statement must group all record rows with the same value in
the column Customers.CompanyName. Then the aggregate function COUNT(Orders.OrderID)
returns the number of OrderIDs that are assigned to one customer.
To limit the resulting record to row in which the number of
orders for one customer is higher than 7, you must use the HAVING clause. The HAVING
clause is used to set conditions for the GROUP BY clause. You cannot limit the resulting
record rows using a WHERE clause because the WHERE clause does in fact restrict record
rows before the GROUP BY clause is executed. However, HAVING sets conditions for the GROUP
BY clause and related aggregate functions similar to the way in which the WHERE clause
sets conditions for SELECT.
If this explanation sounds complicated to you, you might not be
alone. Fortunately, you can use graphical tools as provided in Access 97/2000 or SQL
Server 7 to build such a SQL statement easily. This is described in detail in the next
section of this hour, "Creating a SQL Statement."
The recordset itself is opened in line 23, as shown in the
following:
objRS.Open strQ
The script uses the Open method of the Recordset object in
conjunction with the SQL statement, which is stored in strQ, as the first parameter. (The
Open method can have up to four parameters; see the Online documentation of IIS.)
Lines 26-32 of Listing 14.1 write some HTML tags and text to the
client.
In lines 34-38, a While loop steps through the rows of the
recordset and writes the values of the field CompanyName and the field NumOrders, which
represents the number of orders made by the company, of each row of the recordset to the
client.
While Not objRS.EOF
Response.Write objRS("CompanyName") & ": "
Response.Write objRS("NumOrders") & " Orders<BR>"
objRS.MoveNext
Wend
After all rows are written to the client and the While loop has
terminated, the recordset stored in objRS and the connection stored in objConn are closed
and set to Nothing to free their resources on the server immediately.
Finally, lines 45 and 46 write the closing HTML tags to the
client.
The general idea of Listing 14.1 is that you first create and
open a connection
Set Connection =
Server.CreateObject("ADODB.Connection")
Connection.Open ConnectionString
then create a recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
and assign the connection to the recordset using a statement like
Set Recordset.ActiveConnection = Connection
Then you open the recordset with a statement like
Recordset.Open SQL-Statement
and retrieve the values of the record rows.
You might ask yourself why I am introducing a new way to open a
recordset when you could use the Execute method of the Connection object (like in Listing
13.1) to create and open a recordset with one line of code like in the following
statement:
Set Recordset = Connection.Execute(SQL-Statement)
The reason is that you must create a Recordset object and assign
a Connection object to it as active connection, if you want to set additional Recordset
options to gain more control about how the recordset is opened, maintained, and updated.
This is especially important if you want to gain maximum speed for a high-load Web server.
You can read more about this topic in this hour's section "Preparing Recordset
Options."
Creating a SQL Statement
Maybe you are not familiar with all the SQL statements, or you
want to use a graphical tool to create a SQL statement instead of writing it as text, so
this is of special interest for you. Both Microsoft Access 97/2000 and SQL Server 7 have a
graphical tool for creating queries and their relating SQL statements. First, I will
describe how to use the graphical query-building tool of Access 2000 to build the SQL
statement from Listing 14.2, which reads as follows:
SELECT Customers.CompanyName, COUNT(Orders.OrderID) AS NumOrders
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName
HAVING COUNT(Orders.OrderID) > 7
ORDER BY COUNT(Orders.OrderID)
The following To Do list implements the Northwind sample
database, which ships with Access 97/2000. Install the Northwind database if you have not
done so before you try to perform the following steps.
To Do: Build a SQL Statement Using the Graphical Query
Tool of Access 97/2000
TODO
- Open the Access Northwind database, which resides in the directory X:\Program
Files\Microsoft Office\Office\Samples.
- Change to the Queries tab.
- Click the New button to create a new query.
- Click OK in the New Query window to create the new query in Design View.
- Add the tables Customers and Orders to the query: Choose them in the Show Table window
and click the Add button.
- Close the Show Table window. Now the query window should look as shown in Figure 14.2.
Figure 14.2
The Query window with the Customers and Orders tables.
- The join between the two tables is shown with a line between the fields of the tables
that join the tables together. (Optional: When creating another query, you could choose
another join if necessary. Delete the join line with a click on it to mark it and press
the Delete key. Then to establish the new join, drag and drop a field from a table onto a
field of another table. Now the two tables are joined through the two chosen fields.)
- Drag and drop the field CompanyName from the Customers table in the upper frame into the
Field row of the first column in the lower frame.
- Then drag and drop the field OrderID from the Orders table into the Field row of the
second column of the lower frame. Now the Query window looks like in Figure 14.3.
Figure 14.3
The Query window with the Fields CompanyName and OrderID.
- Right-click in a column in the lower frame of the Query window and select Totals from
the context menu. This enables the GROUP BY clause and allows you to use aggregate
functions. You can see the entries Group By in the Total row in the lower frame in both
field columns.
- To use the aggregate function COUNT on the column OrderID, you must change the Total
value of the OrderID field. Click the Group By text in the second column, and you can
assign an aggregate function to the field OrderID. Choose the COUNT function. Now the
Query window looks as shown in Figure 14.4
Figure 14.4
The Query window with the Total value of the field OrderID set to Count.
- Add the criteria that COUNT(OrderID) must be higher than 7. Do this by clicking the
Criteria field of the OrderID column in the lower frame. Type >7 in the Criteria field.
- To let the query sort the resulting rows ascending by the values of the OrderID field
(or rather the values of the COUNT(OrderID) function), choose Ascending in the Sort row of
the OrderID column. Now the Query window with the final query looks as shown in Figure
14.5
Figure 14.5
The Query window with the final query.
- The SQL statement of the final query is shown when you right-click in the upper frame of
the Query window[md]but not on one of the tables[md]and click SQL View from the context
menu.
- Now you see the final SQL statement (see Figure 14.6), which can be used in your ASP
script with a little modification: The semicolon at the end of the statement has to be
removed.
Figure 14.6
The Query window in the SQL view with the SQL statement.
- If you want, you can look at the result of the query by choosing View, Datasheet View
from the menu bar.
- You can save the query by choosing File, Save from the menu bar.
If you have SQL Server 7, you can use a more sophisticated
graphical tool to build SQL queries, such as the SQL statement from Listing 14.2.
The following systematic list uses the SQL Server 7 Northwind
sample database, so ensure that it is installed.
To Do: Build a SQL statement Using the Graphical Query
Tool of SQL Server 7
TODO
- Open the Enterprise Manager of SQL Server 7 (Start button, Programs, Microsoft SQL
Server).
- Expand the server group and the server, which contains the database from which you want
to derive the SQL query. Your Enterprise Manager will look similar to Figure 14.7.
Figure 14.7
The Enterprise Manager of SQL Server 7 with an expanded SQL Server group and SQL
Server (KIRROYAL).
- Right-click the Data Transformation Services folder, and click New Package. The DTS
Package window appears.
- Choose Data, Microsoft OLE DB Provider for SQL Server from the menu. Create a connection
to the SQL Server itself, which is used for the new query to connect.
- In the Connection Properties window, specify the server name of your SQL Server, the
correct authentication mode (with username and password), and the Northwind database as
Database (see Figure 14.8). Then click OK.
Figure 14.8
The Connection Properties window.
- To create the actual SQL query, select Task, Execute SQL Task from the menu of the DTS
package window. The Execute SQL Properties window pops up (see Figure 14.9).
Figure 14.9
The Execute SQL Properties window.
- Click the Build Query button in the Execute SQL Properties window. The Data
Transformation Services Query Designer window appears. This is the window in which you can
build your SQL statement using a graphical tool.
- Drag and drop the tables [Northwind].[dbo].[Customers] and [Northwind].[dbo].[Orders]
from the left frame into the upper-right frame or right-click in the upper-left frame,
select Add Table, add the Customers and Orders tables, and close the Add Table window.
- Right-click in the upper-left window and select Show Panes, Grid to add the Grid Pane to
the Data Transformation Services Query Designer window.
- Now you face four frames (see Figure 14.10). The left frame is the Table/View list, the
upper-right frame is the Diagram pane, the middle-right frame is the Grid pane, and the
lower-right frame is the SQL pane.
Figure 14.10
The Data Transformation Services Query Designer window with Table/View list, Diagram
pane, Grid pane, and SQL pane.
- Hide the Table/View list by clicking the far left button from the toolbar of the Data
Transformation Services Query Designer window.
- Drag the CompanyName field from the Customers table in the Diagram pane (upper-right
frame) and drop it on the first row of the Column column in the Grid pane (middle right
frame).
- Drag the OrderID field from the Orders table in the Diagram pane and drop it on the
second row of the Column column in the Grid pane.
- Add the Alias NumOrders to the OrderID row in the Grid pane.
- Right-click in the Grid pane and select Group By from the menu.
- Click the Group By column in the OrderID row in the Grid pane and change its value to
Count.
- Click the Sort Type column in the OrderID row and select Ascending.
- Add the criteria that COUNT(OrderID) must be higher than 7. Do this by clicking the
Criteria field of the OrderID row. Type >7 in the Criteria field. Now you have built
the same SQL query as the SQL statement used in Listing 14.1. The SQL statement is shown
as text in the SQL pane (lower-right frame). The Data Transformation Services Query
Designer window appears as shown in Figure 14.11
Figure 14.11
The Data Transformation Services Query Designer window with a final SQL statement.
- You can copy and paste the SQL statement and use it for an ASP script.
- Click the OK button in the Data Transformation Services Query Designer window. The
Execute SQL Properties window appears and allows you to finish building your query by
clicking OK.
- Close the DTS Designer window.
There are other graphical tools for creating a SQL statement. If
you have Visual InterDev 6.0, you can use it like the DTS Query Designer in SQL Server
7.0.
Preparing Recordset Options
As I mentioned at the end of this hour's section "Opening a
Recordset," you can set some options to gain better control of the speed and behavior
of the Recordset object. In fact, you can set values to the following properties of a
Recordset object before opening it. The properties listed in Table 14.1 have been selected
because they are used to define the options of a recordset and therefore its
possibilities.
Table 14.1 Selected Properties of the Recordset
Object
 |
|
Property |
Description |
|
|
CursorLocation |
Specifies the location of the cursor
engine that will be used to manage the cursor of the Recordset object. The CursorLocation
property can have two values. The value adUseServer tells the recordset to use the cursor
that is supplied by the data provider or driver. AdUseClient lets the Recordset object use
a client-side cursor that is supplied by a local cursor library.
(In Figure 13.1, which shows Universal data access architecture,
you can see the client-side cursor engine used as service by OLE DB. However, the
server-side cursor is located in the database itself.) Each cursor location has its
advantages and disadvantages. To do batch updates on a recordset, for example, you must
set CursorLocation to adUseClient.
|
|
CursorType |
Specifies the type of cursor that is
used. There are four types of cursors: forward-only, static, dynamic, and keyset cursor. A
forward-only cursor enables you to step through a recordset in the forward direction only.
This saves resources and speeds up performance but does not allow you to use the PageCount
or RecordCount properties to determine the number of rows or pages in the recordset. The
static cursor allows movement in both a forward and backward direction. You can use the
PageCount and RecordCount properties, too. Both forward only and static cursors only
provide a "snapshot" or static copy of a recordset. Changes made by other users
are not reflected. However, this does not matter when you use recordset data for a Web
page because you cannot update data on a Web page after it has been sent to the client.
Therefore, I recommend using forward-only or static cursors only. You can assign a cursor
type to a recordset by setting the corresponding value to its CursorType property:
adOpenForwardOnly, adOpenStatic, adOpenDynamic and adOpenKeyset.
|
|
LockType |
Indicates the type of lock that is
used on records when editing a recordset. When a record is locked, it cannot be altered by
another user. AdLockReadOnly does not allow you to alter any data at all.
AdLockPessimistic tells the provider to lock records at the data source immediately upon
editing. AdLockOptimistic only locks records when you call the Update method.
AdLockBatchOptimistic is required for batch update mode. Some providers do not support all
LockTypes!
|
|
Retrieving Multiple Recordsets
Sometimes a query returns more than one recordset. Imagine that a
script is using the following SQL statement for retrieving data:
SELECT MAX(UnitPrice) FROM Products
SELECT COUNT(*) FROM Orders
Access does not support multiple recordsets.
Therefore, you cannot use the preceding SQL statement with an Access database.
Listing 14.3 can be executed only when the ODBC data source
Northwind is referring to the SQL Server Northwind database. Therefore the script itself
checks whether the data source is an SQL Server database.
Listing 14.3 Example for Retrieving Data from
Multiple Recordsets
1: <% @LANGUAGE = VBScript %>
2: <%
3: Option Explicit
4: Response.Expires = 0
5:
6: Dim objConn, objRS, strQ
7: Dim strConnection
8:
9: Set objConn = Server.CreateObject("ADODB.Connection")
10: strConnection = "Data Source=Northwind;"
11: strConnection = strConnection & "User ID=sa;Password=;"
12: objConn.Open strConnection
13:
14: Set objRS = Server.CreateObject("ADODB.Recordset")
15: Set objRS.ActiveConnection = objConn
16:
17: If objConn.Properties("DBMS Name") <> "Microsoft SQL Server"
Then
18: Response.Write "This script can only be used on an "
19: Response.Write "SQL server database."
20: Response.End
21: End if
22:
23: strQ = "SELECT MAX(UnitPrice) AS "
24: strQ = strQ & "'Maximum Unit Price' FROM Products;"
25: strQ = strQ & "SELECT COUNT(*) AS 'Number of Orders' "
26: strQ = strQ & "FROM Orders"
27:
28: objRS.Open strQ
29:
30: %>
31: <HTML>
32: <BODY>
33: Here are the maximum unit price of a product
34: and the number of orders in the Northwind database.
35: <BR><BR>
36: <%
37: Do Until objRS Is Nothing
38: Do While Not objRS.EOF
39: Response.Write objRS(0).Name & ": " &
objRs(0) & "<BR>"
40: objRS.MoveNext
41: Loop
42: Set objRS = objRS.NextRecordset
43: Loop
44:
45: objConn.close
46: Set objConn = Nothing
47: %>
48: </BODY>
49: </HTML>
ANALYSIS Lines 1-15 are equal to lines 1-15 in Listing 14.1,
therefore they aren't explained again.
In lines 17-21, the script checks whether the connection stored
in objConn is a SQL Server connection by verifying that the DBMS Name property of the
connection is Microsoft SQL Server. If a server other than SQL Server is the data source
for the connection, a message is written in lines 18 and 19 and the script's execution is
stopped in line 20.
The SQL statement is stored in the variable strQ in lines 23-26
and executed as the recordset is opened in line 28.
Lines 31-35 write some HTML tags and text to the browser.
Lines 37-42 contain two nested Do loops. The outer loop is
executed until the recordset in objRS is Nothing, which indicates that no more recordsets
are referenced by objRS. The inner loop (lines 38-41) steps through all rows of the
current recordset and writes the name and value of the first field, which has the field
index zero, to the client. When the inner loop has reached the end of the current
recordset, it is terminated and the next recordset's reference is stored in the variable
objRS using the following statement:
Set objRS = objRS.NextRecordset
When the current recordset becomes the last recordset, the method
NextRecordset returns a reference to Nothing, which lets the outer Do loop stop execution.
Summary
In this hour, you learned about using the Open method of the
Recordset object to open a recordset. A short introduction into building SQL queries using
graphical tools is also a topic of this hour.
You also learned how to retrieve multiple recordsets from a query
against SQL Server 7.
In the next hour, you will learn about how to manipulate data
from a database. You will learn how to add, update, and delete records and also how to
handle large text or binary data fields.
Q&A
Q Where can I get more information about aggregate
functions?
A You can get more information about aggregate function from the help in
Microsoft Access or SQL Server.
Q Can I write a script that accesses a database without
any SQL statements?
A Yes, this is possible. For this purpose, ADO provides the AddNew,
Delete, Update and UpdateBatch methods of the Recordset object. More about this topic is
discussed in Hour 15, "Manipulating Data of a Database."
Workshop
The quiz questions and exercises are provided for your further
understanding. See Appendix A, "Answers," for the answers.
Quiz
- Which two cursor types are important to Web programmers?
- Name two methods for opening a Recordset object.
- Which two methods can you use to create a Recordset object?
Exercise
Change Listing 14.1 in a way that the recordset is opened with a
forward-only cursor and the cursor location is set to Client.
© 1999 Sams Publishing. This sample chapter is reprinted with
permission of the publisher. |
;){kind=link}
;){kind=link}
;){kind=link}
;){kind=link}
;){kind=link}
;){kind=link}
;){kind=link}
;){kind=link}
;){kind=link}
;){kind=link}