Beginning ASP Databases

655 Pages·1999·21.9 MB·English

by  John Kauffman

wrox 1
Beginning ASP Databases

John Kauffman
with
Kevin Spencer and Thearon Willis

Wrox Press Ltd.

Beginning ASP Databases
© 1999 Wrox Press

All rights reserved. No part of this book may be reproduced, stored in a retrieval system or transmitted in any form or
by any means, without the prior written permission of the publisher, except in the case of brief quotations embodied in
critical articles or reviews.

The author and publisher have made every effort in the preparation of this book to ensure the accuracy of the
information. However, the information contained in this book is sold without warranty, either express or implied.
Neither the authors, Wrox Press nor its dealers or distributors will be held liable for any damages caused or alleged to
be caused either directly or indirectly by this book.

First Published 1999

Latest Reprint October 2000

Published by Wrox Press Ltd,

Arden House, 1102 Warwick Road, Acocks Green,

Birmingham, B27 6BH, UK

ISBN 1-861002-7-26

Trademark Acknowledgements

Wrox has endeavored to provide trademark information about all the companies and products mentioned in this book
by the appropriate use of capitals. However, Wrox cannot guarantee the accuracy of this information.

Credits

Authors Managing Editor
John Kauffman Joanna Mason
Kevin Spencer
Thearon Willis Development Editor
Sarah Bowers
Additional Material
Chris Blexrud
David Buser Technical Editors
David Sussman Joanna Mason
Chris Ullman Dianne Parker
Andrew Polshaw
Technical Reviewers
Burt Abreu Design/Layout
Chris Blexrud Tom Bartlett
David Buser Mark Burdett
Charles Caison Jnr Jonathan Jones
Robert Chang John McNulty
Michael Corning William Fallon
Steve Danielson David Boyce
wrox 2
Marco Gregorini
Scott Haley
Rob Hebron Figures
Jon Jenkins David Boyce
Manohar Kamath William Fallon
Pieter Reint Siegers Kort
Robert MacDonald Cover
Sam MacDonald Chris Morris
Dave Navarro Sr
Geoff Pennington
John Timney Index
David Williams Alessandro Ansa

About the Authors

John Kauffman

John Kauffman was born in Philadelphia, the son of a chemist and a nurse. John's family of six shared daily brain
teasers and annual camping forays that covered most of the 50 United States. After jobs weeding strawberry patches,
bussing tables, running spotlights for rock and roll concerts, touring North America with a drum and bugle corps,
prematurely leaving three colleges, stuffing voles for a mammologist, packing boxes of rat poison, tarring roofs,
delivering drapes in New York City, laboring in a candy factory, teaching canoeing, driving a forklift, studying tiger
beetles in the Chihuahua desert, managing a picture framing factory, coaching a youth yacht racing team and
volunteering as a human guinea pig for medical research, John (to the great relief of all around him) earned a pair of
degrees in the sciences from The Pennsylvania State University and appeared to settle down. He then conducted
research for Hershey Foods in the genetics of the cacao tree and the molecular biology of chocolate production.
Subsequently he moved to Rockefeller University where he identified, cloned and sequenced DNA regions which control
the day and night biochemical cycles of plants.

But science didn't hold a candle to a woman he met in 1985 and married. Since then he has followed Liz in her career
as a diplomat across four continents. They moved to Tanzania in 1986 and John began work with computers and
business management in an assistance program for subsistence-level farmers. In 1990 they moved to Taiwan and then
mainland China where John provided software training services to multi-national corporations and the diplomatic
community in Beijing, Hong Kong, Shanghai and Sichuan. During the graduation banquet for one course he was
honored by his students with a special entree of snake bile, frog skin and turtle meats.

John and Liz performed their most significant genetics experiments in 1988 and 1990 with the production of their
children Sylvia and John. Growing up in Africa and China, they are doing well hashing through another generation's
worth of brain teasers and camping trips.

John now divides his freelance consulting time evenly between teaching, writing and programming, primarily in the
areas of Visual Basic, Word macros, Access and Access Programming, and ASP. John is available for contract training in
Asia, Europe and North America by contacting [email protected].

