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: