|
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 or higher with Microsoft Data Access
Components (MDAC) version 2.5 or 2.6 or higher (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 SQL 2000 Databases
For Microsoft Access databases, first create a directory called 'db' at the SAME level as your 'wwwroot' directory.
For most sites, our set up script has already created the db folder for you. This must be done with an FTP client and not Microsoft FrontPage. This directory cannot be inside the 'db' directory. This will ensure that your database has the proper permissions on it and that no-one that visits your site will be able to directly download your database.
Upload your database to this 'db' directory. Then you can log in to your
Windows 2003 Control Panel
and set your own DSN for your Access database under the Database icon.
For Microsoft SQL 2000, please see http://www.1planhost.com/sql2000.asp for details and to sign up for service.
IMPORTANT: 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. There is a $35/year/database fee for SQL as stated on our site.
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
IMPORTANT: In our examples below, we are assuming your site is hosted on our
Windows 2003 web servers and therefore the path to your database would be
e:\html\domains\yourdomain.com\db\mydatabase.mdb
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=e:\html\domains\yourdomain.com\db\mydatabase.mdb"
%>
|
OLE DB
|
<%
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA
SOURCE=e:\html\domains\yourdomain.com\db\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 /db directory on the same level as the public /wwwroot
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=e:\html\domains\yourdomain.com\db\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("/db/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.
1PlanHost provides affordable Windows 2003
ASP, ASP.NET and FrontPage Web Hosting solutions for thousands of
companies and non-profit organizations worldwide. With our
Windows 2003 hosting 100% Uptime and 45 Day Full Money
Back Guarantee,
it is hard to beat us in terms of reliability and value. Since
1996 our
commitment has been to provide customers with the most reliable servers and outstanding support.
Sign up
now to begin your positive Windows 2003 web hosting experience with 1PlanHost.com
An innovative Windows 2003
Resellers Web Hosting Program is also available. We are looking forward to serving you! Darrell Williams
| 