This book is dedicated my parents in appreciation of their tremendous effort raising my siblings and me: to my father,
who spent so much of his time helping us to understand science and mathematics; and to my mother, who invested so
much of herself in facilitating our far-reaching interests in science, music and business. But their greatest gift was
helping us to understand how other people think, feel and learn, and to value what others have to say. That gift is the
key to my success. It is my parents' lessons that allow me to listen to my students and clients, then work with them to
achieve their goals.

Kevin Spencer

I started programming in C in the early '90's and wrote a number of shareware programs for DOS, most of which were
Door programs for BBSs. In the process, I learned to construct relational databases in C, and later worked with
Microsoft Visual FoxPro and Access. As Internet Database Connectivity technologies began to emerge from Microsoft, I
learned them as well, and several years ago, started my business, "Site Design by TAKempis," which specializes in
Internet Database application programming with ASP/ADO. Microsoft awarded me the MVP award in 1997. I have
written articles about Microsoft FrontPage and ASP/ADO for several online magazines, including Wrox's ASP Today.

Thearon Willis

A senior consultant with 19 years of IT experience, Thearon is currently a consultant for an International Insurance
company providing Web programming services. His Web development tools of choice are ASP, Dynamic HTML, Visual
Basic and SQL Server. Thearon lives in Charlotte, North Carolina with his wife and daughter.
wrox 3
To my wife Margie for her patience and understanding while I write yet another book, I love you very much.

Introduction

Microsoft created Active Server Pages (ASP) to sew together a group of technologies for creating modern web sites,
which can intelligently interact with the user at the front end and with servers and datastores at the back end. Among
these technologies is ActiveX Data Objects (ADO), which allows easy, powerful and robust control of technologies that
access datastores. This book 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 author's proven teaching techniques and the
wrox 4
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.

What Is This Book About?

This book explains, demonstrates and applies techniques that lie at the intersection of the two great tools of the
Information Age. One tool, the database, is relatively old. The other, the World Wide Web, is quite new. ASP and ADO
together provide the ability to combine these two to maximum effect.

Even the most computer-illiterate managers realize that business as we know it today could not exist without databases
to hold information on customers, orders, inventory, accounts, payroll and every other facet of commercial activity.
Many of the great advances in productivity since 1960 have been possible because of the speed and accuracy that
databases provide for business information. However, until recently there has not been a convenient way to provide
universal access to this information. For example, it was difficult for a worker while on a trip to Buenos Aires to register
her overtime hours with the payroll department back at headquarters in Orlando.

The newer technology is the World Wide Web, a universal (well, planetary) method of exchanging and viewing
information. Since the explosion of Web access that started in the mid-1990s, virtually all computer users are now able
to view the information offered on the Internet. However, it has not always been easy to provide customized pages,
designed to display database information specifically useful to an individual user. For example, a person interested in
buying a boat saw a static page with a generic picture of the boat; it was difficult for a web programmer to create a
dynamic page displaying an image of the boat, configured with the sailor's specific requests regarding sail plan, colors
and deck fittings.

ASP and ADO make it relatively easy to bring together these two great information technologies of our time, to provide
the capacity to use the data of business through the Web. With ASP-ADO the traveling technician can now use any
computer in any country, with any browser connected to the web, to send in her overtime hours directly to her
company's payroll database. The sailor can not only see his prospective purchase in the color and sail plan of his
desires, but docked in front of palm trees or pine trees depending on his zip code, with a list of the dealers nearest to
the sailor and the dealers' current inventories. The web page can even suggest the next more expensive yacht if it is
still within the financial grasp of an average citizen in the sailor's zip code.

Why Is This Book Different?

There are many books that present the ideas of ASP and a dozen more that focus on ADO. Some are quite advanced
and assume an existing knowledge of other techniques. But to date no other book has been written with the intention of
coaching beginners through their first uses of ADO on ASP pages.

The first word in the title of this book is Beginning, thus setting the tone for the tome. The theory is covered, but then
there are also copious examples for you to study. An important role of any teacher is to select which out of all the
software's features to teach, and in what order. Considerable effort has been made in this book to present material in
the best way for it to be learned; not alphabetically or divided by object or according to historical precedent. In this
book we start by stating a business goal, then present the minimum techniques to achieve that goal. After we have the
basics working we add on the most commonly used parameters to enhance that technique.

