Database Connectivity and Data Access

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

 
Connect to the Database
 
Run your SQL commands
 
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 =
Server.CreateObject("ADODB.Connection")

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 =
Server.CreateObject("ADODB.Connection")

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
at oledb.com. 
Connecting to a database using a DSN:
Set db =
Server.CreateObject("ADODB.Connection")

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
password. 
Connect to a SQL Server database with OLE DB: 
Set db =
Server.CreateObject("ADODB.Connection")

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 =
Server.CreateObject("ADODB.Connection")

db.Open "Driver={MySQL}; SERVER=localhost; DATABASE=database; UID=username;
PWD=password"
This code has only been tested on a Cobalt RAQ with
Chili!Soft ASP and MySQL.
Connect to Oracle 8 (OLE-DB): 
Set db =
Server.CreateObject("ADODB.Connection")

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
database:
Connect to Oracle 8: 
Set db =
Server.CreateObject("ADODB.Connection")

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
oledb.com. 


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
mytable")

rs.MoveFirst

Do Until rs.EOF

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

Loop
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")



rs.MoveFirst

Do Until rs.EOF

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

rs.MoveNext

Loop



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
added.
Here is another quick and easy way to connect and list
records:

 

 



'This code connects to the
database.

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

db="DSN=TechSupport;UID=TechSupport;PWD=foobar"
'This code iterates through the
current records.

mySQL = "SELECT * from chairs "

rs.open mySQL, db, 1, 3<-- Change the '3' to a '1' for
a read-only. -->

rs.MoveFirst

Do Until rs.EOF

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

 rs.MoveNext

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

rs.MoveFirst

rs.Delete

rs.AddNew

rs("Name") = 'Jane Doe'

rs.Update

rs.Close

 
 

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.