| CIS 209 | Web Page Scripting Languages |
In this chapter we will use Access and its sample Northwind database to learn about and practice advanced Web database integration.
To complete the lessons in this chapter you must use Microsoft Access’s sample database, Northwind. The database is automatically installed when you installed Access, if you chose to install samples. Click on START, SEARCH, FILES OR FOLDERS, and type northwind.mdb to find the file on your computer. If the database is not installed on your machine you can download the files from the Microsoft Web site.
http://office.microsoft.com/Downloads/9798/Nwind.aspx
The Northwind database comes with eight tables and fifteen predefined queries. The following table lists the eight tables in the Northwind database, and a description of their contents.
|
Table Name |
Description |
|
Categories |
Contains a categoryID, name, description, and picture |
|
Customers |
Contains the customerID, and customer contact information |
|
Employees |
Contains the employeeID, position information, and employee contact information |
|
Order Details |
Contains the orderID and productID, the order quantity, and the price information |
|
Orders |
Contains the orderID, customerID, employeeID, shipping method, and delivery information |
|
Products |
Contains the productID, name, category, price and inventory information |
|
Shippers |
Contains the shippingID and the shipping company information |
|
Suppliers |
Contains the ID of the supplier, company name, and company contact information |
If you follow the contents of each of the tables above you can start to define some relationships between the tables. Each table will contain a primary key, a field that has to be unique for each entry in the table.
This server-side include file will be used to hold the connection string. It will also create the connection and recordset objects. This connection server-side include page can be reused for all of the queries that will be created in this chapter.
<% dim DRV DBQ CS oC oRS
DRV = “Driver={Microsoft Access Driver (*.mdb)}; “
DBQ = “DBQ= c:\yourfolder\nwind2000.mdb;”
CS = DRV & DBQ
Set oC = server.createobject(“ADODB.connection”)
Set oRS = server.createobject(“ADODB.recordset”)
Set oCM = = server.createobject(“ADODB.command”)
%>
Remember that the file must be saved with a .inc extension.
You can build your own SQL statements if you know SQL or you can use the wizards in Microsoft Access to build your SQL statements. In Microsoft Access, you define the search criteria and the tables searched and it will create the SQL for you. The process is started by clicking the Queries tab to open the list of predefined queries. From there you can select a predefined query or select the New option and define your own. The pictures below show the result of selecting the New icon and setting up a custom query. The first image is the Design View, the second image is the Datasheet view, and the final image is the SQL statement view. When you are in Access you can switch between the different views using the File Menu View pull-down menu.
By cutting and pasting the select statement in the third picture above you can embed the SQL in your ASP pages. Since the statements are usually too big to fit on one line the line can easily be broken up as in the example below.
<% dim SQL
SQL = “SELECT Products.ProductID, Products.ProductName, Products.UnitPrice, “
SQL = SQL & “Products.UnitsInStock, Products.UnitsInStock “
SQL = SQL & “FROM Products”
SQL = SQL & “WHERE (((Products.CategoryID)=8))”
SQL = SQL & “ORDER BY Products.UnitPrice DESC; “
%>
The following code example uses the server-side include file that sets up the connection and the SQL statement above to define the values for an HTML table that is eventually displayed.
<! --#include file="nwconx.inc"-->
<html><head><title>SQL Products Page</title></head>
<body>
<h1>SQL Example</h1>
<% dim SQL
SQL = "SELECT Products.ProductID, Products.ProductName, Products.UnitPrice, "
SQL = SQL & "Products.UnitsInStock, Products.UnitsInStock "
SQL = SQL & "FROM Products”
SQL = SQL & "WHERE (((Products.CategoryID)=8))”
SQL = SQL & "ORDER BY Products.UnitPrice DESC; "
oRS.Open SQL, CS
%>
<table border=1 cellspacing=0 cellpadding=5>
<% do until oRS.EOF %>
<% = "<tr><td>" %>
<% = oRS("ProductID") & "</td><td align=left>" %>
<% = oRS("ProductName") & "</td><td align=right>" %>
<% = formatcurrency(oRS("UnitPrice")) & "</td><td align=left> " %>
<% = oRS("UnitsinStock") & "</td><td align=left>" %>
<% = oRS("Discontinued") & "</td></tr>" %>
<% oRS.movenext
loop
oRS.close
set oRS=nothing
%>
</table>
</body></html>
By using the keywords AND and OR you can combine multiple search criteria within the same SQL statement. The example SQL above could have selected a more specific result using AND or OR.
SELECT Products.ProductID, Products.ProductName, Products.UnitPrice,
Products.UnitsInStock, Products.UnitsInStock
FROM Products
WHERE (((Products.CategoryID)=8)) AND ((Products.UnitsInStock)>100))
ORDER BY Products.UnitPrice DESC;
The code above defined a more advanced search by stating the criteria must have a product category of 8 AND the units in stock must be greater than 100. This change in the SQL statement reduced the results to three products.
The Access Design Window has one small change to add this SQL statement to the query, UnitsInStock has a criteria of >100 added.
Updating a database can consist of creating a new record, modifying an existing record, or deleting a record. You can accomplish this by using SQL commands, or by using the recordset object built into ADO.
In SQL, inserting a new record requires creating an INSERT query. The syntax for inserting a new record using SQL is as follows.
INSERT INTO tablename
VALUES (value1, value2, value2,…..valueN)
If you do not have a value for one of the fields, you can use substitute the word NULL for the value. Remember the primary key values cannot be duplicated in the table.
The command object can be used with the connection object or the recordset object to use SQL in a Web page to create a new record. The table below lists a subset of the methods and properties of the command object.
|
Methods and Properties |
Description |
Example |
|
Execute Method |
Executes a SQL statement or stored procedure identified by the CommandText property |
oCM.Execute |
|
Cancel Method |
Cancels the current command |
oCM.Cancel |
|
ActiveConnection property |
Assigns the connection object (oC) or connection string (CS) |
oCM.ActiveConnection = CS |
|
CommandText |
Specifies the SQL statement (SQL) or stored procedure |
oC.CommandText = SQL |
|
CommandType |
Describes the Command Text |
oCM.CommandType = adCmdText |
The following example creates a new record using SQL and the command object.
<!-- #include file = "nwconx.inc"-->
<!-- #include file = "adovbs.inc"-->
<html><head><title>The Command Object</title></head>
<body>
<h1>Manipulating the Records</h1>
<%
SQL = "INSERT INTO Shippers "
SQL = SQL & "(shipperID, CompanyName, Phone) "
SQL = SQL & "VALUES (42, 'FedNight', '(555) 800-8888')"
oC.open CS
oCM.ActiveConnetion = oC
oCM.CommandText = SQL
oCM.CommandType = adCmdText
oCM.Execute
Response.write SQL
%>
</body></html>
|
Syntax of Code |
Explanation |
|
<!-- #include file = "nwconx.inc"--> |
This includes the server-side include file that holds the connection string |
|
<!-- #include file = "adovbs.inc"--> |
This include file holds the values of the ActiveConnection, CommandText, and the CommandType properties. These must be declared before the execute can be called. |
|
SQL = "INSERT INTO Shippers " |
This is setting up the SQL statement used to INSERT a new record. |
|
oC.open CS |
This opens a connection, using the connection object. |
|
oCM.ActiveConnetion = oC |
These lines set the properties of the connection object. You can set the property to the connection string or the connection object. |
|
oCM.Execute |
After all the connection variables have been declared you can call the execute command that executes the SQL statement. |
The previous example created a new record in the Shippers table using SQL and the command object. You can also update a record, or delete a record, the same way. To update a record you can replace the following code in the example above.
<%
SQL = “UPDATE Shippers “
SQL = SQL & ”SET Companyname = ‘ExpMail’, “
SQL = SQL & “Phone = ‘(222) 800-8888’ “
SQL = SQL & “WHERE ShipperID = 42”
The rest of the ASP Web page can stay the same. If you would like to delete a record, the following sample code can be inserted into the ASP Web page.
SQL = “DELETE FROM Shippers “
SQL = SQL & ”WHERE ShipperID = 42”
Although you can use SQL to create new records, or to update or delete existing records, the ADO Model also contains methods to perform these common functions. These methods do not require the command object.
You can create a new record using the AddNew method of the recordset object.
<!-- #include file = "nwconx.inc"-->
<!-- #include file = "adovbs.inc"-->
<html><head><title>The Command Object</title></head>
<body>
<h1>Manipulating the Records</h1>
<%
oC.open CS
SQL = "SELECT * FROM Shippers"
oRS.Open SQL, oC, adOpenDnyamic, adLockOptimistic, adCmdText
oRS.Filter = "CompanyName = 'OverNight'
If oRS.EOF Then
Ors.AddNew
oRS("CompanyName") = "Overnight"
oRS("Phone") = (222) 555-1234
oRS.Update
End If
oRS.Filter = adFilterNone
Response.write SQL
%>
</body></html>
|
Syntax of Code |
Explanation |
|
SQL = "SELECT * FROM Shippers" |
This creates the SQL statement, using the SELECT command. |
|
oRS.Open SQL, oC, adOpenDnyamic, adLockOptimistic, adCmdText |
This opens the recordset object, using the SQL query and the connection object. The parameters are passed to indicate the CursorType, LockType, and CommandType. |
|
oRS.Filter = "CompanyName = 'OverNight' |
The filter method of the recordset object is used to search for a specific record. |
|
If oRS.EOF Then |
If the record is not found and the end of file is reached then the record is not present and the file should be updated. |
|
Ors.AddNew |
These statements add the new record to the data set. |
|
oRS.Filter = adFilterNone |
This releases the filter. |
The syntax to update a field looks very similar except you don’t want to find the end of file, instead you would use the NOT end of file test to make sure the record being updated is present.
If not oRS.EOF Then
oRS("CompanyName") = "Overnight"
oRS("Phone") = (222) 555-1234
oRS.Update
End If
Deleting a file would use the NOT end of file test and then a simple call to the delete method.
If not oRS.EOF Then
oRS.Delete
oRS.Update
End If
The database cursor is a location in memory where the records from the recordset are stored. The recordset is similar to a Rolodex, from which you can access each individual address card. You can access records row by row within the recordset object, using the cursor. The CursorType, which is specified when you retrieve the recordset, indicates which methods and properties apply to the recordset. The types of cursors that are supported include the Forward Only, Static, Keyset, and Dynamic. The ForwardOnly is the default recordset.
The Static recordset allows you to use navigational methods. The navigational methods of the static recordset include MoveFirst, MoveNext, MoveLast, and MovePrevious. The Move method takes two parameters, the number of records to move through, and the starting position. Negative numbers allow you to move backwards in the recordset. For example,
ORS.MOVE (-2, 3)
This would move back to the first record.
Check out the following EXAMPLE.
Download WebDBv1.1.zip.