Summary of Contents Introduction Chapter 1: Why Use ASP and ADO to Put Your Database on the Web? Chapter 2: Setting Up ASP and ADO Chapter 3: Simple Recordsets-Reading Data Chapter 4: Basic SOL for ADO Recordsets Chapter 5: More Uses for Simple Recordsets Chapter 6: Connections Chapter 7: Behind the Scenes of ADO Chapter 8: Recordset Parameters Chapter 9: Recordset Methods and Properties Chapter 10: SOL Statements to Modify Data Chapter 11 : Databases and Cookies Chapter 12: The Errors Collection Chapter 13: Command Object Chapter 14: Stored Procedures and Passing Parameters Chapter 15: Irregular Data Chapter 16: ADO Tips and Tricks Chapter 17: Performance Testing and Performance Improvements The Sailors Case Study Appendix A: Structure of Sailors.mdb Appendix B: Structure of Clothier.mdb Appendix C: Profile.mdb Database Schema Appendix D: Creating a Sailors Database in SOL Server Appendix E: Active Server Pages Object Model Appendix F: Microsoft ActiveX Data Objects 2.0 Library Reference Appendix G: VBScript Reference Index

Beginning ASP Databases

John Kauffman

with Kevin Spencer Thearon Willis

APress Media, LLC

Beginning ASP Databases

Copyright© 1999 by John Kauffman, with Kevin Spencer, and Thearon Willis

Originally published by Apress in 1999

All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher.

ISBN 978-1-59059-249-6 ISBN 978-1-4302-1121-1 (eBook)DOI 10.1007/978-1-4302-1121-1

Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark.

In the United States: phone 1-800-SPRlNGER, email orders@springer-ny.com, or visit http://www.springer­ny.com. Outside the United States: fax +49 6221345229, email orders@springer.de, or visit http://www.springer.de.

For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, email info@apress.com, or visit http://www.apress.com.

The information in this book is distributed onan "as is" hasis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall ha ve any liability to any person or entity with respect to any loss or damage caused or alleged tobe caused directly or indirectly by the information contained in this work.

The source code for this book is available to readers at http://www.apress.com in the Downloads section.

Editorial Board Dan Appleman Craig Berry Gary Cornell Tony Davis Steven Rycroft Julian Skinner Martin Streicher JimSumser Karen Watterson Gavin Wray John Zukowski

Technical Reviewers Burt Abreu Chris Blexrud David Buser Charles Caison Jnr Robert Chang Michael Corning Steve Danielson Marco Gregorini Scott Haley Rob Hebron Jon Jenkins Manohar Kamath

Credits

Pieter Reint Siegers Kort Robert MacDonald Sam MacDonald Dave Navarro Sr Geoff Pennington John Timney David Williams

Managing Editors Joanna Mason Frances Olesch

Additional Material Chris Blexrud David Buser David Sussman Chris Ullman

Development Editor Sarah Bowers

Technical Editors Joanna Mason Dianne Parker Andrew Polshaw

Design/Layout Tom Bartlett Mark Burdett Jonathan Jones John McNulty William Fallon David Boyce

Index Alessandro Ansa

Figures David Boyce William Fallon

Cover KurtKrames

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 Training®Kauffmans.org.

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 inC 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 I ADO. Microsoft awarded me the MVP award in 1997. I have written articles about Microsoft FrontPage and ASP I 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.

To my wife Margie for her patience and understanding while I write yet another book, I love you very much.

Table of Contents

Introduction

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

Business Impact of Dynamic Web Pages Active Server Pages Overview Active Server Objects

Databases in the Modern World

ODBC Databases on the Web Before ADO The Way Ahead: Data Stores

What is OLE-DB?

The ADO Object Model

What ASP and ADO Can Do

ASP and ADO Examples

Why use Server-Side Solutions?

What Server-Side Solutions Mean in Business Terms Benefits Drawbacks

Alternative Web Servers with ASP Alternatives to ASP

Take Home Points: A Summary Of Terminology

Summary

1

11

12

12 13 13 14 15 17 17

18

20 21 26 26 26 27

29 30

31 31

Table of Contents

Exercises 32 Quiz 32 Quiz Answers 33

Chapter 2: Setting Up ASP and ADO 35

Overview of Components and Options 36 Server Support for ASP 37 Installing liS on NT Server 37 Installing liS and ASP on Windows 2000 (Workstation or Server) 43

Do I need to install it at all? 44

Installing PWS on Win 9x 46 Sources of PWS 47 Typical Problems of PWS on Win9x 55

