SUMMARY
There are many ways to connect to a database. This
step-by-step article provides sample connection strings for various types of
databases and database connections.
back to the top
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
back to the top
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.
back to the top
Microsoft Access
Without DSN
<%
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\mydatabase.mdb"
%>
OLE DB
<%
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:\mydatabase.mdb"
%>
File DSN
<% Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "FILEDSN=ADSN"
%>
With DSN and no User ID/Password
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSNname"
%>
With DSN and User ID/Password
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "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=c:\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("/databases/mydatabase.mdb")
Conn.Open DSNtest
%>
back to the top
Microsoft SQL Server
OLE DB
<%
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=mydatabase "
%>
With DSN
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=MyDSN;UID=user;PWD=password;DATABASE=mydatabase"
%>
Without DSN
<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=mydatabase"
Conn.open DSNtest
%>
back to the top
Microsoft Visual FoxPro
Without DSN
<%
Set Conn = Server.CreateObject("ADODB.Connection")
ConnStr= "Driver=Microsoft Visual Foxpro Driver; UID=userID;SourceType=DBC;SourceDB=C:\databases\mydatabase.dbc"
Conn.Open ConnStr
%>
back to the top
Oracle
ODBC with DSN
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.cursorlocation=adUseClient
' requires use of adovbs.inc; numeric value is 3
Conn.open "DSN=test;UID=name;PWD=pass"
%>
OLE DB
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.cursorlocation=adUseClient
' requires use of adovbs.inc; numeric value is 3
DSNTest="Provider=MSDAORA.1;Password=pass;User ID=name;Data Source=data.world"
Conn.open DSNtest
%>
back to the top