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 |
Microsoft Access and MySQL Databases
Both of these types of databases can be created using your Helm
control panel feature. Drill into the Domains section of
your Helm control panel to create databases and dsns for your
connections.
You should load any existing databases into the db folder
located at the same level as your wwwroot and logs folder.
This will be amore secure location and your dsn(s) will
automatically point to his folder area
IMPORTANT REGARDING SQL: You must have some working
knowledge of SQL Server 2000. We will not provide you
with Enterprise Manager or any other means to connect to your
database. You must get this from Microsoft or have some
knowledge of commands used to create tables and fields.
There is very limited support for SQL Server 2000. If you just
want to use it because you heard it is better, please remain
with Microsoft Access. SQL is much more powerful, but requires
an advanced knowledge of database commands.
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
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"
%> |
With DSN and no User ID/Password (This is our favorite to
use and recommended)
<% 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 |
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.
|