Written By An Experienced Teacher

In many ASP books the code is simply demonstrated, and the assumption is that the reader can learn why code was
written just by looking at the final result. I think that is a big jump for most beginners. Although I am also a
programmer, I am primarily a teacher, with over a thousand students in classrooms and on-line ASP courses. I keep a
notebook of students' questions and where students make mistakes in exercises. This body of knowledge is wrapped up
and presented in this book in ways that are most useful for the classroom or home-student.

My goal is not to write the book that presents the most depth or breadth of ASP and ADO solutions. Rather, my goal is
to do the best job of explaining how to build your first fifty pages using ADO with ASP. After you master this book and
go on you will find additional ways to perform tasks and additional features in the software. But those discoveries are
for the future. The purpose of this book is to focus your attention on what you must learn to get started and to insulate
you from the sea of non-essential material which can overwhelm a beginner.

Exercises

Each chapter ends with one or more exercises. Keep your solutions on file as they can become a good portfolio when
you apply for a job or make a presentation to a client. Furthermore, you know the code works and you can cut and
paste it into the pages of future sites.

Quizzes
wrox 5

It is only the poor students that groan about quizzes. Most of my pupils are glad to check their own mastery of the
material and find places where they may have missed a point. These questions are specifically written as a check on the
topics where students usually make mistakes. In the early chapters there is a focus on understanding confusing
vocabulary or tricky theory while in the later chapters the questions involve troubleshooting code.

Detailed Code Dissection

Try It Out

How It Works

The chapters in this book are sprinkled with mini-exercises called Try It Outs. Each one states an objective and then
follows that with the answer in code and a screen shot. Immediately following is a How It Works section that dissects
the code line-by line and explains the syntax, choice of techniques, purpose of variables, etc.

More Explanations

There is far more explanation of code in this book than in many other books. My assumption is that if you understand
some code and read the explanation you will confirm your thinking. If you don't understand some code then the
explanation will straighten it out for you. Many professional programmers will snort that some of these explanations are
not needed, the ideas are trivial. But students give the same paragraphs great comments, 'This was the first time this
syntax was explained carefully and thoroughly enough for me to really get it.'

I also understand that not everybody (including myself) reads books from cover to cover. Sometimes I explain a vital
idea more than once if there are two places where I think folks might jump into the middle of the book. Feel free to skip
over things you already know. In many chapters I've noted when we are covering topics that may be familiar to some
readers so at these places I've tried to give you a note on where you could skip to, to get right into the more advanced
material.

Lists of Common Errors

Another feature that sets this book apart from others is the focus on troubleshooting problems you may encounter. I've
included many lists of common errors drawn from my records of students' failed pages. I suggest that you read through
them prior to starting to code a page to give yourself a reminder of problem spots. If your page works then give
yourself a pat on the back. But if your page fails then try checking your code against each item on the list. I'll bet in at
least 80% of the cases you'll find your problem.

Clarifications

In almost every class on ASP the same questions arise. I have answered many of these in additions to the main text.
These can clarify how the same word is used with more than one meaning, provide help on topics that the main text
assumes you already understand, or explain alternative ways of doing a task.

Useful Examples

When selecting a book at the shop (or on-line) I look closely at the examples. Can I re-use them in my business? If the
examples are artificial and produce nothing more then 'Hello world!' I put the book back on the shelf. This book gives
you solutions to real-world problems. I use a database to support the membership of a sailors' organization and web
pages to achieve the expected goals of such a club, such as providing up to date information and registering boats. You
can use the examples in this book to solve problems or perform tasks that you might be faced with.

Who Is This Book For?

The programming community generated a tremendous response to Beginning ASP 2.0. We thank you. We fitted two
chapters and one case study on data access into that book but didn't have room for the dozen more chapters that I
wanted. So here it is, the logical next step to Beginning ASP 2.0 for those of you who plan to connect your sites to
datastores.

This book is also well suited to people from the database community who now want to implement their solutions with a
Web interface. These folks have plenty of experience with database programming, but want to translate that knowledge
into ADO solutions in ASP pages.
wrox 6
Many web designers are from non-programming backgrounds, with an education in graphics design or word processing.
These people can benefit from the book by learning how to take their visually appealing sites and giving them the
business power of database connectivity. Folks in this group would probably do well to study the Beg ASP book first.

