How to Create a Database Connection from an ASP Page

How to Create a Database Connection from an ASP Page: Connection String Samples
There are many ways to connect to a database. This tutorial shows the requirements for connecting to a database, how to setup a DSN, and sample connection strings for various types of databases including Microsoft Access and Microsoft SQL Server and database connections.

Requirements

The following are the requirements for connecting to a database:

Active Server Pages (ASP) enabled Internet Information Services (IIS) version 5.0 Web server with Microsoft Data Access Components (MDAC) version 2.5 or 2.6 (with a Jet database engine)

Connectivity to a local or remote database

ASP enabled Microsoft Internet Explorer version 5.0 or later

Sample Database Connection Strings

These examples are for demonstration purposes only. You must paste this code in your ASP code to make a connection to the specified database. Note that you must change elements such as database name, server name, database location, Data Source Name (DSN), and so on. We HIGHLY recommend that you use a DSN connection to your database(s) to increase the performance of the database(s) used on your site.

Microsoft Access

With DSN and no User ID/Password (recommended Access connection string)

<% Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open"DSN=DSNname" %>

VERY IMPORTANT! Make sure to insert the following line where you want to
close the connection]

<% connectionToDatabase.Close
Set connectionToDatabase=Nothing %>

Without DSN

<%
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\html\users\yourdomaincom\database\mydatabase.mdb"
%>

OLE DB

<%
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=D:\html\users\yourdomaincom\database\mydatabase.mdb"
%>

File DSN

<% Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "FILEDSN=DSNname"
%>

Another Example of With DSN and no User ID/Password

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=DSNname"
%>

With DSN and User ID/Password (NOTE: a user/pass is NOT needed if you place your database in the /database directory on the same level as the public /html directory so this is rarely used)

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=DSNname","username","password"
%>

Without DSN, using a physical path as a reference

<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtest=dsntest & "DBQ=D:\html\users\yourdomaincom\database\mydatabase.mdb"
Conn.Open DSNtest
%>

Without DSN, using Server.MapPath

NOTE: Server.MapPath is the path from the Web server root. By default, this is C:\Inetpub\Wwwroot.

<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtest=dsntest & "DBQ=" & Server.MapPath("/database/mydatabase.mdb")
Conn.Open DSNtest
%>

Microsoft SQL Server 2000 Connection Strings

With DSN (recommended SQL connection string)

<% dbconn="Provider=SQLOLEDB.1;UID=user;Password=password;Initial Catalog=DSNname;Data Source=IPaddressoftheSQLserver,PortNumber" %>

OLE DB

<%
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=mydatabasename "
%>

With DSN

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=DSNname;UID=user;PWD=password;DATABASE=mydatabasename"
%>

Without DSN

<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=mydatabasename"
Conn.open DSNtest
%>

NOTE: If you do not want to put your SQL 2000 connection string on your actual ASP pages where the public can view the source and see it, you can create an include file from a hidden page and then call it from within your page where desired. This is a common practice and often used to protect your SQL database.