Platinum Edition Using Visual Basic 5

Previous chapterNext chapterContents


- 33 -
Database Access with ActiveX Data Objects (ADO)

OLE DB is the low-level API used to create the data providers for the ADO interface.
ODBC provides OLE DBs interface to relational database sources.
Writing syntactically correct SQL statements isn't hard. With some new tools, it's not even necessary.
ADO is designed to work well in a client/server application environment. It's very useful in Web development, which is by definition client/server.
The ADO Object Model consists of Connection, Errors, Command, Parameters, Recordset, and Fields objects. All but Errors have a Properties collection.
Building a non-Web example requires small changes from Web-based development.

ActiveX Data Objects (ADO) is nearly revolutionary in its concept and scope. It is not a database connector like DAO or ODBC; rather it is an extensible set of data access objects that is a programming model. These objects are based on OLE DB, which operates at the basic API (Application Programming Interface) level. ADO wraps this functionality into an easy to use flexible package that will be the basis of all of Microsoft's future data access development. Even better news is that if you are familiar with DAO or RDO, you will have a very short and shallow learning curve to master ADO.

Microsoft's other new database-connectivity tool, Advanced Data Connector (ADC), is firmly rooted in ADO technology. ADC provides connection to data-aware controls.

Microsoft is making it very clear that DAO and RDO have been developed as far as they will be taken. They will be supported for some time into the future, but will not be enhanced. All future development effort will go toward ADO, so climb aboard and see that the future is here today.

Understanding ActiveX Data Objects

ADO currently does not come as part of the Visual Basic 5.0 product. It is part of Visual C++ 5.0, Visual InterDev, and Active Server Pages, which is part of Microsoft Internet Information Server 3.0 for Windows NT 4.0 and the Personal Web Server for Windows 95. ADO isn't part of VB 5.0 because ADO wasn't completed when Visual Basic 5.0 was packaged for shipment. ADO definitely will be part of all future releases of Visual Basic. Nevertheless, Visual Basic 5.0 currently works seamlessly with ADO.

In the past, Visual Basic programs were created to provide access to data. The first data was simple ASCII files. This was followed by an interface known as VBSQL that was used to interface with SQL Server databases. The emphasis then shifted to ISAM databases, such as MS Access. Microsoft created the Jet Database Engine and Data Access Objects (DAO) to allow easy access to the Jet. Mixed into all of this was Open Database Connectivity (ODBC), which provided remote access to a heterogeneous group of database engines through ODBC drivers.

The latest and most significant development is OLE DB, which provides access to a heterogeneous group of types of data stores, including relational databases. The ActiveX Data Objects is a programming interface model that gives access to OLE DB.

Understanding Where OLE DB Fits

OLE DB is a low-level OLE API that organizes as either Data Providers or Data Consumers. OLE DB is a set of functionality that is encapsulated in an OLE server. This OLE server uses Data Providers to interface to various types of data stores. The list of data stores will grow over time as more data Providers are written. ADO is a programming model that interfaces to the OLE DB server to provide access to the data.

OLE DB currently uses a Data Provider, code-named Kagera, to interface to ODBC, which then provides an interface to relational and ISAM data stores. There is a plan eventually to create Data Providers for OLE DB that will interface directly to the relational and ISAM data stores, as well as other data stores. OLE DB is being called the universal data access technology.

Understanding ODBC


NOTE: ODBC is a subject about which you will need information as you read this chapter. Everything that you need to know about ODBC is explained in the section "Introducing ODBC" in Chapter 32, "Using Remote Data Objects (RDO)."

Reviewing Relational Databases

Relational databases are based on the theoretical work by E. F. Codd. Prior to Codd's concept of relational databases, attempts were made to use a number of database structures. The organizational structures were elegant and performed the task, but the inverted tree and other structures proved inflexible after their creation. Any change to the structure was a major effort and required recompilation of the database. One of the great features of relational databases is the flexibility. Adding another item of data is no problem with the relational model.

A relational database management system (RDBMS) is a software application that stores data. This data is arranged in such a way as to be available for reading, updating, adding new records, and deleting records. The RDBMS doesn't need a front end or user interface. It is sufficient if the database accepts command-line commands (in the form of SQL statements, discussed later in this chapter), executes the command and returns a group of records, updates a record, adds a record, or deletes a record.

The user interface (UI) usually is a separate function that is in a totally separate application program called a client. The client/server design concept is explored in a later section of this chapter.

Tables

Data in a relational database is organized in tables. A table is a two-dimensional organizational structure that is composed of rows and columns. A column contains a specific category of data, such as the names or social security numbers of a group of people, for example. In such an example, the collection of data regarding a particular individual, such as name, social security number, address, and phone number, are contained within a row.

Table 33.1 is an example of a table that contains the contact information in a PIM (Personal Information Manager).

Table 33.1 A Contacts Table

