3
Simple Recordsets -
Reading Data
So you've decided that ASP-ADO is the solution for your
situation, and you've learned how to set it up. Now you're ready to begin using
ASP-ADO to access and manipulate data within web pages. This chapter discusses
the simplest situation – the simple task of reading
the data. Although we are only reading the data, we can still use it in several
ways. Not only can we write it to the page; we can also use the data in
variables, expressions and as the arguments for other functions.
Then we look at some of the techniques and problems with
presenting the data that we read in an HMTL table. Last, even though VBScript
implements only parts of Object Oriented Programming, we'll include a few
paragraphs to provide an overview of the aspects of OOP that are available.
One of my sailing coaches emphasized that yacht races are won or lost long
before the day of the race. Regattas are won during the months of preparation
and training prior to the starting gun. Fortunately, ADO does not require many
weeks of practicing tacks and jibes in the cold rain; you just have to consider
the following three issues:
Creating a DSN
Having the correct User Identification, Password and
Permissions
Understanding the structure of the database
In Chapter 2 we talked about creating an ODBC Data Source which is
referred to by a DSN – this is typically performed by a systems administrator
or other operator with access to the server. To review: the DSN contains the
information necessary to make a physical connection into the database. This
information may include database name, path, server name, User ID, Password, Driver
name and other parameters.. These
pieces of information are wrapped up in a DSN, which is given a name. You need
to get the name of your DSN from your systems administrator prior to using
ASP-ADO.
If you are working on PWS you have access to the settings of
your Windows OS and can set your own DSN (also discussed in Chapter 2), by
selecting Start | Control Panel | 32 bit ODBC
and following the steps of the wizard. In my development work I keep two copies
of the database – one on my development machine using PWS and one on the
server.
In Chapter 6, we will talk about how to
make a connection without a DSN.
I'm frequently approached by people seeking
help with ADO, only to find that the problem is not with their ADO code, but
that they do not have access rights to the database they're trying to use. Even
if you're only going to request a simple recordset, you need to meet the
security requirements of the database. Generally this means that you present a
User Identification (UserID) and a password (pwd).
Security can be established at various levels, but you will need to get this
information from the owner or systems administrator of the database.
Another obvious (but frequently overlooked) point of preparation is understanding the structure of the database.
ADO will always produce errors if your commands don't use the exact spelling of
the tables, queries, views and fields. A more subtly but equally deadly error
arises when commands to the database conflict with its relationships between
tables. In Chapter 5 we'll look at a technique for finding out the names of the
fields, but you still have to know the names of the tables. The structures of
the tables used in this book are provided in Appendix A.
Common Errors in preparation:
Incorrect spelling of DSN
DSN no longer exits, or name has been changed
The DSN will not work if the file is moved to another location
after the DSN is created (applies to Access and Excel files).
DSN is of User or File type rather than a System DSN
UserID or password is misspelled or incorrect
Names of fields or tables are incorrectly known by plural or
singular (e.g. Author instead of Authors)
Type (number/text/date) of fields is not known correctly
Once you have properly prepared for using ADO, creating the
simplest recordsets only requires three lines of code. Here's a first example:
suppose we have a DSN by the name of Contacts,
which contains a table called People. We can access this data
with the following three lines of code:
Dim oRSp
Set oRSp =
server.CreateObject("ADODB.recordset")
oRSp.open "People",
"DSN=Contacts"
Additionally, if there is database security enabled, we can
specify our security details as we create the recordset. For example, suppose
our User ID for accessing the database is AlbertE, and
our password is emc2. We can pass these additional parameters as
follows:
Dim oRSp
Set oRSp =
server.CreateObject("ADODB.recordset")
oRSp.open "People",
"DSN=Contacts;uid=AlbertE;pwd=emc2"
Connection drivers (see chapter 6 on
Connections) vary in their nomenclature for identifying users. The code in this
section of the book is illustrating the syntax for ODBC for JET and SQL, that
is uid=AlbertE. If you are using the native OLEDB drivers for SQL you would use UserID=AlbertE.
Let's look more closely to understand what's happening in
these three lines of code:
The first line, above, dimensions a variable (that is, it reserves
the name oRSp).
Although in VB proper we try to dim variables with a specific type, in VBScript
all variables are variants. In fact, this line is not mandatory in VBScript;
however, as the ASP debugging tools become more robust (more like Visual
Basic), dimensioning your variables and objects will help you to catch errors.
Various programmers name their variables and objects in various ways, and in
this book we'll use the convention of prefixing the name of any object with a
lower case o.
Since this object will be a recordset, we'll follow that with the RS.
Before long you will be working with multiple recordsets on a page, so it's
worth using a few other characters in your variable name to indicate what data
this particular RS will hold. In this case I used a p, since this recordset
will be filled with records of people.
The second line creates a Recordset object and the oRSp object is turned
into a pointer to this object. Now oRSp can hold all of the
properties, react to the events and execute the methods of a recordset from the
library called ADODB. This process is called instantiation (see Notes on Objects at the end of this
chapter). The action is performed by the CreateObject method of
ASP's Server
object. The CreateObject method needs
one parameter – the name of the class to use as a model. We specify the class
library (in this case, ADODB) and the class within that library (in this case,
Recordset). Once you created this new object, you have all of the capabilities
that Microsoft build into the original tool (in this case, the ability to
access data).
The third line uses the recordset object's Open
method to make data available to you. Note the syntax: we're calling the Open method of the object
called oRSp,
so we write oRSp.Open. In order to carry out its task, the Open
method requires two parameters– in this case we supply two parameters.
So it only takes us three lines of ADO
code to open a recordset and prepare it for reading. However, keep in mind that
this uses OOP. Under the covers lies all of the low-level code required to
prepare the recordset (and believe me, that is plenty of code) – it's already
been written by Microsoft and encapsulated in the Recordset object of the ADODB library.
Before we go on, there is an additional line of code that
will make your life easier. Although good debugging tools are still in the
future, you can start your VBScript with the following line.
Option Explicit
This directive will allow VID to check your code and if
you mis-type a variable you will get an error warning at design time. However,
use of Option Explicit then requires that you DIM all
variables prior to use.
Once we have established the record (Dim, Set,
RS.Open)
we can then access the data in the recordset. A given piece of data is utilized
by stating the recordset and the field name as follows:
ORSp("PeopleNameFirst")
This will return the data in the NameFirst
field of the current record. This is like a function in that a value is
returned, and that value must go somewhere or be used somehow. We will discuss
the four most common ways of using data in the following sections. But first
let me share a list of the most common mistakes I have observed in code from my
ASP-ADO students.
Common Mistakes When Creating Recordsets and
Using Recordset Data:
(most frequent of all) students forget that the rs("field") construct returns a data.
That data must go somewhere; as the argument for a Response.Write, or into a
variable or used as a test expression. But you can never have a naked rs("field") sitting on a line.
Leaving out the Response.Write
Misspelling the Response.Write
Putting double quotes around the entire
oRSp("NameFirst")
Leaving out the double quotes or parenthesis
Wrong RS name or wrong Field name
Misspelled Field Name (very common error)
A closed recordset is closed. Don't try to use it or try to
close it again
The recordset is empty (EOF and BOF are both true)
To write the data on the page:
Response.Write
oRSp("PeopleNameFirst")
The above code examples would be used within a section of ASP, that is within the <%
%>, to put the data onto the page. Keep in mind that there is
also the ASP shortcut to drop a Response.Write into a section of HTML as shown
below:
Welcome,
<%=ORSp("PeopleNameFirst")%> to our page.
Microsoft now recommends a syntax that explicitly states the
Value property to return. Although this is not yet common, stating this default
property improves speed and robustness. An example follows.
Response.Write
oRSp("PeopleNameFirst").Value
The most common mistakes of the VBScript Response.Write Shortcut
are:
Forgetting the equals sign
Typing in "Response.Write"
Forgetting the <% and %>
Wrong RS name or wrong Field name
Misspelled Field Name (very common error)
Sometimes we don't need the data to go directly to the page,
in which case we can save the information into a variable. For example, we may
need to perform some string
manipulation or validation prior to building the page.
VarNameFirst =
ORSp("PeopleNameFirst")
The code on the above line stores the data into a variable
for later use. This must be performed within ASP delimiters, since HTML lacks
capacity to use variables.
The most common mistakes are:
Attempting to perform this operation in HTML, outside of ASP
Wrong RS name or wrong Field name
Misspelled Field Name (very common error)
Data retrieved by ADO can be used
directly in an expression. The pseudo-code listings below show examples:
If
ORSp("NameFirst")="Enrico" then
' code for Enrico
End If
In the above code we use the data in a field of the current
record of the oRSp recordset as the text to compare against the
word "Enrico." A similar test is performed in the code below to
determine if it is time to end the looping.
Do while NOT
oRSp("NameFirst")="Enrico"
' code for people OTHER then Enrico
oRSp.MoveNext
Loop
If oRSp("Member") then
' code for members
Else
' code for non-members
End If
In our last case we switch to retrieving data from a
different field. The member field was established (at the time the database was
designed) as of type True/False. Therefore it will return a value of true or
false, which can be directly used as a whole expression. If the database
contains true the code for members will be run.
The most common mistakes are:
Attempting to perform this operation in HTML, outside of ASP
Leaving the comparison sign (= or > or <) out of the
expression
Writing expressions where the two sides of the comparison sign are of two different
data types. For example,
"Joe" should not be compared to "2".
Wrong RS name or wrong Field name
Misspelled Field Name (very common error)
Errors in upper/lower case for data stores that are case
sensitive
Quotes around numerical values
Data read by ASP-ADO can be used as an argument for
another function. For example:
VarNameFirstLetter =
Left(oRSp("PeopleNameFirst"),1)
VarPassword =
lCase(oRSp("PeopleNameFirst"))
VarSpaceLocation = instr
(oRSp("PeopleNameFirst")," ")
Although the above works, many coding shops prefer that you first
read the data into a local variable. It is easier to read and maintain code
without all of the quotes and parentheses.
The most common mistakes are:
Attempting to perform this operation in HTML, outside of ASP
Providing data from ASP-ADO which is of the wrong type for the
argument
Wrong RS name or wrong Field name
Misspelled Field Name (very common error)
Not writing test code to handle a request that returns a NULL
Try It Out – Using Recordset Data
We'll create a page that opens a recordset based on the items
table of the Clothier database (see
Appendix B for the source and structure of this database). From that recordset,
we'll perform four tasks:
Writing the name of the first item on the page
Putting the type of the first item into a variable and then
print that variable to the page
Using an If..Then structure so that items with less than 10 to
a box show the warning "Small Box"
Printing the price of the first item (using the Format function
to make it with two decimal places only)
The following listing shows the code to generate the
required page:
<%
dim oRSi
set
oRSi=server.CreateObject("adodb.recordset")
oRSi.open "items",
"DSN=clothier"
oRSi.MoveFirst
Response.Write "Next line is a
simple write of data:<BR>"
Response.Write
oRSi("ItemName") & "<BR><BR>"
Response.Write "Next line is
writing a variable that holds the data:<BR>"
dim varItemName
varItemName =
oRSi("ItemType")
Response.Write varItemName &
"<BR><BR>"
Response.Write "Next line is
deciding what to write based on an If...Then using the data:<BR>"
If
oRSi("ItemQtyPerBox")<10 then
Response.Write "Small Box<BR><BR>"
Else
Response.Write "Large Box<BR><BR>"
End If
Response.Write "Next line uses
the data as an argument for a function:<BR>"
Response.Write
UCase(oRSi("ItemDepartment")) & "<BR><BR>"
%>
Your page should appear as below in your browser:
How
It Works - Using Recordset Data
The first few lines in the next listing create the
recordset. A recordset will open with the pointer at record one, so in this
simple case there is actually no need for the MoveFirst method. On the other
hand, if a recordset has been opened earlier and been in use, you would want to
use the MoveFirst so you are sure you are at BOF.
<%
dim oRSi
set
oRSi=server.CreateObject("adodb.recordset")
oRSi.open "items",
"DSN=clothier"
oRSi.MoveFirst
In our first section we merely need to print the data which
is returned from the recordset, as follows:
Response.Write "Next line is a
simple write of data:<BR>"
Response.Write
oRSi("ItemName") & "<BR><BR>"
But if we can also put data into a variable which can be
used later, in the following case to write to the page:
Response.Write "Next line is
writing a variable that holds the data:<BR>"
dim varItemName
varItemName =
oRSi("ItemType")
Response.Write varItemName &
"<BR><BR>"
As we can see in the next list, you can use data without
printing it to the page. Below we use it in a test to determine which of two
messages to write to the page:
Response.Write "Next line is
deciding what to write based on an If...Then using the data:<BR>"
If
oRSi("ItemQtyPerBox")<10 then
Response.Write "Small Box<BR><BR>"
Else
Response.Write "Large Box<BR><BR>"
End If
Another option is to use the data returned from the
recordset as an argument in a function. Below we use the name of the department
of the item as the argument for the Upper Case function.
Response.Write "Next line uses
the data as an argument for a function:<BR>"
Response.Write
ucase(oRSi("ItemDepartment")) & "<BR><BR>"
%>
Visual Basic Books 
