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

Code

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 =
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  

  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.

About this post

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

Categories

ASP/ HTML

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.