Last Name First Name Area Code Phone No
Jones John 520 555-1212
Hansen Sue 212 555-1212
Adams Sue 602 444-3434
Adams Phil 602 444-3434
Smith Fred 303 222-2323

This looks very much like the information that is kept in many pocket diaries. The rows in the table are not in any particular sequence. The "correct" sequence for the rows in a relational database is in an as-entered chronological sequence.

Primary Keys

One requirement for a table is that each row of the table be unique. This unique identifier can be a specific column that contains an item of information that is unique, such as the social security number of an individual or the serial number of a computer. The primary key can be composed of more than one column.

Notice in Table 33.1 that neither the last name nor the phone number will work as a primary key. Phil and Sue have the same last name and phone number. The first name will not work as the primary key because Sue Hansen and Sue Adams have the same first name. With the data contained in this table, only the combination of the first and last names works as the primary key.

Experience dictates that using the first and last name as the primary key is not a good choice for a table that has many people listed in it. There are certainly many people in the world with the name John Jones. As you design the database table, think of a good choice for the primary key. One strategy is to create a unique number that is attached to each record as the primary key. This works quite well, except for the fact that you can have more than one record for the same individual and not know it.

Normalization

The rules of normalization are of paramount importance in the proper design of a relational database. Application of these rules keeps the database designer from falling into some traps that may cause problems. These rules are called normalization, and a database that complies is said to be in the normal form. There are three rules or normal forms that are usually applied. These are called the first, second, and third normal forms. What they lack in imaginative naming, they make up for in usefulness.

First Normal Form A table is in the first normal form if all columns in the table contain atomic values, which means a column contains only one item of data such as a phone number or child's name but never two items of the same type of data such as two or more phone numbers or the name of two or more people.

Second Normal Form A table is in the second normal form if it is in the first normal form and every value is dependent on the primary key value. An example is if you attempt to construct a table composed of first name, last name, and phone number, where the first and last name are used as the primary key. This will work until you attempt to record the second phone number for a person, such as the work and home phone numbers. If you choose to use the phone number rather than the first and last name as the primary key, you would run into the problem of two people having the same phone number. You can begin to see the problems with different database designs and how the normalization rules can help.


NOTE: The language of relational databases can be somewhat arcane and obtuse. The statement that a data item must depend on the primary key is one of these cases. What this means is that without the presence of the primary key, say my social security number, you wouldn't have my name in the record. The two, the SSN and the name, are linked together.

Third Normal Form A table is said to be in the third normal form if the table is in the first and the second normal forms and if every value depends on only the primary key. This is the most difficult to understand of the commonly used normal forms. An example is if you are constructing a table that includes pay rates. In your theoretical organization, all people occupying a particular job title are paid the same amount of money, regardless of their time at that job. In this case, the pay rate depends on the job title, not the person occupying the job. If you constructed a database to describe this organization, you would need to create a table of job titles and pay rates and a table of employees. You would place the Key of the job title table in the employee table as a foreign or cross reference key, not the pay rate itself. In other words, the pay rate goes with the job, not the individual. The individual occupies the job and the job title has a pay rate.


NOTE: There are actually five normal forms. The last two are rarely used and delve into mathematical theory that is easy to forget. I have never had occasion to use the fourth and fifth normal forms.

Foreign Keys

Why is this important? Because a well designed relational database does not contain redundant data. An item of information, such as a name, should occur only once in a database so that if the item of data (name) needs to be changed, it needs to be changed only one time.

For example, suppose an employee of your company named Dorothy Rubyslippers marries Cal Combatboots and decides to change her name to Dorothy Combatboots. If her name appears in multiple tables in your company's database, each and every one of the tables containing her name must be located and changed. If the database is properly constructed, however, her name would need to be changed in just one place.

If a table contains multiple instances of an item of information about an individual, such as multiple phone numbers, all the phone numbers should be moved into a separate table. The two tables then need a common item of information that can be used to cross-reference the two tables. As an example, look at Tables 33.2 and 33.3.

Table 33.2 Name Table

SSN Last Name First Name Sex
123-45-6789 Jones John Male
111-22-3333 Hansen Sue Female
012-43-8765 Adams Sue Female
888-22-1111 Adams Phil Male
666-55-4444 Smith Fred Male

Table 33.3 Phone Numbers Table

Key Foreign Key Area Code Phone No
1 123-45-6789 520 555-1212
3 012-43-8765 212 555-1212
9 111-22-3333 602 444-3434
25 888-22-1111 312 444-3434
25 123-45-6789 520 444-3434
72 666-55-4444 215 111-2222
73 012-43-8765 212 222-3333
81 666-55-4444 215 333-4444
88 888-22-1111 312 444-5555
89 111-22-3333 602 555-6666

In Tables 33.2 and 33.3, the social security number (SSN) is used as the common item of information to correlate the two tables because it is the only column of information that has a unique entry for each individual. In the Names table, the SSN is the primary key. In the Phone Number table, a number is assigned as the primary key. The Phone Number table contains the SSN as a foreign key. It is the key from the Names table.