Lastly, I get students who are interested in computers but are not sure what to study. I predict that there will be strong
growth in demand for programmers comfortable with ADO and ASP, so building a portfolio of ASP pages can only help
your resume.

What Software Do You Need?

In order to practice writing web pages you need four types of software. First is a browser. Actually, most web designers
have several browsers so they can test their pages on both Internet Explorer and Netscape and for the last few versions
of each.

Second, you need a page editor. This can be as simple as Notepad or as powerful as Visual InterDev. The simpler
editors such as FrontPage do a fine job with HTML but lack tools to speed the development of database connections.

Third, you need software to hold and serve your pages. This software must be capable of processing the ASP scripts, so
your two main choices are IIS running on NT, or PWS running on Windows 95/98 or NT Workstation. This topic is
discussed in detail in chapter 2. In short, if you have permission to test pages on a site with ASP capability then you are
all set. If not, you can set up a Personal Web Server (PWS) on your own Windows machine and do just about
everything in this book.

Fourth, you will need some type of database management software. In this book we mostly use Access because most
people have it or can easily get it and set it up. Since Access databases are file-based, they are very easy to download
from the WROX site, make backups, and make changes by flipping into the Access software. The second database
management system we use in the book is MS SQL Server, mainly to illustrate differences when moving to full-powered
systems for deployment.

What Knowledge Is Assumed?

Three skill sets are needed before studying the concepts of this book. The first is to be comfortable with the basics of
HTML. You should have built many static pages by now using tags such as <FONT>. Furthermore, this book covers
many pages of code that build tables and gather information from forms. All of this is generated, of course, using ASP
pages. A surprisingly high percent of the failed pages from my students is caused by simple HTML mistakes. Granted,
some of those tags must be built from difficult concatenations, but students should be able to spot <TABLE> and
<FORM> tag errors in finished pages. These topics are covered well in Beginning Active Server Pages (ISBN 1-861001-
34-7). If you have no problems understanding the demonstrations and exercises in chapters 3, 5 and 10 of Beg ASP
then you are ready to study implementing ADO in ASP. It is not important which page editor you use. I find Microsoft's
Visual Interdev to be the best, but Allaire's HomeSite, Adobe PageMill and others are fine and even Notepad will do.

Second, this book will only be accessible to those who have some experience with programming. The scripting
languages (VBScript or JScript) hold together the ASP and ADO objects. This book uses VBScript so you will be
particularly well-prepared if you have worked in the VB family such as AccessVBA, Word macros or VB itself.

Third, a basic understanding of database theory is an advantage in learning from this book. We provide some example
databases for this book, available for download, which are discussed in Appendices A to C. These appendices describe
the structure of the databases, however you should be familiar with terms such as tables, fields and queries, and
understand the basic half dozen data types. We explain the basics of SQL over a couple chapters, so no prior knowledge
is required of SQL statements. In this book we use Access and Microsoft SQL server, but ADO can connect to almost
any relational or non-relational database.

To summarize: to get the most from a course in ASP pages using ADO, prospective students should have some
experience with:

(cid:132) HTML and ASP

(cid:132) Any programming language

(cid:132) Any database management system

If students are smart, they can learn one of these on the fly, but if they lack two or all three then they should do some
prerequisite work first.

How Is This Book Organized?
wrox 7

The chapters of this book roughly fall into five groups. The first group provides background material, including help in
understanding when an ASP and ADO solution is suitable for you (chapter 1) and how to set up various configurations of
ASP-enabled servers and operating systems (chapter 2). We have paid particular attention to addressing the many
problems that can arise when installing PWS.

The second group leads you through read-only recordsets starting with simple recordsets that are essentially a snapshot
of a table (chapter 3). Having mastered that, we study selection and sorting of records using SQL (chapter 4). We finish
off this section by exploring how to use these read-only recordsets to solve a series of business situations (chapter 5).

