Search Tools Links Login

Database Connectivity and Data Access

This article shows various connection strings, used to connect to various databases in Windows, as well as methods to access and modify data. Some connection strings may require client software to be installed, but most work with Windows 2000.

Original Author: Daniel M. Hendricks


Database Connectivity in ASP

This reference will show you how to connect to a variety of
databases in different ways:

  1. Connect to the Database
  2. Run your SQL commands
  3. Common Examples

Connect to the Database

Before you can access your database, you need to connect to it
using one of the following methods:

Microsoft Access 2000 Database (OLE-DB):

Set db =

db.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("database.mdb") & ";"

Microsoft Access databases are quick, easy, and portable.
It works good for small, intradepartmental applications. If you plan on
having more than a few users connecting to it, however, you many wish to
consider using a database like SQL Server or Oracle instead. Here is
another way to connect to a Microsoft Access database:

Microsoft Access 2000 Database:

Set db =

db.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
Server.MapPath("database.mdb") & ";UID=;PWD="

For a description of the difference between OLE-DB and
ODBC, check out this article

Connecting to a database using a DSN:

Set db =

db.Open "DSN=mydsn;UID=username;PWD=password"

Before you can use this method, you must create a DSN
in your control panel (usually under ODBC or Data Sources). This process
varies from each version of Windows, so you're on your own. When you
create a DSN, you will be asked to give it a name. The name you enter
should replace the "mydsn" value above, along with the username and

Connect to a SQL Server database with OLE DB:

Set db =

db.Open "Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog=database;
User ID=username; Password=password"

An OLE DB connection can provide faster performance than a
DSN. This method doesn't require you to set up a DSN (which makes reloading the
machine easier), which makes it easier to reload the computer and doesn't
require you to create a DSN. However, if you move your applications to
another server or if you move your database to another server, you will need to
update any hard-coded values. There are ways around this, but for
simplicity, I have provided the example above.

Connect to a MySQL Database Under Linux/Chili!Soft ASP:

Set db =

db.Open "Driver={MySQL}; SERVER=localhost; DATABASE=database; UID=username;

This code has only been tested on a Cobalt RAQ with
Chili!Soft ASP and MySQL.

Connect to Oracle 8 (OLE-DB):

Set db =

db.Open "Provider=OraOLEDB.Oracle;User ID=user;Password=pwd; Data Source=hoststring;"

This code has only been confirmed to work with Oracle 8i
server and Windows client. Important: Requires Oracle client connectivity
tools to be installed. Here is another way to connect to an Oracle

Connect to Oracle 8:

Set db =

db.Open "Driver={Microsoft ODBC for Oracle};UID=user;PWD=password;CONNECTSTRING=hoststring"

This also requires the Oracle client tools be
installed. For a description of the difference between OLE-DB and ODBC,
check out this article at

Run Your Commands

Now that you have a connection to your database, you can run
SQL statements:

Delete Records:

db.execute("DELETE FROM mytable WHERE
FullName = 'John Doe'")

This is only used as an example. You will need to
replace "mytable" with the name of the table you are trying to delete
from. Likewise, replace "FullName" with the name of the
appropriate field.

Insert Records:

db.execute("INSERT INTO mytable VALUES
('John Doe', 22, '321 Disk Dr.', 'Hollywood, CA')

Again, this is only used as an example. Change the
statement as needed.

List Records:

set rs=db.execute("SELECT * FROM


Do Until rs.EOF

   Response.Write rs("MyField") & "<br>"


The first line is a select statement that selects records.
The following lines iterate through each line, displays the current value of the
"MyField" field, and adds a line-feed. You will want to change
the "mytable" and "MyField" values appropriately.

Common Examples

Add, list, and delete records:



  Set db = Server.CreateObject("ADODB.Connection")

  db.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
  Server.MapPath("database.mdb") & ";"


  db.execute("INSERT INTO MyTable VALUES ('Dan Hendricks', 22)")

  set rs=db.execute("SELECT * FROM MyTable")



  Do Until rs.EOF

  Response.Write rs("NAME") & "<br>"




  db.execute("DELETE FROM MyTable WHERE NAME = 'Dan Hendricks'")


This code will open the database, add the values "Dan
Hendricks" and "22" into the first two field of the chosen table,
display all current records in the table, and finally delete the record that was

Here is another quick and easy way to connect and list



'This code connects to the

  set rs=Server.CreateObject("ADODB.Recordset")



'This code iterates through the
  current records.

  mySQL = "SELECT * from chairs " mySQL, db, 1, 3  


  Do Until rs.EOF

     Response.Write rs("MyField") &




'This code deletes a record, and
  then adds a new one




    rs("Name") = 'Jane Doe'




NOTE: This does not use the same connect statements
listed above. It's just a different way to connect to a database and list,
add, or remove records.

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 88 times




No attachments for this post

Loading Comments ...


No comments have been added for this post.

You must be logged in to make a comment.