These two tables exist in a parent/child relationship; the Name table is the parent of the child Phone Number table.

The best way to decide whether data should be included in a table or moved to a separate table is to determine if the data item depends on the primary key and there is only one of them. For example, if you create a table that contains data about a group of people, each person will have only one social security number, only one date and place of birth, and will be either male or female. Therefore, all this data can go in one table. However, when you look at other data associated with these individuals, such as the names of their children, the makes of their cars, and their telephone numbers, you realize many of them have more than one of these items. Therefore, each of these items should be in its own separate child table.

Referential Integrity

The referential integrity of a database concerns the parent/child relationship between tables. In the preceding example regarding the Names and Phone Numbers tables, there shouldn't be a record in the Phone Number table unless there is a parent record in the Name table. If you delete the record of an individual from the Name table, all of the associated records from the Phone number table should also be deleted. It is a violation of referential integrity if there are records in the Phone Number table that have no associated record in the Name table. In this situation, the records in the Phone Number table that have no parent records are called orphan records.

The relationships between tables are classified as one to many, one to one, many to one, and many to many. These relationships are important in the use of the database, which you will find out in the section concerning the Structured Query Language.


NOTE: In Microsoft Access, when the relationships between tables are set forth in a database, cascading updates and deletes can be specified. If the database engine does not provide this to be done automatically, then it needs to be done with program code. It is important that you understand the characteristics of the database engine you will be using.

Indexes

Because the rows of a table are not organized in any particular order, the RDBMS provides the capability to create one or more indexes. An index is a separate table that lists in order, either ascending or descending, the contents of a particular table with pointers to the records in the table. An index provides increased search speed when searching a database table for a specific record or set of records. Instead of reading the entire table, a search of the index is performed and the resulting list of pointers is used to collect the records from the base table.

Views

A view is a copy of a table or tables that doesn't exist except as a results set that is created when the view is queried. For example, imagine a table in a company's personnel records database that includes the home phone number of the employee. In this company, as in many, the home phone number is considered confidential information. If the company gives employees access to the personnel table as a company directory, they will have a problem.

The solution is to create a VIEW on the personnel table that doesn't include the column for home phone numbers and then give the employees access to the VIEW. This will protect the security of the data. Because access to a VIEW looks just like access to a table, no one will be able to tell the difference.

Speaking Structured Query Language (SQL)

Structured Query Language, or SQL (pronounced sequel), is a standard that was created, and is maintained, by ANSI (American National Standards Institute). Relational Database Management Systems (RDBMS) are SQL-compliant. This means that the database software system (also called the database engine) is able to accept a SQL statement, parse it, execute it, and return a results set successfully. The current SQL standard is SQL 92. Most RDBMSs are SQL 92-compliant. SQL, in concept, is intended to be English-like and not a cryptic code. An example of a SQL statement is the following:

SELECT * FROM TABLENAME

This SELECT statement would result in all of the columns and all of the rows of a table being returned as a results set. SQL is divided into two major sections: Data Definition Language (DDL) and Data Manipulation Language (DML).

DDL concerns the creation and alteration of the structure of the database. It includes CREATE and DROP statements. Applications usually don't permit the user to access the structure of the database.

The database application program usually concerns functions that exercise the DML. DML includes statements such as SELECT, UPDATE, INSERT, and DELETE. These are used to manipulate the data in the database, leaving the database structure alone.

SELECT Statements

The simplest SELECT statement will retrieve all the rows of a table in the order in which they occur in the table. As an example, here is a SELECT statement for the Name table.

SELECT * FROM Name

When the database engine executes this SQL statement, it will create a results set that looks like the following:

SSN           Last Name     First Name       Sex
123-45-6789     Jones          John          Male
111-22-3333     Hansen         Sue          Female
012-43-8765     Adams          Sue          Female
888-22-1111     Adams          Phil          Male
666-55-4444     Smith          Fred          Male
(5 row(s) affected)

The format of the SELECT statement is to start with the command, which is the SELECT *. This instructs the database engine to select all columns. Followed by FROM Names which tells the database engine which table or view that you want the rows selected from.

You won't always want all the columns from a table or all the rows. In such a case, you can choose the rows that you want to select and set a filter in the form of a WHERE clause to select only certain rows. Using the Name table as an example, a SELECT statement can be typed as follows:

SELECT First Name, Last Name FROM Name WHERE First Name = `Sue'

This shows the results set from this SELECT statement.

First Name     Last Name
Sue            Hansen
Sue            Adams
 (2 row(s) affected)

The WHERE clause filters the records to be selected. One other change you may want to make to your SELECT statement is to have it sorted. To do so, you add an ORDER BY clause, as follows:.

SELECT First Name, Last Name FROM Name 
     WHERE First Name = `Sue' ORDER BY Last Name

This shows the results set from the SELECT statement.

First Name     Last Name
Sue            Adams
Sue            Hansen
 (2 row(s) affected)