By the third section we delve into the underpinnings of how ADO works with ASP. We start with switching our work to
explicit connection and native OLDB providers (chapter 6). A theoretical chapter follows explaining the flow of
information up and down through the software layers (chapter 7). We finish the section with a closer look at the
parameters for the Recordset Open method (chapter 8).

You can only bill clients half an invoice for reading data. In the next section we study the modification of data. Chapter
9 covers techniques using the methods of ADO without SQL. Handling SQL statements to modify data is explored in
chapter 10. This section closes with applying those modification techniques to cookies (chapter 11). At this point we
take the time to examine the Errors collection, and discuss error handling in chapter 12. From here we look at the
ways that ADO offers to work with queries using the Command object, in chapter 13. Once you have worked with the
Command object you can then pass parameters into and out of stored procedures as described and demonstrated in
chapter 14.

The last section addresses common questions from my students. Modern web sites go way beyond text to include
various types of irregular data as covered in chapter 15. Having kept a list of common students mistakes, it was easy to
write chapter 16 on tips and tricks, which lists some common toe-stubbers and how to avoid them. And last, for a
beginners book, is a discussion of how to improve server performance in chapter 17.

Where To Find The Code

We provide the source code for all the code examples in this book for download from our web site, together with the
Access databases used in the examples. All the code for the book is contained in one zip file. When you extract the code
on your machine, the zip file will create a folder for each chapter with the relevant code.

To download the code navigate to http://www.wrox.com and click on the Download link on the menu bar at the top
of the page. This will take you to a page where you should select Beginning ASP Databases from the dropdown list
available and click the Download Source Code button. You can then select to download from our US or our UK site.

The code samples are completely free, however you will need to register on the Wrox site. If you have already
registered, you will be prompted for your password, otherwise you will be asked to register during the download process
- we've tried to make registration as quick and simple as possible.

Conventions

We have used a number of different styles of text and layout in the book to help differentiate between the different
kinds of information. Here are examples of the styles we use and an explanation of what they mean:

Advice, hints, and background information comes indented and italicized, like this.

Important information comes in boxes like this.

(cid:132) Bullets are also indented, and appear with a little box marking each new bullet point, like this.

Important Words are in a bold type font.

Words that appear on the screen in menus like the File or Window are in a similar font to the one that you see on
screen.

Keys that you press on the keyboard, like Ctrl and Enter, are in italics.

Code has several fonts. If it's a word that we're talking about in the text, for example when discussing the For...Next
loop, it's in a fixed width font. If it's a block of code that you can type in as a program and run, then it's also in a gray
box:
wrox 8
Set oCars = CreateObject("WCCCars.Cars")
Set recCars = oCars.GetAll(RegistryRestore("Showroom", "Not Set"))
Sometimes you'll see code in a mixture of styles, like this:

If IsMissing(ConnectionString) Then
varConn = RegistryRestore("Showroom", "Not Set")
Else
varConn = ConnectionString
End If
The code with a white background is code we've already looked at and that we don't wish to examine further.

These formats are designed to make sure that you know what it is you're looking at. We hope they make life easier.

Tell Us What You Think

We've worked hard on this book to make it useful. We've tried to understand what you're willing to exchange your
hard-earned money for, and we've tried to make the book live up to your expectations.

Please let us know what you think about this book. Tell us what we did wrong, and what we did right. This isn't just
marketing flannel: we really do huddle around the e-mail to find out what you think. If you don't believe it, then send
us a note. We'll answer, and we'll take whatever you say on board for future editions. The easiest way is to use e-mail:

[email protected]

You can also find more details about Wrox Press on our web site. There, you'll find the code from our latest books,
sneak previews of forthcoming titles, and information about the authors and editors. You can order Wrox titles directly
from the site, or find out where your nearest local bookstore with Wrox titles is located.

Customer Support