First, Be Sure You are Testing Correctly 55 Opening an ASP Page Launches Visual lnterdev, FrontPage or PhotoShop 55 IE Connects to Internet Even When Page is Local on PWS 56 Loss of FTP Message 57 NotePad Adds a .txt Extension 57 IE Erroneously Changes the URL type 58 Installation Error Message: Requires 32 bit TCP /IP Networking or Missing WinSock 58 PWS Install Can't see IE 4.01 59 Synchronizing Default Pages 59 MTS registry not installed 60

More Troubles and Troubleshooting 60 ASP Roadmap 61

Installing ADO on liS/NT /Win 2000 or Win9x/PWS 61 Setting up DSNs 64

Steps to Create a DSN for an Access Database 65 Steps to Create a DSN for a SQL Datastore 67 A Practical Tip on Thinking Ahead 73

II

Is it all working?

Summary Exercises

Quiz

Quiz Answers

Chapter 3: Simple Recordsets - Reading Data

Preparing to use Simple Recordsets Step 1- Create a DSN Step 2- User Identification and Password

Table of Contents

74 77 78

78

79

81

Step 3- Understand the Structure of the Database

81 81 82 82 82 84 85 85 86 89 91

91 92

95 99 99

Syntax for Simple Recordsets Writing Data to the HTML Page

Stuffing Data into a Variable

Using Data in an Expression

Using Data as an Argument in a Function

Which Record? A Note on Moving Through Record sets

Building Tables with Data Building Tables with For ... Next Loops

Reading All of the Records (with EOF)

A Trap With Recordsets and Tables Table Warping by NULLS

A Note on Moving in Recordsets

Summary

Exercises Exercise Answers

Quiz

Quiz Answers

100 100 101 101 104 105

iii

Table of Contents

Chapter 4: Basic SQL for ADO Recordsets 109

Introduction to SQL 109 Flavors of SQL 110

SQL Syntax 110 General Syntax Rules of SQL 111

Using SQL Statements to Open Recordsets 111

Solving the Elephantine SQL Statement Problem 113

SELECT Statements 114

ORDER BY Clause 117

WHERE Clause 119 Simple WHERE Clause 120

Using WHERE to Find One Record 121 WHERE with BETWEEN ... AND 121 WHERE with IN 122 WHERE with Logical Operators 124

Building SQL Statements with a Query Grid 125

JOIN Queries- A Marriage of Tables 130

Incorporating Data from HTML Forms into SQL Statements 132

Working with User-Entered Numbers in SQL Statements 133 Working with User-Entered Text and Dates in SQL Statements 137

Troubleshooting SQL Statements 142 Response.Write the SQL Statement 142

Comment Out the Line that Executes the SQL 143

Add a Response.End and Walk Through the Code 144

Type (or Rebuild on a Query Grid) the SQL Statement Elsewhere and Compare to the Original 145

When Fixing, Save the Original 145

iv

Table of Contents

Write a Simpler Statement and See if it Works, then Add Clauses 145 Hard Code the Variable Information 146 Is there Data to be Returned? 147 Low Tech Solutions 148

Summary 148

Exercises 148

Exercise Answers 149

Quiz 154 Quiz Answers 155

Chapter 5: More Uses for Simple Recordsets 159

Introduction 159 Filling a List Box 160

Fields Collection 164

Making a Table Procedure 167

Writing Tables With the GetString Method 171

Common Errors when Using GetString 172

Summary 176

Exercises 177

Exercise Answers 180

Quiz 184

Quiz Answers 185

Chapter 6: Connections 187

Introduction 187

Why Use Connection Objects? 187

Implicit and Explicit Connections 188

Making Connections 189 Syntax 189 Multiple RecordSets on One Connection 192

v

Table of Contents

Alternative Types of Connections DSN-less Native OLEDB Providers

Connection Pooling

Viewing and Changing Connection Properties

Elucidating Schema An Introduction to Schema Schema Technique Schema Arguments

Summary

Exercises

Exercise Answers

Quiz

Quiz Answers

Chapter 7: Behind the Scenes of ADO

vi

Introduction

The Components ASP (Active Server Pages) ADO (ActiveX Data Objects) ODBC (Open Database Connectivity) OLE DB RDS (Remote Data Services) MDAC (Microsoft Data Access Components)

The Process Play By Play To Sum It Up

Troubleshooting ASP I ADO

Common ASP I ADO Errors Debugging Techniques

195 195 200 205 206 207 207 208 210 213 214 216 219 220

223

223 223 224 225 226 227 228 229 229 229 230 231 232 236

Viewing the SQL Viewing Variable Values