Notice that in the last two examples the columns are listed in the order they were listed in the SELECT statement, not the order they occur in the table.

There are many other clauses and refinements to clauses that can be added to your SELECT statements. It is not the intention of this chapter to present an exhaustive discussion of the SELECT statement. However, be assured that you can extract from the database the exact data you want in the exact form you want.


CAUTION: A common mistake made when beginning to work with databases is to assume that if the SELECT statement does not return any rows, there must have been an error. An empty results set is a perfectly valid response from a database engine. If you create a WHERE clause and no records meet the criterion, the results set will be empty.

Joins

Joins are the selection of data from more than one table using one SELECT statement. These SELECT statements can require much thought and planning to create them properly. For example, assume that you want to see the first name, SSN, and phone number from all of the people in the database who have the last name "Adams." To get this data, you will need to query two tables.

SELECT First Name, SSN, Phone No FROM Name, Phone Number 
     WHERE Name.Last Name = `Adams'
     AND Where Name.SSN = Phone Number.Foreign Key

The results set from this SELECT statement is shown in the following code:

First Name     SSN             Phone No
Sue            012-43-8765     555-1212
Sue            012-43-8765     222-3333
Phil           888-22-1111     444-3434
Phil           888-22-1111     444-5555
 (4 row(s) affected)

Notice in the SELECT statement that fully qualified names are used for the column names in the form of TABLE.COLUMN. This is used to remove any ambiguity about which column forms which table when the SELECT statement refers to more than one table.

INSERT Statements

INSERT statements are used to add new records to the database table. The following is an example of an INSERT statement that is used to add a record to the Name table:

INSERT INTO Name (SSN, Last Name, First Name, Sex)
    VALUES (`543-22-1234', `Jones', `Tom', `Male')

