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