Kevin Spencer

The Sailors Case Study

You have made it through all of the chapters and find yourself here, ready to implement a complete application, using everything that you have learned throughout this book. By now you have covered all the essentials of ADO and recordset manipulation using Microsoft Access and SQL Server. You have seen how ASP and ADO can be used together to create web applications incorporating information from within a database. This case study will help you to reinforce what you have learned by implementing as much of the material covered as possible into a single application. We will be creating a Sailors web site, and integrating data from the Sailors database.

Stored Procedures and Passing Parameters

When we refer to stored procedures in terms of Microsoft Access we are really referring to queries. The large scale, enterprise relational databases refer to queries as stored procedures. So, what is a stored procedure? It depends on whom you ask. Basically, a stored procedure is a set of logical SQL code that selects columns and rows of data from one or more tables in your database. Stored procedures also allow you to write SQL statements that insert, update and delete data.

RecordSet Methods and Properties

There are two ways to work with a database using ADO. One technique is to incorporate all of the selecting, sorting, editing, and deleting into SQL statements. (In Chapter 10 we will utilize action queries using the SQL keywords upate, insert, and delete to makes changes in our data.) The second technique, covered in this chapter, is to perform these tasks using methods available to the recordset. SQL statements can handle far more complex objectives than methods. However, for some simple tasks, the various recordset methods work very well. In addition, methods can be applied after a SQL statement has performed an initial action. Furthermore, as a site designer you will come across legacy code which relies on these methods.

More Uses for Simple Recordsets

There are many business problems that can be solved with nothing more than a simple recordset. In the last chapter we created half a dozen recordsets, and used them to get data for trivial ASP pages. Prior to going on to learn about more complicated recordsets, I want to demonstrate applying the basic techniques to some more useful business applications. This chapter will lead you through creating pages that you can show to your boss, and will allow you to already justify the cost of the book.

Setting Up ASP and ADO

The first step you should make in using ASP and ADO is to get the software working properly. Before you can start programming databases in ASP, there are three steps that need to take place.

Simple Recordsets — Reading Data

So you’ve decided that ASP-ADO is the solution for your situation, and you’ve learned how to set it up. Now you’re ready to begin using ASP-ADO to access and manipulate data within web pages. This chapter discusses the simplest situation — the simple task of reading the data. Although we are only reading the data, we can still use it in several ways. Not only can we write it to the page; we can also use the data in variables, expressions and as the arguments for other functions.

SQL Statements To Modify Data

So far, in this book, the emphasis has been on retrieving data using the select statement. We have used the select statement to obtain data from the database, which we have then processed or used to populate forms. But the data has to get into the database somehow, and we also need a means of updating existing data. This chapter will show how we can do just that.

Behind the Scenes of ADO

People are always asking me what went wrong with their ASP/ADO application. My favorite questions always go along the lines of “My ASP page doesn’t work. What’s wrong?” You know, if you went to a doctor and told him “I don’t feel good. What’s the matter with me?” he wouldn’t have a clue. If you went to a mechanic and said “My car doesn’t work. What’s wrong with it?” he would probably laugh at you. There are 2 major aspects of troubleshooting ASP/ADO: symptoms, and a knowledge of the process. There are a lot of “links” in the “ASP/ADO chain.” If you don’t know what they are, even knowing what the symptoms are isn’t going to help you much.

Basic SQL for ADO Recordsets

Much of the communication between ASP-ADO and the database is conducted by the Structured Query Language (SQL). This chapter covers the fundamentals of reading data through the use of SQL statements. (SQL for changing and adding data is covered in Chapter 10.) In addition to the syntax of SQL statements, this chapter discusses specifically how to build those statements using ASP. The chapter ends by addressing the complexities added by using data gathered from forms or server functions.

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!