| CIS 209 | Web Page Scripting Languages |
When a database is exposed to the web, users are able to access the database with a web browser. In this chapter, we will learn how to use Active Server Pages (ASP) to connect to an Access database and insert, modify, delete, list and search records - exposing the database to web users.
Here are some examples of how companies use databases on the web:
Here is a list of the tasks that are used to develop a web page that displays data from a database:
Database management systems (DBMS) are applications that store and manage data. Relational databases store data in tables and link (or relate) tables together to minimize data redundancy. A table is a grid of rows and columns which stores related information. Each row is a record. Each column is a field. For example, an employee table would contain a record (row in a table) for each employee. A field (column in a table) will contain specific information about each employee, such as name, address, pay rate, etc. A recordset is simply a group of records - could be all the records in a table, or it could be a subset of records that match some criteria.
Database programs include Access, dBase, FoxPro, SQL Server, Oracle, MySQL. In Access, we will use the design view to define fields in a table and to assign each field a data type (number, text, date, etc.).
The Universal Data Access (UDA) model provides a method whereby data can be shared across different applications and platforms. The UDA is implemented by standards called open database connectivity (ODBC) and OLEDB, and the ActiveX Data Object model (ADO). See http://www.microsoft.com/data.
There are many ways to connect to your web application using this UDA model. First, build your database tables and determine what you want to retrieve from your database. Then, decide if you need to use ODBC drivers or OLEDB providers to connect to your database. Next, create either a DSN (Data Source Name) or a connection string. The DSN or connection string identifies the location of the data source and the connection method. Then, create a web page that will connect to the database. You can use the built-in objects within ADO to connect to the database, execute commands, and return a recordset from the database. Finally, you display the data, write information in a cookie, or perform calculations on the data.
Because of differences between ODBC and OLEDB, and differences between the ADO objects, it is recommended that you first work with the Access and ODBC until you are comfortable with ADO objects. Then, you can convert your Access application to SQL Server and use OLEDB.
In 1992, a standard known as open database connectivity (ODBC) was created to provide a common interface for relational database systems. ODBC drivers are used to provide access to an ODBC-compliant database and are available for most DBMSs. The ODBC drivers provide the low-level interface to the database applications. You do not need to know how the database application is storing the data in order to access the data.
Email programs store data in a different format than relational database applications. The UDA model provides a method for accessing relational database stores and these non-relational data stores, which is called OLEDB - which stands for object linking and embedding database. OLEDB allows your application to access a database without an application or database specific interface. You don't need to know how the database application is storing the data in order to access the data. OLEDB providers are available for most common data stores.
Microsoft provides an OLEDB provider that will interface with the ODBC driver in order to support legacy database applications. For example, if your database application does not have an OLEDB provider, but does have the ODBC driver, you can use the OLEDB provider for ODBC to access the database. The web application interacts with objects built within the ADO model. Then ADO interfaces through the ODBC and OLEDB to the database.
The ODBC drivers and OLEDB providers enable the low-level interface to the database. In order to send requests to and receive responses from ODBC and OLEDB, Microsoft provides Microsoft Data Access Components (MDAC), which include not only the OLEDB provider and ODBC driver, but also the components that would be used to interface to ODBC and OLEDB. This collection of components is known as ADO (ActiveX Data Objects).
MDAC contains ODBC drivers for text files, Access, FoxPro, Paradox, dBase, Excel, SQL Server, and Oracle. MDAC also provides the ODBC Driver Manager which is used to create a DSN from the control panel. DSNs contain the connection information required to connect to an ODBC or OLEDB data source. The most common way to connect a web application to a database is to use ActiveX Data Objects (ADO), which come as a part of the Windows MDAC (Microsoft Data Access Components).
Older computers may not have MDAC installed. MDAC can be downloaded from http://www.microsoft.com/data/download.htm. MDAC is automatically installed when you install programs such as Visual InterDev, Personal Web Server, Internet Information Server, Visual Basic, SQL Server 2000, Visual Studio, Windows 2000 SP1 or SP2 and Windows XP.
There are different versions of ASP, MDAC and ADO. When you install Personal Web Server (PWS) or Internet Information Server (IIS), you are also installing support for ASP version 2.0, MDAC version 1.5 and ADO version 1.5. If you install any of the Visual Studio 6.0 programs, you are installing MDAC version 2.0 and ADO version 2.0. MDAC and ADO version 2.1 are available from Internet Explorer 5.0 and with Office 2000 applications. MDAC and ADO version 2.5 come with Windows 2000. ADO+ is now called ADO.NET. See http://msdn.microsoft.com/asp for the latest information on ASP.NET. You may want to check out this Microsoft link on How To Build Your First ASP Application.
The following code will identify which version of ADO is installed on the server.
<%
set oC = server.createobject("ADODB.connection")
Response.write oC.Version
%>
Let's see the version currently running on the server - 282ln12ADOversion.asp.
The ADO model, ODBC drivers, and OLEDB providers reside on the server, not the client. ASP.NET and ADO.NET allow the use of disconnected recordsets, which are recordsets downloaded from the database to the client. Disconnected recordsets are used locally. Then, after changes are made, the recordsets are synchronized with the database on the server.
In order to connect a web application to a data source, you need to know the location and type of data, and what parameters to send with the connection request. There are two ways of storing this information:
The DSN stores the connection data in the registry on the computer where the DSN was created. In order to create a DSN, you must have local access to the computer. Oftentimes, web developers do not have local access to the server since the server could be anywhere in the world. Web hosting companies usually charge an extra fee for setting up a DSN. It is more economical to have the connection information stored in an external file that you can upload to the web server. The connection information can be placed in a connection string and stored in an external file.
A connection string contains the connection data in a string within the application. The connection string can be stored in the web page that is accessing the database, or in a separate server-side include file. The benefit of using the connection string is that you do not have to have local access to the server.
You can either FTP the file with the connection string, or you can use the publishing feature within FrontPage, InterDev, Visual Studio.NET, or Dreamweaver to send the file that contains the connection string. Whether you use a DSN or a connection string, you must identify the same information required to make the connection to the data source.
A DSN is a convenient way to store connection information that would otherwise have to be specified in a connection string.
There are three types of DSNs:
Web applications will require that we use a system DSN to store database connection information.
To create a system DSN, use the ODBC Driver Manager in the Control Panel. On Windows 2000, ODBC will be found under Administrative Tools in the Control Panel. The exact procedure for creating a DSN will vary with the type of DSN, the data source, and the version of MDAC installed on the server.
We will not use DSNs in our class since this method requires physical access to the server. As in many web development environments, the server is not easily accessible. Instead, we will use a connection string to make our database connections. If you set up a DSN at home or work, just to play around with it, you can use the System DSN once it is created as outlined in the next section.
Once you create the DSN, you can refer to it in your ASP page with the following code.
Dim oC
set oC = server.createobject("ADODB.connection")
oC.open "dsn=NAME_YOU_GAVE_THE_DSN"
When you create a DSN that has a username and a password associated with the database, you must pass these parameters when you request to open the connection as follows: (DSN for an ODBC connection to an Access database)
Dim oC
set oC = server.createobject("ADODB.connection")
oC.open "dsn=NAME_YOU_GAVE_THE_DSN; uid=admin; pwd=pass;"
Advantages of using DSN-less Connection Strings:
Although File DSNs can be shared with other users, they are bottlenecks on busy sites since each visitor will be hitting this file with an open, read, and close to get the data for the driver/provider.
The connection string contains the same connection information that is stored with the system DSN. The most basic connection string, which is used to connect to an Access database, consists of three parts:
The following is a connection string to connect to an Access database named customer in the current folder:
CS = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= customer.mdb;"
Connection strings must appear on one line because it is a string.
Because connection strings are case sensitive and because the driver name must match exactly the name of the driver installed on the server, it is a good idea to split the connection string into two variables, then concatenate them into one variable. This keeps you from having to retype the name of the driver each time you want to reuse a connection string.
In the following example, the DRV variable stores the Access ODBC driver and the DBQ variable stores the location to the Access database. The strDriver and strDBQ variables are concatenated and stored in the CS variable.
dim DRV, DBQ, CS
DRV = "DRIVER={Microsoft Access Driver (*.mdb)};"
DBQ = "DBQ= customer.mdb;"
CS = DRV & DBQ
Connection strings can be created within the web page that displays the recordset, or they can reside in a server-side include file. If you plan to use the database with other web pages, it is easier to maintain the connection string in a SSI file that you can include in each web page that accesses the database. If the name or location of the database changes, you only have to change it once, in the SSI file.
When you are working with an Access database, you can use an ODBC driver or an OLEDB provider to connect to the database. The keyword PROVIDER is used instead of DRIVER. Also, the keyword DBQ is changed to DATA SOURCE. Sample code for the OLEDB driver is shown below:
CS = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source= customer.mdb;"
We can create a SSI file with a connection string to an Access database by using Notepad and the following code: Note: The server script tags <% and %> are used to tell ASP to process the code on the server.
<%
dim DRV, DBQ, CS
DRV = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DBQ = "DBQ= customer.mdb;"
CS = DRV & DBQ
%>
We can save the file as conxsample.inc and place it in our data folder to make it available to any of our web pages.
See Charles Carroll's page from LearnASP.com at http://www.learnasp.com/learn/dbopen.asp
See "A Nifty Way to Create Connection Strings" from 4GuysFromRolla.com at http://www.4guysfromrolla.com/webtech/070699-1.shtml
Similar to Access connection strings, you provide the name of the provider, the userID and password associated with the database, the name of the SQL server, and the name of the data source. Here is an example of an ODBC connection string to a SQL server database.
<%
CS = "Driver={SQL Server}; SERVER=Windamere; UID=sa; PWD=; DATABASE=customer"
%>
Here is an example of how to use an OLEDB connection to a SQL Server database. Note the keyword used is PROVIDER, not DRIVER. The name of the database is identified as the INITIAL CATALOG. The name of the server is identified as the DATA SOURCE.
<%
CS = "Provider=SQLOLEDB.1; Data Source=Windamere; User ID=sa; PASSWORD=; Initial
Catalog=customer;" (NOTE: SHOULD BE ON THE SAME LINE)
%>
Once you've created the database and the connection, the next step is to create the web pages that will interact with the database. The ActiveX Data Object Model (ADO) provides a method for accessing databases from your web pages. The primary objects in the ADO model are the:
The field object is accessed from the recordset object. The parameter object is a parameter of the command object. The err object is created when a connection error occurs. See the ADO programming model below, showing each object and how they relate to each other.
See Microsoft's ADO Web site at http://www.microsoft.com/data/ado for more information on the ADO and UDA models.
The connection object is used to establish a connection between a Web page and a database.
Use the CreateObject method of the server object to create a connection object. The connection object can open a connection to a database using the open method. The connection parameter can be a DSN or a connection string - both identify the path and name of the database. They also identify the ODBC driver or OLEDB provider used to connect to the database.
You can use the open method of the connection object to open a connection to a database. You must pass the DSN or connection string to the open method. We must pass the DSN or connection string to the open method. The following example demonstrates how to open a connection to a database using a DSN name dsnProducts. The connectiono object is named oC.
Dim oC
Set oC = server.createobject("ADODB.connection")
oC.open "dsnProducts"
We must always close the connection to the database when we're finished using the connection. Set the variable to "nothing" to release the memory that was used to store the object. We must first close the connection. Use the following syntax:
oC.Close
set oC = nothing
We can use the connection object to execute commands by passing the command to the open method of the connection object. These commands are often requests for tables or SQL statements. The SQL "select" statement is used to retrieve records from a database.
When the connection object executes a "select" statement, a group of zero or more records is returned as a recordset object. The group of records returned in called a recordset. The following example illustrates how the connection object can execute a SQL "select" statement to return a recordset.
Dim oC
set oC = server.createobject("ADODB.connection")
oC.open "SELECT * FROM products", "dsnProducts"
The following example illustrates how the connection object can be used to execute a SQL "insert" statement to insert a new record. We'll look more at SQL statements in the next chapter.
Dim oC
set oC = server.createobject("ADODB.connection")
oC.open "INSERT INTO Products(name,price,date,number)
VALUES('chair',153.00,#12/1/2000#,NULL)","dsnProducts"
We can pass the keyword NULL instead of a value.
The connection object contains several properties.
To change the ConnectionTimeout or CommandTimeout properties the connection object must not be open. The following example illustrates these connection object properties:
CMT = oC.CommandTimeout
Response.write CMT
CNT = oC.ConnectionTimeout
Response.write CNT
Response.write oC.Version
We can set these properties with:
oC.CommandTimeout = 60
oC.ConnectionTimeout = 60
The recordset object can be used in combination with the connection object to open the connection and to retrieve a recordset. In the following sample code, the recordset object uses a connection opened by the connection object. The recordset object then retrieves a table named products.
Dim oC
set oC = server.CreateObject(“ADODB.connection”)
oC.open “dsnProducts”
dim oRS
set oRS = server.CreateObject(“ADODB.recordset”)
oRS.open “products”, oC
oRS.Close
set oRS = nothing
oC.close
set oC = nothing
A recordset object is an object, with properties and methods. A recordset is a set of database records.
The field object is available through the recordset objects. Once you have
retrieved a recordset, you can access all of the records and fields within the
recordset. Know syntax to write a field value to the screen:
response.write oRS("name").
Dim oC
set oC = server.CreateObject(“ADODB.connection”)
oC.open “dsnProducts”
dim oRS
set oRS = server.CreateObject(“ADODB.recordset”)
oRS.open “products”, oC
do until oRS.EOF
response.write "Product Name: "
response.write oRS("name")
response.write "Product Price: "
response.write formatCurrency(oRS("price"))
oRS.movenext
loop
The loop displays the name and price for each product in the table.
The command object enables you to specify a specific command that you are going to execute on a database. This command is often a request for a table or SQL statement such as select, insert, update or delete. The sample code below demonstrates how the command object executes a SQL statement.
Dim oC
set oC = server.CreateObject("ADODB.connection")
oC.open "dsnProducts"
dim oCM
set OCM = server.createobject("ADODB.command")
SQL ="Delete FROM Products WHERE ProductName = ‘chair’"
oC.open CS
oCM.ActiveConnection = oC
oCM.CommandText = SQL
oCM.CommandType = adCmdText
oCM.Execute
It is useful to store SQL statements in a variable, and then assign the variable to the CommandText property. Then, if an error occurs, it is easy to use the response.write method to determine what the SQL statement is.
It is also useful to use the constant adCmdText instead of a numeric value. In order to use a constant, you must include the file that contains the database constants, called ADOVBS.INC. This is a free file from Microsoft which was automatically installed when you installed support for ADO.
The recordset object can be used to retrieve recordsets from the database. First, create recordset object using the Create Method of the server object
set oRS = server.createobject("ADODB.recordset")
The open method of the recordset object is used to open a connection to the database and retrieve the recordset. The open method takes several parameters. First is the source parameter, which is usually a SQL statement, the name of a table, or a command object. The next parameter is the connection parameter, which contains the connection information. Some other parameters can be passed to the open method to indicate the type of recordset to return.
RecordsetName.Open source, connection, cursorType, lockType, Options
The following sample code shows how to open a recordset object using a connection string that is stored in a variable named CS.
<% dim CS Drv, DBQ, oRS
Drv = "Driver={Microsoft Access Driver (*.mdb)}; "
DBQ = "DBQ= D:\chapter12\customer.mdb;"
CS = DRV & DBQ
oRS.open "SELECT * FROM customer", CS
%>
The fields collection contains the field objects returned from the recordset object. Retrieving a field value is shown below.
oRS.("field1").Value
Or you can request the field name.
oRS.("field1").Name
You can loop through the entire fields collection.
<% for each fld in oRS.Fields
Response.write fld.Name
Response.write "<br>"
Next %>
The same type of loop can easily be used to recreate the data in a table on a Web page.
<table border="0" width="400">
<tr><td>E-mail Address</td><td>Name</td></tr>
<do while not oRS.EOF %>
<tr>
<td><% = oRS("email") %> </td>
<td><% = oRS("name") %> </td>
</tr>
<% oRS.movenext
loop %>
</table>
http://www.halharris.com/282/sql.htm
http://www.halharris.com/282/SampleCode.htm
Check out this EXAMPLE for use on our class server.