Erroneous Non-Errors

Sabotaging Your Application

Summary

Exercises

Quiz

Quiz Answers

Chapter 8: Recordset Parameters

Parameter Overview

Errors

Source Parameter

Table Names SQL Statements

Stored Procedures Command Object

The ActiveConnection Parameter

The CursorType Parameter

The adOpenForwardOnly Cursor

The adOpenKeySet Cursor

adOpenDynamic Cursor

adOpenStatic Cursor

The LockType Parameter

adLockReadOnly Lock

adLockPessimistic Lock

adLockOptimistic Lock

adLockBatchOptimistic Lock

Options Parameter

Summary

Exercise 1

Table of Contents

242 247 249 249 249

250 251 252

255

255 256 257 257 260 261 264 270 270 270 272

273 274 275 275 276 279 280 283

284 285

vii

Table of Contents

Exercise 2 Quiz Quiz Answers

Chapter 9: RecordSet Methods and Properties

Introduction Supported Methods Find

Find Syntax Find Traps

Filter Property AddNew Update and Batch Update UpdateBatch Delete Editing Data in Records Navigation Through Records Summary Exercises Exercise Answers Quiz Quiz Answers

Chapter 10: SQL Statements To Modify Data

viii

Distinction Between Adding and Updating Where Does The Data Come From? Inserting A Record Updating A Record Deleting A Record

287 289 290

293

293 294 299 299 303 304 304 313 318 320 328 328 330 330 331 337 338

341

341 342 343 356

372

Table of Contents

Summary 381 Exercise 1 382

Exercise 2 389

Quiz 400

Quiz Answers 401

Chapter 11: Databases and Cookies 403

Review of Cookies 403 Purpose of Cookies 403 Key Points About Cookies 404 Examples of Using Cookies 407

Syntax to Work with Cookies in Simple ASP (no ADO) 408 Syntax to Set (Write) Cookies in Simple ASP (no ADO) 408

Syntax to Read Cookies in Simple ASP (no ADO) 408

Syntax for Cookies with Multiple Data with Simple ASP (no ADO) 409

Using Cookies with ADO and a Database 414

Reading Cookies for Use in Database Look-Up 415

Resetting a Cookie 419

Summary 427

Exercises 427

Exercise Answers 428

Quiz 432

Quiz Answers 433

Chapter 12: The Errors Collection 435

Introduction 436

Why Using Error Handling Is Important 436 The Errors Collection and the ASP Object Model 436 VBScript vs ADO Errors 440 Development Errors vs Production Errors 441 True Errors vs Data Validation Errors 446

ix

Table of Contents

How to Get Error Information 451 Information About Errors 451 Error Syntax 452

VBScript 452 ADO 452

Using Error Information in Development 453

Using Error Information in Production 462 The Correct Course of Action 462 Continuing Execution 466

Common Errors 467 Common VBScript Errors 467

Common ADO Errors 470

ASP Help 473 ASP-ADO Errors 473 ASP Roadmap 474

Summary 474 Exercise 475

Exercise Answer 476

Quiz 481

Quiz Answers 482

Chapter 13: Command Object 485

Command Object Overview 486

Command Object Structure 486

When You Must Use a Command Object 489

When it is Useful to Use a Command Object 502

Parameters 506 Creating multiple parameters with one Parameter object 506

Creating a parameter without a Parameter object 507 Using a separate Parameter object for each parameter 507

Referring to a parameter by ordinal reference 508 Passing parameters directly 508

X

Stuffing Techniques Summary Exercises Quiz Quiz Answers

Table of Contents

519 525 525 537 538

Chapter 14: Stored Procedures and Passing Parameters 541

Benefits Maintainability

Simple Select Stored Procedures Parameterized Stored Procedures

Reusability Performance Flexibility

Application Active Server Pages Business Rules Compiled Programs

Traps Passing Values to a Stored Procedure

Select Stored Procedures Command Object Recordset Object

Action Stored Procedures Command Object Connection Object

Retrieving Values From a Stored Procedure Retrieving a Return Code Retrieving a Value What About Access?

Summary Exercise 1

541 542 542 542 542 542 543 543 543 544 544 545 546 547 547 556 562 562 569

575 576 580 586 586 586

xi

Table of Contents

Exercise 2 587 Exercise Answers 588 Quiz 594 Quiz Answers 595

Chapter 15: Irregular Data 597

Introduction 597 How Binary Data is Stored 598 Working With Binary Data 599

The Easy Way 599 Building a Dynamic Image Tag from a Stored File Name 600

