Beginning ASP Database

Chapter 3

This manuscript is an abridged version of Chapter 3 from the Wrox Press book Beginning ASP Databases. 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.

Beginning ASP Databases explains the theory and practice of using ADO with ASP by presenting numerous examples, exercises, lists of common errors and quizzes. The emphasis is on the authors' proven teaching techniques and the presentation of the most commonly used features of ADO in ASP. If you read each chapter and do the exercises you will have a portfolio of several dozen data-intensive web pages of increasing complexity - a fine return on your investment. For further details about the book, and other books in our range, visit the Wrox Press Web Site.

• Preparing to use Simple Recordsets
• Which Record?
• Building Tables with Data
• Reading All of the Records (with EOF)
• A Trap With Recordsets and Tables
• Exercises
• Quiz

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.

Preparing to use Simple Recordsets

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

Step 1 - Create a DSN

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.

Step 2 - User Identification and Password

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.

Step 3 - Understand the Structure 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

Syntax for Simple Recordsets

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:

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 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)

Stuffing Data into a Variable

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)

Using Data in an Expression

Data retrieved by ADO can be used directly in an expression. The pseudo-code listings below show examples:

If ORSp("NameFirst")="Enrico" then
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 ofthe 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

Using Data as an Argument in a Function

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