Syntax for Simple Recordsets - Page 3
November 27, 2000
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)
Writing Data to the HTML Page
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)
Simple Recordsets - Reading Data - Page 2
Beginning ASP Databases
Using Data - Page 4
|