Notice that the column names are listed in a comma-separated list, enclosed in parentheses. This is followed by the VALUES clause, which has the values for each column in a comma- separated list, again enclosed in parentheses. Likewise, notice that the values are in the same order as the column names. Finally, notice that all of the values are strings and that they are enclosed in single quote (') marks.

The results set from an INSERT is somewhat different than one from a SELECT statement. No row is requested to be returned. Instead, you receive a notification that the INSERT was successful, in the following form:

(1 row(s) affected)

UPDATE Statements

The UPDATE statement is used to change existing data in a table. The UPDATE statement should always contain a WHERE clause. The WHERE clause selects the rows to be updated. As per an earlier example, assume that the hypothetical employee, Dorothy Rubyslippers, gets married to Cal Combatboots and decides to change her name to Dorothy Combatboots in the company records. Table 33.4 shows the table before the update action is applied.


CAUTION: UPDATE statements are inherently dangerous, as you will see in this section, so make sure that you understand the importance of the WHERE clause.

Table 33.4 Name Table Before a Change

SSN Last Name First Name Sex
123-45-6789 Jones John Male
111-22-3333 Hansen Sue Female
012-43-8765 Adams Sue Female
231-99-2222 Rubyslippers Dorothy Female
888-22-1111 Adams Phil Male
666-55-4444 Smith Fred Male

The UPDATE statement appears as follows:

UPDATE Name SET Last Name = `Combatboots' WHERE SSN = `231-99-2222

This produces a results set of:

(1 row(s) affected)

Now a look at the importance of the WHERE clause in the UPDATE statement. If an UPDATE statement without a WHERE clause is used, such as

UPDATE Name SET Last Name = `Combatboots'

the results set informs you:


(6 row(s) affected)

But the intent is to update only one row. What went wrong? A look at Table 33.5 provides an explanation.

Table 33.5 Name Table With an Improper Update

SSN Last Name First Name Sex
123-45-6789 Combatboots John Male
111-22-3333 Combatboots Sue Female
012-43-8765 Combatboots Sue Female
231-99-2222 Combatboots Dorothy Female
888-22-1111 Combatboots Phil Male
666-55-4444 Combatboots Fred Male

Because the row to be updated is not limited with a WHERE clause, all of the rows of the table were updated. Most actions with a database do not have an undo function. If you imagine a table with 10,000 rows instead of 6, you can appreciate that UPDATE statements not well thought out can produce considerable damage.

DELETE Statements

The DELETE statement is as dangerous as it is simple. A DELETE statement without a WHERE clause deletes the entire contents of a table. As an example, assume that Dorothy Combatboots quits the company and it is now time to delete her record in the Name table. The DELETE statement will be as follows:

DELETE FROM NAME WHERE SSN = `231-99-2222'

The results set will be:

(1 row(s) affected)

A look at Table 33.6 shows that the record for Dorothy Combatboots nee Rubyslippers is gone, with no evidence that it was ever there.

Table 33.6 Same Table After a Deletion

SSN Last Name First Name Sex
123-45-6789 Jones John Male
111-22-3333 Hansen Sue Female
012-43-8765 Adams Sue Female
888-22-1111 Adams Phil Male
666-55-4444 Smith Fred Male

Examining the Use of Client/Server Design

Any time two computers are involved in the mutual performance of executing an application, with each performing a different function, you are undoubtedly looking at a client/server application. Many definitions of client/server are used. A definition of client/server application is an application that has a client interface and that accesses data on a remote server. The work is distributed between the client system and the remote server system, based on the capabilities of the client and server software applications. Client/server systems usually are efficient because network traffic is minimized and each portion of the application is optimized for a particular function.

Client/server applications function over any type of network, functioning at any speed. These variables are factors that affect the performance of the application, but that do not affect whether the application works.


NOTE: Both the client and the server must use a common communications protocol and a common data format.

The essence of a client/server application can be viewed by looking at a database application. Most relational database management systems (RDBMS), such as Microsoft SQL Server, provide a database engine that manages the data but has little or no user interface. (Exceptions are the desktop databases such as MS Access.) The interface to the RDBMS is through command line functions. When working with this type of RDBMS, the developer needs to create the user interface application.

In this instance, the user interface application, the client, formulates a request for data that is transmitted to the system running the database, the server. This request for data will be in the form of a SQL statement, such as a SQL query. The relational database engine will execute the query, create a results set, and transmit the results set back to the client. Figure 33.1 presents this relationship in a diagram.

FIG. 33.1
Client/server actions are always initiated by the client.

Two-Tiered Client/Server Applications

The client/server diagram shown in Figure 33.1 is called a two-tiered client/server because the client communicates directly with the server, with no intermediary. The limitation of this model is that it is not easily scalable. If the server becomes over taxed by client traffic, the usual solution is to upgrade the server hardware to a faster processor with more memory. However, there is an upper limit to how fast the processor can be.

Multi-Tiered Client/Server Applications

It is also possible to have three or more tiers in the client/server design, which is frequently used in Internet applications. This increases the scalability of the application. An example of this multi-tiered approach can be seen with a Web page that accesses a database on the server. Frequently, the database is on a different server than the Web server. This allows the distribution of the load over multiple systems. It also creates scalability. If the database activity becomes a bottleneck for the application, the database activity can be distributed over multiple servers. If the Web server becomes the bottleneck, the Web server can be distributed over multiple servers. Figure 33.2 shows a diagram of multiple Web servers connected to multiple database servers.

FIG. 33.2
The Domain Name Server (DNS) is providing Round Robin service for the client attachments to the Web servers.

There are other uses of the multi-tier client/server model, including the following two very useful designs. The first design has a middle tier that provides an Online Analysis Processing data warehouse that is extracted from the base layer of operational databases. The second design makes use of a middle tier to enforce business rules. This separate layer for business rules makes the maintenance of the business rules much easier and less disruptive.

Understanding Thin Clients versus Thick Clients

The terms thin client and fat or thick clients are being used with great frequency. The IS world is excited over the prospect of having thin clients. The advent of the thin client is not a new idea. It is a return to the model of the central computer surrounded by "dumb" terminals.

An understanding of the differences between thin and fat clients will provide some insight into the advantages and disadvantages of each.

Client/server implies a division of labor between the client and server systems. The usual division is that the server supplies all of the data and the client displays the data and performs manipulation of the data for the user at the user's system.

A thin client is one that provides little to the relationship except the capability to display the data transmitted by the server. The old "dumb" terminal, such as a 3270 or DEC VT 100, are examples of a thin client.

A fat client is one that provides extensive logic for the manipulation of the data transmitted by the server. This is the workstation paradigm in which a PC is the client and the data is transmitted for the use of the client and then sent back to the server when the client has finished the task. The server is little more than a data repository.

Viewing this in light of Internet technologies and, in particular, World Wide Web technology, the Web browser is the client and the Web server is the server. In this model, the Web browser is the thin client that requires little or no maintenance for the client/server application to function. Even when client scripting is used, the browser is processing code that is transmitted each time the application is accessed. New versions of the script do not need to be installed on the client.

The primary concern in this scenario is the capability of the Web browser to support various technologies. Not all Web browsers are created equal. The capabilities of Web browsers vary from LYNX, which is a text-only browser, to MS Internet Explorer which supports almost everything. Web browsers vary in their support for different versions and features of HTML. Most browsers support the current 3.2 version of HTML.

In addition to the varying levels of support for HTML, there are varying levels of support for scripting languages. Netscape Navigator supports JavaScript but not VBScript. MS IE 3.0 and later supports JavaScript and VBScript. Add to this mix the complication of ActiveX support, which is present in MS IE 3.0 and later, but not Netscape Navigator without a third-party plug-in.

All of these issues, such as script language and ActiveX support, are moving the thin client of the Web browser toward a fatter client.

The most essential item to consider when analyzing Web browsers as thin clients is whether you are in an environment in which you have control over the Web browser used as a client. If you are not in such an environment, then you need to design to the lowest common denominator and use server logic to provide alternative interfaces for different browsers.

Understanding the ADO Object Model

An understanding of the ADO Object Model will be of great assistance in working with the various components. It will seem complex and overwhelming at first, but it will all fall into place as you gain experience using it. The ADO Object Model is composed of six primary objects, which are shown in Figure 33.3.

FIG. 33.3
The Errors, Parameters, and Fields objects are part of a collection.

Closer examination of each of these objects will provide a greater understanding of them in preparation for building programs using ADO.

Connection Object

Connection objects can be created independently of any other previously defined object. The Connection object represents a specific unique session with a particular data source. The Connection object contains the following two collections:

The Connection object contains the following six methods:

The Connection object also contains nine properties:

http://www.microsoft.com/ado An excellent source of information on ActiveX Data Objects is available from Microsoft. This site will enable you to keep up to date on this rapidly developing technology. There are also excellent help files available for free download.

Error Object and Errors Collection

An Error object is generated each time an error is encountered in an ADO operation. The Error object is part of an Errors Collection. The Error object contains seven properties:

The Errors collection has one method, the Clear method, which removes all of the Error objects in the collection. The Errors collection also has two properties: Count, which indicates the total number of Error objects in the collection, and Item, which returns a specific Error object.

Command Object

A Command object is the definition of a specific command, such as a SQL statement or stored procedure that you execute on a specific data source. The Command object has two collections:

The Command object also contains two methods:

The Command object has five properties:

Parameter Object and Parameters Collection

A Parameter object represents a parameter or argument associated with a Command object that is based on a parameterized query or stored procedure. The Parameter object contains one method, the AppendChunk method, which is used to append a large text or binary file to a parameter.

The Parameter object contains eight properties:

The Parameters collection contains three methods:

The Parameters collection also contains the two properties of Count and Item.

Recordset Object

You do not need to create a Connection object to create a Recordset object. This is accomplished by passing a connection string with the Open method. ADO creates a Connection object, but it isn't assigned to an object variable. If you are opening multiple Recordset objects over the same connection, you must create and open a Connection object. This will assign the Connection object to an object variable. If you do not use this object variable when opening your Recordset objects, ADO creates a new Connection object for each new Recordset object, even when you pass the same connection string.

A Recordset object represents the entire set of records from a base table or the results of an executed command. At any time, the Recordset object refers only to a single record within the set as the current record.

The Recordset object contains two collections:

The Recordset object contains nineteen methods:

The Recordset object contains seventeen properties:

Field Object and Fields Collection

Field objects represent columns of data with a common data type. The Fields collection contains all stored Field objects of a Recordset object. The Field object contains one collection, the Properties collection. The Field object contains two methods:

The Field object contains ten properties:

The Fields collection contains one method, the Refresh method, which updates the objects in the Fields collection to reflect Field objects available from, and specific to, the provider.

The Fields collection contains the Count and Item properties.

Building an Example Using ADO on the Web

Before you can build a Web-based example, it is necessary to cover a couple of background subjects. If you are already familiar with Active Server Pages and VBScript, you can skip the next two sections covering these topics. An understanding of HTML is also useful in reading this section.

See "Using VBScript with the HTML <FORM> Elements and Intrinsic Controls," Chapter 35

See "Using a Java Applet in a Web Page," Chapter 36

See "Programming Active Server Pages," Chapter 37

Active Server Pages

Active Server Pages are an ISAPI (Information Server Applications Program Interface) filter and Active Server processing that enables server-side script processing for Microsoft Internet Information Server for Windows NT 4.0 Server, Peer Web Services for Windows NT 4.0 Workstation, and a Personal Web Server for Windows 95. When a request for a file is received by the Web server, the ISAPI filter checks the file extension. If the extension is .ASP, the Active Server parses the page and processes any server-side script that is present, and then generates the HTML page that is sent to the client. The server-side script can be either VBScript or JavaScript (actually, JScript, which is Microsoft's implementation of JavaScript). The server-side script is distinguished from client-side script in the Web page in one of two ways, which are explained next.

If the script is enclosed in <% %>, the script is processed by the Active Server. If the script is enclosed in <SCRIPT> </SCRIPT> HTML tags, the opening tag must include the RUNAT parameter, as in <SCRIPT LANGUAGE="VBScript" RUNAT="Server">, or the Active Server will see it as destined for the client and pass it on to the HTML page being generated for the client.

VBScript

VBScript is a subset of Visual Basic. It is actually more like VBA (Visual Basic for Applications). There are a couple of characteristics of which you should be aware. VBScript is a loosely typed language. A variable does not need to be declared before it is used. All variables are of the data type Variant. The VBScript Variant data type has subtypes that are very similar to the VB data types. There are VBScript functions that will convert between subtypes. You can still generate the Type Mismatch error in VBScript. Another characteristic of VBScript that is important to remember is that the error processing must be done in line. You will need to deal with errors at the point at which they are generated. Because you are used to Visual Basic, you will find VBScript very friendly territory. Chapters 35, "Internet Programming with VBScript," and 36, "Programming Web Pages," will give you more details.

Opening a Connection

For this example, a SQL Server database was created with one table. The table contains three columns. The first column is the key, which is an identity or auto-number column. When a record is added, the database engine generates the next key. The second column is the first name, and the third column is the last name. The database contents consists of the names of the first five presidents of the United States. The database intentionally is kept simple so that you can concentrate on the ADO objects.

The first task that needs to be performed is to create an ODBC DSN on the system that is the Web server. The DSN needs to point at your database. The DSN named PlatVB5 was created already.

Next, an HTML page is required with an .ASP extension which contains the VBScript that will access the database. This .ASP page is shown in Listing 33.1.

Listing 33.1 Default.ASP--The Active Server Page that Will Access the Database

<%@ LANGUAGE="VBSCRIPT" %>
<HTML>
<HEAD>
<TITLE>ActiveX Data Object (ADO)</TITLE>
</HEAD>
<BODY BGCOLOR=#FFFFFF>
<H3>ActiveX Data Object (ADO)</H3>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=PlatVB5;SERVER=SoftCoyote;UID=sa;PWD=password"
Set RS = Conn.Execute("SELECT * FROM MyTable")
%>
<P>
<TABLE BORDER=1>
<TR>
<% For i = 0 to RS.Fields.Count - 1 %>
     <TD><B><% = RS(i).Name %></B></TD>
<% Next %>
</TR>
<% Do While Not RS.EOF %>
     <TR>
     <% For i = 0 to RS.Fields.Count - 1 %>
          <TD VALIGN=TOP><% = RS(i) %></TD>
     <% Next %>
     </TR>
     <%
     RS.MoveNext
Loop
RS.Close
Conn.Close
%>
</TABLE>
</BODY>
</HTML>

Before you begin an analysis of the code, Figure 33.4 shows the Active Server Page when it is processed by the Active Server, and the resulting HTML Page displayed by the Web browser.

FIG. 33.4
If you choose View, Source, you will see that the HTML at the client is not the same as was processed by the Active Server.

When you look at Listing 33.2, you will see the actual HTML generated by the Active Server and sent to the Web browser.

Listing 33.2 Default.HTM--This HTML Page Exists Only on the Web Browser

<HTML>
<HEAD>
<TITLE>ActiveX Data Object (ADO)</TITLE>
</HEAD>
<BODY BGCOLOR=#FFFFFF>
<H3>ActiveX Data Object (ADO)</H3>
<P>
<TABLE BORDER=1>
<TR>
     <TD><B>Key</B></TD>
     <TD><B>Fname</B></TD>
     <TD><B>Lname</B></TD>
</TR>
     <TR>
          <TD VALIGN=TOP>1</TD>
          <TD VALIGN=TOP>George  </TD>
          <TD VALIGN=TOP>Washington  </TD>
     </TR>
     <TR>
          <TD VALIGN=TOP>2</TD>
          <TD VALIGN=TOP>John  </TD>
          <TD VALIGN=TOP>Adams  </TD>
     </TR>
     <TR>
          <TD VALIGN=TOP>3</TD>
          <TD VALIGN=TOP>Thomas  </TD>
          <TD VALIGN=TOP>Jefferson  </TD>
     </TR>
     <TR>
          <TD VALIGN=TOP>4</TD>
          <TD VALIGN=TOP>James  </TD>
          <TD VALIGN=TOP>Madison  </TD>
     </TR>
     <TR>
          <TD VALIGN=TOP>5</TD>
          <TD VALIGN=TOP>James  </TD>
          <TD VALIGN=TOP>Monroe  </TD>
     </TR>
</TABLE>
</BODY>
</HTML>

First look at the line of VBScript that creates the Connection object:

Set Conn = Server.CreateObject("ADODB.Connection")

This creates a connection object on the server. Next, this line opens the connection object:

Conn.Open "DSN=PlatVB5;SERVER=SoftCoyote;UID=sa;PWD=password"

The Open method is called on the Connection object Conn and the parameters of the DSN, UserID, and password are passed. Next, the Recordset object is created by calling the Execute method on the Connection object. The SQL statement is passed as the parameter for this method.

Set RS = Conn.Execute("SELECT * FROM MyTable")

You now have a results set as a Recordset object at the Active Server. This Recordset object is complete with the Fields collections, which are used in VBScript that is used to create the table with the values.

The code in Listing 33.3 shows the VBScript that creates the table and manipulates the Recordset object's Fields collection to get the values.

Listing 33.3 Default.ASP Snippet--This VBScript Creates the Table in the Resulting HTML Page Sent to the Web Browser

<% Do While Not RS.EOF %>
     <TR>
     <% For i = 0 to RS.Fields.Count - 1 %>
          <TD VALIGN=TOP><% = RS(i) %></TD>
     <% Next %>
     </TR>
     <%
     RS.MoveNext
Loop

Finally, the Recordset and Connection objects are closed with the Close method.

RS.Close
Conn.Close

Adding a Record

To add a record to a table, you first need to collect the data that you want to add. This is done with an HTML form, the contents of which are shown in Listing 33.4.

Listing 33.4 InsertRec.HTM--This HTML Form Collects the Data and Transmits It to the .ASP Page

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Developer Studio">
<META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">
<TITLE>Document Title</TITLE>
</HEAD>
<BODY BGCOLOR="WHITE">
<FORM ACTION="InsertRec.ASP" METHOD=POST>
<TABLE>
     <TR>
          <TD> FIRST NAME </TD>
          <TD> LAST NAME </TD> 
     </TR>
     <TR>
          <TD><INPUT TYPE=text SIZE="24" NAME=fname></TD>
          <TD><INPUT TYPE=text SIZE="24" NAME=lname></TD> 
     </TR>
     <TR>
          <TD><INPUT TYPE=SUBMIT VALUE="Submit"></TD>
          <TD><INPUT TYPE=RESET VALUE="Reset"></TD>
     </TR>
</TABLE>
</BODY>
</HTML>

The INPUT tags create the two text boxes in which the data is entered. Figure 33.5 shows the Web page with the data ready to be submitted to the Active Server for insertion into the database. In Listing 33.10, notice the line of code:

<FORM ACTION="InsertRec.ASP" METHOD=POST>

This causes the page Insert.ASP to be called.

Figure 33.6 shows the results of the record being submitted. Two pieces of information have been placed on the form: first, a print of the SQL statement used to insert the record, and second, a message that indicates success.

FIG. 33.5
Click the Submit button to send the data to the server.

FIG. 33.6
Because there is no results set from an INSERT statement, a message will tell the user that the operation was successful.

In Figure 33.7, you can see that the record was added to the database.

The contents of the .ASP page that performed the update appear in Listing 33.5.

FIG. 33.7
The table in the ASP page is dynamic and will list all records.

Listing 33.5 InsertRec.ASP--The HTML and VBScript that Perform the Insert Operation

<%@ LANGUAGE="VBSCRIPT" %>
<HTML>
<HEAD>
<TITLE>ActiveX Data Object (ADO)</TITLE>
</HEAD>
<BODY BGCOLOR=#FFFFFF>
<H3>ActiveX Data Object (ADO)</H3>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=PlatVB5;SERVER=SoftCoyote;UID=sa;PWD=password"
%>
<%
Dim SQLState
%>
<%
SQLState = "INSERT INTO MyTable (Fname, Lname) VALUES (`"
%>
<%
SQLState = SQLState & Request.Form("fname")
%>
<%
SQLState = SQLstate & "`, `"
%>
<%
SQLState = SQLState & Request.Form("lname")
%>
<%
SQLState = SQLstate & "`)"
%>
<%
Response.Write SQLState
%>
<%
Set RS = Conn.Execute(SQLState)
%>
<P>
Your New Record Has Been Inserted Into The Database
<%
Conn.Close
%>
</TABLE>
</BODY>
</HTML>

First the connection is opened, and then the SQL statement is built and executed. As you can see, ADO is very easy to use.

Creating a Non-Web ADO Example

When creating an example that will work in Visual Basic, you will find the syntax very much the same. Listing 33.6 is a simple VB program that opens a connection and creates a Recordset that can then be walked with the MoveNext and MoveFirst methods.

Listing 33.6 PlatVB_5_28_1.FRM--The Visual Basic Code Required for a Simple ADO Connection

Option Explicit
Dim RS As Recordset
Public Sub Command1_Click()
    RS.MoveFirst
    Text1.Text = RS.Fields(1)
    Text2.Text = RS.Fields(2)
End Sub
Private Sub Command3_Click()
    RS.MoveNext
    Text1.Text = RS.Fields(1)
    Text2.Text = RS.Fields(2)
End Sub
Public Sub Form_Load()
    Dim conn As ADODB.Connection
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "DSN=PlatVB5;SERVER=SoftCoyote;UID=sa;PWD=password"
    Set RS = conn.Execute("SELECT * FROM MyTable")
    Text1.Text = RS.Fields(1)
    Text2.Text = RS.Fields(2)
End Sub

Notice that the following three lines of code are virtually identical with the Web example:

Set conn = CreateObject("ADODB.Connection")
conn.Open "DSN=PlatVB5;SERVER=SoftCoyote;UID=sa;PWD=password"
Set RS = conn.Execute("SELECT * FROM MyTable")

The only difference is the Server object in the first line before the CreateObject method is called. Figure 33.8 shows the VB program with the command buttons used for walking the results set.

FIG. 33.8
This example uses the same database and DSN as the Web example.

From Here...

In this chapter, you have explored the ActiveX Data Objects. The ADO provide a simple, powerful, and flexible interface to relational databases through the use of an ODBC DSN. Exploring the following chapters will provide further insight into some of the issues involved with database and client/server development. Mastery of database access is one of the most important skills that a programmer can possess:



Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.