Using Databases in ASP

One of the most important features of ASP is that it makes it easy for you to connect to a database. is usually connected to an access or an SQL database. Because access is the easiest to start with, and you may already have access on your machine, in the example below, we'll use access for example. Once you've learned the core technology for ASP and Access database connectivity, when you start using SQL Server, you'll find that the key technologies needed are essentially the same.

When you want to connect to the database, you need to open the database on the server. You can connect and open a database by using a data source name (DSN) or by using a dsn-less connection directly in your scripting language.

Create a data source name (DSN)

You can make your database available for use in ASP by creating a System DSN in the Control Panel for your database. You can create several DSNs on your local computer, each of which corresponds to a different database that you use. After you have created the DSN, you can test your page on your local server. If your site is made up of

ISP provides services, and the ISP supports ASP, it is very likely that it will provide a GUI interface to create a DSN for your database.

In Windows 95/98/NT, open the Control Panel (Start menu-> set-> Control Panel) and double-click ODBC to enter.

Select System DSN and click Add.

Select Microsoft Access Driver and click End.

Fill in the data source name. This is the name you gave to your database, so it's the same operation as an alias.

Click the Select button in the database selection to browse the location of the Access database you created in the system.

Click OK

The new DSN now appears in the System DSN and can be used on your local server.

Connecting to a database

Let's build a dsn-less connection and see how it connects to the database. When you create a DSN, you have stored some information about the database, so you don't need to repeat them every time you need to use some information, such as database type, name, location, and optional, user and password.

To create a dsn-less connection, you need to provide the same information. The following example shows how to create a dsn-less connection to a database called products:

<%
StrConnect = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\\db\\products.mdb"
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.OpenStrConnect
%>

The second line defines the drive and physical path of the database. In order to use a dsn-less connection, you need to know the actual location of the file (absolute path). Server.MapPath provides a simple working environment for anyone using the host service to find the actual access paths that are difficult to find.

If we have established a System DSN and are named products, the connection code should be:

<%
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open "products"
%>

Now that the database is open, what can you do about it? The first thing, of course, is to read a series of records in the database and put them on your page. But before that, you need a recordset.