Creating a Connection
Here's
our first tutorial in ASP. If you've read the introduction, you might
remember that we talked about how the majority of ASP tasks are for
database related reasons. Well, this tutorial will tackle the first
hurdle in database interaction - making the connection. To create
a connection to a database, you have 3 choices. You can either specify
a path to the database, reference a DSN or you can use the Server.MapPath
option. I always use the first option so that I don't have to worry
about any problems with a DSN or the MapPath option. I have had problems
with these last two options before.
The
first option is to create a specified connection to the database.
When designing my first ASP application, I set up a connection to
my database in every ASP page that I needed it. Later, I found that
my testing platform was different than the server, so I had to redo
every one of my connections to reflect this. If I would have known
more and thought about it at the time, I would have created one ASP
page to hold my database connection, and then simply refer to that
anytime I needed to access that connection. We will be connecting
to an Access 2000 database for this tutorial. The syntax for connecting
to another database might be a little different depending on the type
of database. If you are using a different type of database, contact
your systems administrator for more information.
The
way that you refer to your connection is to use it as an include file.
An example:
<% @Language=VBScript%>
<% Option Explicit %>
<!--#include file="connection.asp"-->
<%
set objRS=Server.CreateObject("ADODB.Recordset")
objRS.Open objConn
%>
<html>....
The
code for your connection.asp page would be as follows:
<%
Dim objConn 'declare your variable to hold your connection
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};"
& "DBQ=c:\Inetpub\wwwroot\mydb.mdb"
objConn.open
%>
What's
happening in your connection.asp page is that first you are creating
a connection object on the server by using the Server.CreateObject("ADODB.Connection").
Then you set the ConnectionString of that connection object equal
to the type of database you are using (in this case an Access 2000
db) and the path to the database. You can also specify other options
in your connection string such as username and password if your database
uses these. If you decide to use this connection, please see the last
section of this tutorial for the completion of the connection.
The
second type of connection you can set up is a DSN reference connection.
You can also house this connection in a separate page. To create a
DSN connection for your database, go to Control Panel>ODBC Data
Sources. A window will open and you want to click on the System DSN
tab. Then click Add. Now you need to specify what kind of database
you are connecting to. For now, we will specify the Microsoft Access
Driver because we are using an Access database. Now click on Finish.
You will have to give a Data Source Name and Description and then
click on Select and locate your database. Then when you reference
your database in your connection, your connection string will look
like this:
<%
Dim objConn 'declare your variable to hold your connection
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DSN=name.dsn"
objConn.open
%>
For
your DSN, you will use whatever you gave for a Data Source Name when
you originally set up your DSN.
The
third kind of connection you can use is the MapPath connection. This
is referred to as a DSNless connection. The mappath connection can
be useful when you don't know the exact path to the database and you
don't have a DSN set up for the database. The syntax for this kind
of a connection is listed below.
<%
Dim objConn 'declare your variable to hold your connection
Set objConn=Server.CreateObject("ADODB.Connection")
connectionString="DBQ=" & Server.MapPath("NameOfDatabase.mdb")
objConn.open "DRIVER={Microsoft Access Driver (*.mdb)}; " & connectionString
%>
Finally,
you will include this connection.asp page somewhere in your ASP page
that requires a database connection. Something to remember (and I
do mean REMEMBER!!!!) is to always close your connections as soon
as possible. Whenever you open a connection or a recordset, you are
taking up the server's resources. Therefore, it's best to close all
connections as soon as possible. Don't ever forget to close your connections!
To include and use your connection.asp page, follow the code below.
<%
@Language=VBScript%>
<% Option Explicit %>
<!--#include file="connection.asp"-->
<%
Dim SQL
SQL = "SELECT * FROM tablename WHERE whatever = whatever"
run through all your data here
objConn.close 'closing the connection
Set objConn = nothing
%>
<html>....
And
there you go...all you need to know to connect to your database. In
our next tutorial we will cover retrieving information from our database
and populating a table with that information. See ya next time.
~Geoff
Swartz
| 
