Some ASP and Database Background


Introduction.  Earlier we talked about some of the important properties and aspects of different databases.  I'm assuming you have some background in databases due to previous course work and experience.   In general, relational databases are composed of tables with records and fields.  Records usually require unique identifiers often called primary keys.  Relationships between tables, which can occur in different varieties including one-to-one and one-to-many, are usually set up based on what are called foreign keys.  I assume you know what these are.

We all know there are many different DBMSs that can be used.  In this course we will make use of Access and SQL for our databases.  You could also use dBase, FoxPro, Oracle, Paradox, DB2 or many others.

While ASPs are largely a Microsoft development, they can still be used to interact with a large variety of different DBMSs.  We will talk about this later in this page.

Ultimately, there are only four things you can do with database data

  • retrieve data
  • insert data
  • update existing data
  • delete existing data

Now see how easy databases really are?  Well obviously, this simplification leaves out a lot of other things associated with databases, including the huge variety of ways you even do these four data manipulations.  But these things, including many others, can be done from ASPs.

Why Use Databases On The Web? There are so many reasons it is impossible to list them.

At a general level, think about when you use your web browser and visit a particular site.  When you click on a hyperlink you move to another page.  Unless the previous page is cached in your computer's memory you will need to completely reload it if you go back.  Let's say you visit a particular site to search for a book and then have to leave for class.  When you go back, unless the remote web server is storing information about your past visit, you are likely to have to re-enter a lot of the same information you entered previously.  A web server is called stateless because it doesn't automatically keep tabs on what clients it has spoken to recently or what requests were made.  If you want some information on your clients visiting your web to persist, then you must develop these features.  This can be done to some extent through cookies, text files or some other VBScript objects that we haven't covered.  But one of the best and most used approaches is to develop databases when you want information about a client to persist from one click to the next, or one visit to the next.

One pervasive example of databases on the web is shopping carts in many e-commerce sites.  These are relatively short-term databases and you can easily change or empty a shopping cart on the web.  They also maintain information about an order after you place it in a database.  You are likely to want to find out about shipping progress using a database.  The company is also likely to maintain some information about the orders that clients place for marketing studies.

Think about what would be involved in the QUESBMI situation we described earlier in the course.

We will take time in class to describe some other uses of databases to create persistent information about clients.

Implementing ASP Connectivity to Databases.  To start with, any sort of database can be accessed from an ASP page if it is ODBC compliant.  ODBC is the Open Database Connectivity standard established to help improve connectivity between different DBMSs.  My impression, though I'm not a quotable source, is that this standard developed in large part due to the incompatibility and competition between Microsoft and Oracle database efforts.  

Recently, Microsoft has improved on this using what is called OLEDB.  OLEDB is a COM based data access object that provides access to all types of data.  OLEDB essentially replaces and improves upon ODBC from the Microsoft point of view.  This also seems to be true from a user's point of view.  OLEDB includes an ODBC driver so that it is compatible with all of these types of sources in addition to others.  

The connection is established using what is called ActiveX Data Objects or ADO for short.  At least in terms of connectivity power, these are an improvement over the DAO and RDO from earlier VB developments.  Unfortunately, ADO has not been around as long as the DAO so it tends to be a bit sensitive and finicky at times.  But the ADO seems to be a solid effort towards improvement, especially when trying to deal with connectivity issues associated with accessing data across the internet.

The following diagram represents how an ASP page can access different data sources.




The ADO has six objects.  These are listed in the following table.  We will focus on the Recordset and Connection objects.


Object Description
Connection This was developed to allow you to connect to a data source.
Recordset This allows the developer and user to work with data in a table or query.  This contains a set of rows from what is often called a view.  For example, you can use it to read, modify, add and delete data from the source.
Field This represents a column in the table.  It has a variety of properties such as type, name and value.
Command This gives another way to create a Recordset object by some sort of combination of Connection and Recordset.
Parameters This contains any parameters needed by the command.
Error This object is used in order to handle errors in processing and data access.