The Hard Way 601 The Display Page 602 Stripping the Access Header 604 Getting the Header Info 606 The "Display" File (Access) 609

Uploading BLOB Data 611 The Easy Way and The Hard Way 612

Working With Access Memo and SQL Server Text Fields 617 SQL Server Table Structure 617 In Search of the Lost Data 618

Summary 619 Exercises 620 Quiz 621 Quiz Answers 622

Chapter 16: ADO Tips and Tricks 625

SQL Data Shortcomings 625 Null Values in Recordsets 626 Cleaning SQL Statements 628 Validating Dates- The IsDate Function 632

xii

Retrieving the Number of Records Empty HTML Table Cells

Multiple Recordsets

Refreshing Command Objects

Additional Books

Summary

Quiz

Quiz answers

Chapter 17: Performance Testing and Performance Improvements

Introduction Server Issues

Processor Bottlenecks Processor Throttling (US 5 only) Connection Pooling Tools- Packaged Operating System

System Monitor (Windows 9x) Performance Monitor Event Viewer Task Manager Network Monitor

Tools- Wcat Database Issues

Access vs. SQL Server Indexing

Coding Issues Stored Procedures BLOBS, MEMO, and Bit Fields Storing ADO Objects in Sessions Optimizing Queries

Query Analyzer (SQL Server)

Cursor Types

Table of Contents

633 635 637 643 646 646 647 648

651

651 651 652 652 652 653 653 654 654 654 655 655 655 655 657 657 657 660 660 661 661 662

xlii

Table of Contents

Testing ADO Performance With ASP How the Tests are Run Displaying the Results Analyzing the Results

Summary Exercises Quiz Quiz Answers

The Sailors Case Study

xiv

The Sailors Web Site The Include Files

Error Handling Authentication Check Connecting to the Database Disconnecting From the Database Common Functions Menu Options ADO Constants

Global.asa Logging On and Registration

Login Process Summary

Web Site User Options The Display Boats Page The Boat Registration Page The Display Ranking Page

Interesting Observations Not Authenticated Session Timeouts

Some Things To Try Summary

662 667 670 671

675 676 677 678

681

682 683 683 684 685 685 685 686 687

688 689 714

714 716 720 732 738 738

738

738 739

Appendix A: Structure of Sailors.mdb

Introduction and Objective Vocabulary and Business Rules Where to Download Structure and Relationships of Tables

People Table Boats BoatClass Rank Clubs

Overview of Queries Structure of Tables Structure of Queries

Appendix B: Structure of Clothier.mdb

Introduction and Objective Vocabulary and Business Rules Where to Download Structure of Tables and Queries

Items Table Vendors Table

Table Data

Appendix C: Profile.mdb Database Schema

Tables profile BLOBs

Table of Contents

741

741 741 742 742 742 742 742 742 742

742 743 745

749

749 749 749 750 750 750

750

753

753 753

755

XV

Table of Contents

Stored Queries 756 deletetest 756

inserttest 756

paramselecttest 756

selecttest 756 updatetest 756

Appendix D: Creating a Sailors Database in SQL Server 759

Creating the Sailors Database 759

Creating the Data Source Name 759

Exporting the Tables 760

Appendix E: Active Server Pages Object Model 765

The Request Object 765

The Response Object 766 The Application Object 767

The Session Object 768

The Server Object 768

The ObjectContext Object 769

Appendix F: Microsoft ActiveX Data Objects 2.0 Library Reference 771

Objects 771

Command Object 772

Connection Object 773

Error Object 775

Errors Collection 776

Field Object 776

Fields Collection 777

xvi

Parameter Object Parameters Collection Properties

Property Object Recordset Object Method Calls Quick Reference

Command Connection Errors Field Fields Parameter Parameters Properties Recordset

Appendix G: VBScript Reference

Array Handling Assignments

Constants Error constant System Color constants Comparison constants Date and Time constants Date Format constants Message Box Constants String constants Tristate constants VarType constants

Control Flow

Table of Contents

778 779 779 780 780 785 785 785 785 785 785 785 786 786 786

789

789 790 790 790 790 791 791 791 792 792 793 793 794

xvii

Table of Contents

Index

xviii

Functions Conversion Functions Date/Time Functions Math Functions Miscellaneous Functions Object Management Functions Script Engine Identification String Functions Variable Testing Functions

Variable Declarations Error Handling Input/Output

MsgBox Input Box

Procedures Other Keywords Visual Basic Run-time Error Codes

795 795 796 797 798 798 798 799 800 800 800 801 801 803 803 803 803

809

Table of Contents

xix