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.
IMPORTANT: You must have some working knowledge of SQL Server 2000. You may use Enterprise Manager or similar MS SQL Management tool to connect to your database. Use "mssql.efextra.net,1443" (without quotes) within Enterprise Manager as the host to connect to the MS SQL Server database. You must have some knowledge of commands used to create tables and fields. You may also use our web based SQL Server Management tool located at http://mssql.efextra.net.
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 $2/month/database fee for SQL Server 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
D:\domains\yourdomain.com\db\mydatabase.mdb (replace yourdomain.com with the actual name of your domain)
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:\domains\yourdomain.com\db\mydatabase.mdb"
%> |
OLE DB
| <%
Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=D:\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=D:\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.