Using ASP to interface with Databases
We'll use the Northwinds Database, which is provided
with Office 97. To be able to access this database with ASP on either IIS4.0
or Personal Web Server, you need to provide a System Data Source Name (System
DSN). Setting up a System DSN allows our database to be accessed by all
users on the network. You have to use the 32bit ODBC administrator program
by double-clicking on its icon in Windows Control Panel.
<%LANGUAGE="VBSCRIPT"%>
<HTML>
<HEAD>
<TITLE>DB App</TITLE>
</HEAD>
<BODY>
<%
SQLQuery="Select * FROM Customers;"
SET DbObj=Server.CreateObject
("ADODB.Connection")
DbObj.Open="DSN=NWIND;UID=;PWD=;"
SET oRS=DbObj.Execute(SQLQuery)
%>
<P>Customers</P>
<TABLE BORDER=3>
<%WHILE NOT oRS.EOF%>
<TR>
<TD> <%=oRS.Fields.Item("CustomerID")%>
</TD>
<TD> <%=oRS.Fields.Item("CompanyName")%>
</TD>
<TD> <%=oRS.Fields.Item("ContactName")%>
</TD>
<TD> <%=oRS.Fields.Item("Address")%>
</TD>
</TR>
<%oRS.MoveNext%>
<%WEND%>
<%oRS.Close%>
<%Set oRS=Nothing%>
<%DbObj.Close%>
</TABLE>
</BODY>
</HTML>
The SQLQuery statement is an SQL statement
to select Database entries. Server.CreateObject creates an instance
of the ActiveX Dataobject. By using the CreateObject method of the
Server object, we can instantiate a variable to hold a reference to the
newly created Connection object, just as we would did it with other components.
Once we've created an instance of the Connection
object, we're ready to start using it. We should use the connection to
open our data source, so that we can access and manipulate the data in
it. This is achieved using the Open method that the Connection object
provides.
The ConnectionString parameter is a string
that specifies a data source either as a data source name (DSN), or by
specifying a detailed connection string made up of individual parameter=value
arguments, seperated by semicolons, and containig no spaces. Any ConnectionString
containing an equals sign is interpreted as a detailed connection string.
Having created and opened a connection to our data
source, we can begin to use it. Having to carry out commands that change
the data in our data source, we could use the Connection object's
Execute method. This can accept our SQLQuery string containing
an SQL statement. For queries that return values, we must assign
the results to a RecordSet object. This is like a table in memory,
holding records which are subdivided into individual fields (or columns).
We have to set the result into the variable oRS to refer to a recordset
object, and we have to enclose the parameters in parentheses.
It's convenient to regard the RecordSet object
as having the same structure as a table; in other words: It consists of
rows (each of which is a complete record) and columns (each of which represents
a field). We can move from one record to another, making it the current
record and access the individual fields within this current record. The
easiest way to do this is to call the MoveNext method of ADO. The
end of a RecordSet will be showed by the EOF property of
the RecordSet. When EOF is True any attempt to move forward
in the RecordSet will generate an error.
While this proves a useful way of getting at each
record in the RecordSet, it doesn't do much towards extracting the
data from it. That's the job of the Fields collection. Every RecordSet
object has a Fields collection, which contains the data and other
information about each field in the current record.
oRS.Fields.Item("CustomerID")
The Item property is the default property
of the Fields collection, so we could omit it.
oRS.Fields("CustomerID")
The Fields Collection is the default collection
for the RecordSet object, so we could omit the collection name too.
oRS("CustomerID")
To insert the information into the page we use the
= sign. This is a shortcut for the write method of the Response
object. All it does is insert a string into the HMTL stream that the brower
receives.
After all we have to close the RecordSet and
Connection object.
Now ... you're ready to show the content of ODBC-
capable Databases on the Web. By the way: The discussion fora in the Forum
section are working this way.
|