Transcript Connecting to a Database

Objectives (ASP)
•Using databases
•ActiveX Data Objects (ADO)
•Connecting to a database
•Reading data from a database
•Inserting, updating and deleting records
Material
This week’s material is from Day 15, 16 and 17
of the textbook.
Read the book.
You can skip those statements/sessions that
are not mentioned in this lecture.
Using databases
To store information through the use of a Web site, we
can use cookies, the Session object and text files.
All these methods have their drawbacks.
Databases are specifically designed to store massive
amounts of information efficiently. Many commercial
databases are available, including Access, MS SQLServer, Oracle and Informix etc.
It is possible to read and modify the contents of a
database through an ASP page.
Relational database ?
Using databases
To retrieve information from a database, you need to
use a two-step process
1. Establish a connection to the database
2. Query the database, asking for information
ADO provides two useful objects: the Connection
object and the Recordset object.
What is ADO?
ActiveX Data Objects ADO
At one time, connecting to a database was difficult.
Database came in a variety of formats, and you have
to know low level API (Application Programming
Interface) for every database you use.
A universal ASP came – ODBC (Open DataBase
Connectivity) was developed. Many databases were
known as ODBC-compliant.
Microsoft’s solution for ODBC = DAO (Database
Access Objects) , then RDO (Remote Data Objects),
and then ADO (ActiveX Data Objects)
ActiveX Data Objects ADO
DAO, RDO, and ADO all have shortcomings.
Microsoft introduced OLEDB, a COM-based data
access object.
COM – Component Object Model – a model for binary
code developed by Microsoft
OLEDB sort of replaces ODBC. It includes an ODBC
driver so it is compatible with all the ODBC data
source.
ActiveX Data Objects ADO
ASP Page
ActiveX Data Object (ADO)
OELDB
ODBC
Access
SQL
Other
Data
Providers
ActiveX Data Objects ADO
The ADO model contains six objects
Connection object - connects to data source
Recordset object - work with data in a table
Error object - represents an error generated by data
source
Field object - represents a single column in the table
Command object - provides another way to create a
recordset object
Parameters object - contains any parameters needed
by the command
Connecting to a Database
Before you can do anything with the database, you
need to connect to it.
There are 2 ways to connect to a Access database.
1. Using a System DSN
2. Using a DSN-less connection
A system DSN is a file that contains information about
the database such as where it is and what kind of
database it is.
Creating a system DSN : (P511)
Control Panel - ODBC - System DSN
Using a System DSN
After creating a DSN (e.g. WidgetWorld.dsn to connect
to c:\my documents\WidgetWorld.mdb) …
You can use the connection object in ASP … (P513)
Dim objConn
Set objConn = Server.CreateObject (“ADODB.Connection”)
objConn.ConnectionString = “DSN=WidgetWorld.dsn”
objConn.Open
This creates the Connection object, set the connection
string, and opens the connection.
objConn.Close
Set objConn = Nothing
This closes the connection to free the memory.
Using a DSN-less Connection
This is an alternative to using a System DSN (e.g. to c:\my
documents\WidgetWorld.mdb) … DSN-less Connection
(P512)
Dim objConn
Set objConn = Server.CreateObject (“ADODB.Connection”)
objConn.ConnectionString = “DRIVER={Microsoft Access Driver (*.mdb)};” &_
“DBQ=C:\my documents\WidgetWorld.mdb”
objConn.Open
Driver = line tells the kind of database it is connecting to
DBQ = line tells where on the server the database resides.
Using a DSN-less Connection
Sometimes we do not have physical access to the server.
Sometimes we want the code to be able to run anywhere.
(so we cannot use c:\my documents\WidgetWorld.mdb)
We can use Server.MapPath to find the physical path.
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &_
server.mappath("WidgetWorld.mdb")
Server.MapPath will find the physical path, so the code
can run anywhere on from any physical location.
Connection to a database
We went through 3 ways to connect to a database
1. System DSN (create it under ODBC)
2. DSN-less Connection (physical path)
3. DSN-less Connection (Server.MapPath, relative path)
Now you know how to connect to a database.
To work with data in the database, you need Recordset
object. (P516)
The Recordset Object
The Recordset object may be used to contain a subset of
the records in a table, or even all the records in the table.
To instantiate the Recordset object (P516)
Dim objRS
Set objRS = Server.CreateObject ("ADODB.Recordset")
The Open Method
recordset.Open source, connection, cursortype, locktype, commandtype
Source: Command Object, or string containing recognized command
Connection: Connection Object, or string containing connection info
cursortype: the way to move through recordset, default 0, forward, Day18
locktype: whether you can write to the table, default 1, readonly, Day 17
commandtype: how source should be evaluated, default 2, as table name
Reading data from a database (P519)
<!--#include file="connect02.asp"-->
<%
Const adCmdTable = 2
Dim objRS
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open "tblExpenses", objConn, , , adCmdTable
Do While Not objRS.EOF
Response.Write "Name: " & objRS("EmployeeName")
Response.Write "<P>"
objRS.MoveNext
Loop
objRS.Close
set objRS=Nothing
objConn.Close
Set objConn=Nothing
%>
Inserting, updating & deleting records (Day17)
<!--#include file="connect02.asp"-->
<%
Const adLockOptimistic = 3
Const adCmdTable = 2
Dim objRS
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open "tblUsers", objConn, , adLockOptimistic, adCmdTable
objRs.AddNew
objRs("FirstName") = "Gene"
objRs("LastName") = "Williams"
objRs("Email") = "[email protected]"
objRs("Username") = "ww123"
objRs("Password") = "pass"
objRs.Update
objRS.Close
set objRS=Nothing
objConn.Close
Set objConn=Nothing
%>
Announcements
A Simple Project 3 Diagram