|
SUPPORT -
TUTORIALS - DATABASE
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.
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. 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 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
d:\domains\yourdomain.com\db\yourdatabase.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 d:\domains\yourdomain.com\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.
|
