Textbook Chapter 12: Integrating Databases with ASP Pages20 Minute WebDB Video or Direct Link to Video File
WebDB is a Windows program I wrote for students to quickly get started with Classic ASP databases. It is meant to be a reference, and a working example, to learn the coding required to connect to a database on the server.
All databases on the Web need to provide a way to Create, Read, Update, and Delete records - often called CRUD by programmers.
WebDB will ask you for a table name (what you want to keep up with), and it will ask you for the names of the fields you want to appear in your table. After answering these questions, you click the CREATE ASP PAGES button in WebDB. WebDB will automatically create the table with the fields you provided. Each field will be a text field allowing 50 characters - just a basic set up.
Each generated page is clearly commented to make learning the code quick and easy. In the past, students have struggled with syntax errors as they attempted to write their first Web application. WebDB creates a set of error free pages for students to use as they learn the basic code.
STOP! Take a look at the WebDB video above. After viewing the 20 minute video in its entirety, watch the video again, completing the steps yourself. Once completed, you will have a live, working Web based ASP database that you can experiment with, study the code, and master the basics of connecting to an Access database on the server using Classic ASP. Download WebDBv1.1.zip.
When a database is exposed to the web, users are able to access the database with a web browser. In this chapter, we will learn how to use Active Server Pages (ASP) to connect to an Access database and insert, modify, delete, list and search records - exposing the database to web users.
Here are some examples of how companies use databases on the web:
- E-commerce sites store their product catalog so visitors can place orders
- Human Resources departments expose employee data so employees can make changes
- Sales departments expose sales data so sales people on the road can view commissions
- Business expose data to their business partners to expedite order fulfillment
- Real Estate companies expose property listings data to potential buyers
- Many companies expose customer data to customer service reps that answer customer calls
- Shipping companies provide tracking details so customers can locate and verify shipments
- Social Media sites like Facebook and Twitter are also data driven sites, allowing users to log in to their accounts, like friends, share photos, etc.
Database Driven Web Applications
Here is a list of the tasks that are used to develop a web page that displays data from a database:
- Create database (We'll use Access, but any ODBC or OLEDB will work)
- Create database connection (DSN or connection string)
- Create an ASP page (using Notepad)
- Create a connection object
- Open the connection to the database, using the connection object
- Create a recordset object (ADO object which contains the records)
- Open the recordset object and retrieve records (entire table or certain records using SQL)
- Retrieve and display the fields for each record
- Close the recordset object
- Close the connection object
Database management systems (DBMS) are applications that store and manage data. Relational databases store data in tables and link (or relate) tables together to minimize data redundancy. A table is a grid of rows and columns which stores related information. Each row is a record. Each column is a field. For example, an employee table would contain a record (row in a table) for each employee. A field (column in a table) will contain specific information about each employee, such as name, address, pay rate, etc. A recordset is a group of records - could be all the records in a table, or it could be a subset of records that match some criteria.
Database programs include Access, dBase, FoxPro, SQL Server, Oracle, MySQL. In Access, we will use the design view to define fields in a table and to assign each field a data type (number, text, date, etc.).