If you find a mistake, please have a look at the errata page for this book on our Web site first. If you can't find an
answer there, tell us about the problem and we'll do everything we can to answer promptly! Just send us an e-mail
(with the title and ISBN of the book, and the page number you're referring to), at:

[email protected]

Why Use ASP and ADO to Put Your
Database on the Web?

In your experience of programming Active Server Pages, you've probably created a variety of pages. Some of the tasks
these pages can perform might include counting the number of visitors that use your site, checking to see if the user
has visited your site before and customizing the information on the page accordingly. You may have taken user
particulars in forms and passed this data from page to page. However, without belittling your achievements thus far,
have you actually provided any "real" content? By real content, I mean the sales report that's several inches thick and
changes on a day to day basis and lies festering on your desk; or at home the analysis of your baseball team's latest
dismal run with statistics to back it up!

In business when your information changes day to day, and you've got customers buying items and inventories that
need replenishing, how often are you going to update your page - once a month, once a week or once a day? And
where is that information coming from? Is it a document, a spreadsheet, a database or even all three? How can you link
this information into your web pages and keep it current? What happens if your airline reservation system is still
showing available seats on a flight that sold out last Tuesday? Your customers aren't going to be too pleased. . .

What you need to do when you create your dynamic pages using Active Server Pages (ASP) is to be able to connect to
wrox 9
your text file or database to drag up and display the information, so that when somebody views your page, they're
viewing the current state of the database. Every time you make an amendment in your database, it should be instantly
reflected on your web site, without the need for you to change a single line of HTML. Wouldn't that make life easy?
Fortunately for you, that's what ASP is best at and it provides you with access to a whole set of components for doing
just this. They are known as the ActiveX Data Objects or ADO for short. ADO brings together the power of databases
with the universality of the Web. This chapter discusses what ASP and ADO can do together as well as the several
limitations to the technologies. We're going to look at:

(cid:132) A quick reminder of what ASP does

(cid:132) Databases and Data Stores

(cid:132) What ODBC is

(cid:132) What OLE-DB and ADO are and the relationship between the two

(cid:132) ASP and ADO in action together

(cid:132) A series of explanations and questions to help you decide on whether to implement ASP and ADO as your
solutions

Business Impact of Dynamic Web Pages

ASP and ADO bring together these two great information technologies of our time to provide the capacity to use the
data of business through the Web. With ASP the traveling technician can now use any computer in any country with any
browser connected to the web, to send in their overtime hours directly and use ADO to connect to their company's
payroll database. The sailor can not only see his prospective purchase in the color and sail plan of his desires, but
docked in front of palm trees or pine trees depending on his zip code, with a list of the dealers nearest to the sailor and
the dealers' current inventories. The web page can even suggest the next more expensive yacht if it is still within the
financial grasp of an average citizen in the sailor's zip code.

ASP and ADO offer the business world the ability to gather orders and requests from virtually anyone in the world, and
process that data directly into the business database systems that control the production and delivery of the product.
The customer request process is without any requirement other than Internet access. Whether your prospective
customer is on a Macintosh in Sudan, an NEC in Fukuoka or a Dell in Savannah, you can use Active Server Pages. And
because of ADO, the output to your database is also without prejudice, with connections available to virtually every type
of database.

Active Server Pages Overview

This book explains, demonstrates, and applies the techniques that lie at the intersection of the two great tools of the
Information Age. One tool, the database, is relatively old. The other, the World Wide Web, is quite new. ASP with the
power of ADO provides the ability to combine these tools and has emerged as the Microsoft Solution for web databases
over the past few years. You should already be familiar with ASP, but we'll remind ourselves briefly of its structure
before we see how ADO fits in.

ASP has a short history since version 1.0 was announced in July of 1996. ASP 1.0 beta hit the streets in November
1996 and the final product was released on December 12th, 1996. ASP 1.0 was available in the Windows NT3 Service
pack 3 as part of IIS 3, and for a short while it was also available as a separate freely downloadable extension for IIS 2.

The current (as of mid-1999) version of ASP is 2.0, which was first released in the NT 4 Option Pack of December 1997.
ASP 2.0 is part of IIS 4.0, which in turn is part of the NT 4.0 Option Pack. The components of NT4, including IIS/ASP,
are built into NT4.0 distributed after mid-1998. The next version of ASP will be 3.0, which we expect to be part of IIS 5,
shipping with the server-capable versions of Windows 2000. The primary changes in the late betas have been
movement of the error functions to their own object, and movement of transactions into COM+.

ASP is actually an extension to your web
server that allows server-side scripting. At
the same time it also provides a
compendium of objects and components,
which manage interaction between the web
server and the browser. These 'objects' can
be manipulated by scripting languages: