article

Database Connectivity and Data Access

Email
Submitted on: 1/5/2015 11:22:00 AM
By: Daniel M. Hendricks (from psc cd)  
Level: Beginner
User Rating: By 19 Users
Compatibility: ASP (Active Server Pages)
Views: 3393
 
     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.

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


Other 1 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.