Web Access to Database

1
Web Access to Database

• Tom Whaley
• Department of Computer Science
• Washington and Lee University
• Part 4
• Web Access to DatabaseUsing Active Server Pages

2
ADO - ActiveX Data Objects

• Provides high level way to access data of many
  kinds
• Provides objects for
• Connecting to database
• Executing commands
• Dealing with sets of records
• Dealing with errors

3
ADO Objects and Collections
Parameters
Errors
Command
Fields
Properties
Connection
Recordset
4
ADO - Recordset Object

• This is the main object that well use for our
  examples.
• Represents
• A base table from database
• Result of a query or SELECT statement
• Result of an executed command
• Note There are several ways to execute commands
  with ADO - well work with Recordsets, primarily

5
ADO - Recordset Object (cont.)

• With Recordset object, we can
• Open and close recordset
• Add and delete records
• Cancel the recordset
• Save to file
• Open from file
• Move to different record
• Locate specific records

6
ADO - Connection Object

• Represents a connection to the database
• Makes access more efficient if you are doing
  several operations on the database.
• With Connection object, we can
• Open or close connection to database
• Execute query
• Cancel connection

7
ADO - Command Object

• Allows you to execute a command against the
  database.
• Command is associated with a Connection
• Command has CommandText with the appropriate SQL
  command
• With Command object, we can
• Execute command
• Cancel
• Create a parameter

8
ASP and ADO Models
ASP Model
ADO Model
Server
Client
ServerObject
Request
Database
Recordset
Response
Connection
ApplicationObject
Object-Context
Command
SessionObject
9
The examples
10
Example Suite

• Displaying a specific column from a table
• Function for displaying contents of any table
• Displaying results of a canned query
• Adding a record via Form submission
• Using a simple SQL query
• Building a query on the fly
• Updating a record in a table

11
The Sample Database Hollywood(Arizona State
database course)
12
The movieStar Table
13
Include files

• Well make use of two include files
• This simply allows us to put common code in one
  place and not have to type it into each file
• adovbs.inc contains ADO constants for VBScript
• Datastore.inc contains a string we use to
  connect to Hollywood.mdb

14
Datastore.inc
lt strConnect "DriverMicrosoft Access Driver
(.mdb) DBQC\Inetpub\wwwroot\Hollywood\Holly
wood.mdbgt
15
Example1 - Stars Names

• This example will illustrate
• Creating a RecordSet object
• Populating a RecordSet from a database table
• Looping through the records
• Using the Response object to display the raw data
  from one of the columns
• Closing the RecordSet

16
ltHTMLgt lt!-- INCLUDE FILE"DataStore.inc"
--gt lt!-- INCLUDE FILE"adovbs.inc"
--gt ltHEADgt ltTITLEgtHollywood Stars
lt/TITLEgt lt/HEADgt ltBODYgt lt Dim objRec '
Create the recordset object Set objRec
Server.CreateObject ("ADODB.Recordset") ' Open
the recordset, move forward only, read
only, ' and tell it that its a table we are
opening objRec.Open "movieStar", strConnect,
adOpenForwardOnly, _ adLockReadOnly,
adCmdTable While Not objRec.EOF Response.Writ
e objRec("starName") "ltBRgt" objRec.MoveNext
Wend objRec.Close Set objRec
Nothing gt lt/BODYgt lt/HTMLgt
Link to page
17
(No Transcript)
18
Example2 - General Purpose Table Function

• In this example, well see
• Use of a general purpose function for creating an
  HTML table with data from RecordSet
• Use of the Fields collection for the RecordSet

19
ltHTMLgt lt!-- INCLUDE FILE"DataStore.inc"
--gt lt!-- INCLUDE FILE"adovbs.inc" --gt lt!--
INCLUDE FILE"RecToTable.asp" --gt ltHEADgt ltTITLE
gtADO Fields Collectionlt/TITLEgt lt/HEADgt ltBODYgt lt
Dim objRec ' recordset object ' create
the recordset object Set objRec
Server.CreateObject ("ADODB.Recordset") ' now
open it objRec.Open "movieStar", strConnect,
adOpenForwardOnly, _ adLockReadOnly,
adCmdTable ' now pass the recordset to the
table function Response.Write RecToTable
(objRec) objRec.Close Set objRec
Nothing gt lt/BODYgt lt/HTMLgt
Link to page
20
(No Transcript)
21
lt Function RecToTable (objRec) Dim strT '
table html string Dim fldF ' current field
object ' build the table header strT
"ltTABLE BORDER1gt" _ "ltTR
ALIGNCENTERgt" ' each field as a table column
name For Each fldF In objRec.Fields strT
strT "ltTHgt" fldF.Name "lt/THgt" Next strT
strT "lt/TRgt" ' now build the rows While
Not objRec.EOF strT strT "ltTR
ALIGNCENTERgt" ' add the fields For Each
fldF in objRec.Fields strT strT "ltTDgt"
fldF.Value "lt/TDgt" Next strT strT
"lt/TRgt" objRec.MoveNext Wend strT strT
"lt/TABLEgt" ' and finally return the
table RecToTable strT End Function gt
22
Example 3 Multi-Table Stored Query

• Here starFilm is a stored query that involves
  joining data from several tables.
• Well see that stored queries are handled much as
  regular tables.

23
ltHTMLgt lt!-- INCLUDE FILE"DataStore.inc"
--gt lt!-- INCLUDE FILE"adovbs.inc" --gt lt!--
INCLUDE FILE"RecToTable.asp" --gt ltHEADgt ltTITLE
gtADO Fields Collection with Querylt/TITLEgt lt/HEADgt
ltBODYgt lt Dim objRec ' recordset
object Set objRec Server.CreateObject
("ADODB.Recordset") ' now open it - Note that
Query is given as adStoredProc objRec.Open
"starFilm", strConnect, adOpenForwardOnly,
_ adLockReadOnly, adStoredProc Response.Writ
e RecToTable (objRec) objRec.Close Set
objRec Nothing gt lt/BODYgt lt/HTMLgt
Link to page
24
(No Transcript)
25
Example4 Insert Row

• In this example, well
• Make use of an HTML Form for user input of data
  for a new row in the Critics table
• This input is passed to another ASP page
• This page makes use of the Request object to get
  the data
• The page then inserts the new row

26
ltHTMLgt ltHEADgt ltTITLEgt Form for New Critic
lt/TITLEgt lt/HEADgt ltBODYgt ltH1gt New Critic Entry
Form lt/H1gt ltH3gt Please fill in the form and
click Submit lt/H3gt 'On Submit, CriticInsert
will be executed ltFORM ACTION"CriticInsert.asp"
METHODPOSTgt ltPgtCritic Id ltINPUT TYPE"TEXT"
NAME"CriticId"gtlt/Pgt ltPgtCritic name ltINPUT
TYPE"TEXT" NAME"CriticName"gtlt/Pgt ltPgtltINPUT
TYPE"Reset" VALUE"Start Over on this
Page"gt ltINPUT TYPE"SUBMIT" VALUE"Submit these
values"gtlt/Pgt lt/FORMgt lt/BODYgt lt/HTMLgt
27
The Critic Insertion Code
ltHTMLgt lt!-- INCLUDE FILE"DataStore.inc"
--gt lt!-- INCLUDE FILE"adovbs.inc"
--gt ltHEADgt ltTITLEgtInsert New Critic
lt/TITLEgt lt/HEADgt ltBODYgt lt Dim objRec Dim
strCriticId, strCriticName strCriticId
Request.Form("CriticId") strCriticName
Request.Form("CriticName") Set objRec
Server.CreateObject ("ADODB.Recordset") objRec.Op
en "critic", strConnect, adOpenStatic,
_ adLockOptimistic, adCmdTable objRec.AddNew o
bjRec("cId") strCriticId objRec("cname")
strCriticName objRec.Update Response.Write
"Sucessfully added " Response.Write
strCriticName objRec.Close Set objRec Nothing
gt lt/BODYgt lt/HTMLgt
Link to page
28
(No Transcript)
29
Example 5 Using a simple SQl Query

• In this example, well see how to use an SQL
  query from the ASP file without building a query
  in the database.

30
The Critic Insertion Code
ltHTMLgt lt!-- INCLUDE FILE"adovbs.inc" --gt lt!--
INCLUDE FILE"DataStore.inc" --gt lt!-- INCLUDE
FILE"RecToTable.asp" --gt ltHEADgt ltTITLEgtFind
Contactslt/TITLEgt lt/HEADgt ltBODYgt lt Dim strSQL '
SQL String Dim objRec ' Recordset
object strSQL "SELECT starName,title "
strSQL strSQL FROM movieStar,actsIn,filmProj
ect " strSQL strSQL "WHERE gender 'F'
" strSQL strSQL " AND actsIn.filmIdfilmProjec
t.filmId " StrSQL strSQL " AND
movieStar.starIdactsIn.starId" ' create the
recordset Set objRec Server.CreateObject
("ADODB.Recordset") objRec.Open strSQL,
strConnect, adOpenForwardOnly, _ adLockReadOnly
, adCmdText ' write a table of the
recordset Response.Write RecToTable
(objRec) objRec.Close Set objRec
Nothing gt lt/BODYgtlt/HTMLgt
Link to Page
31
(No Transcript)
32
Example 6 Dynamic Query

• In this example, well see
• User input used to build an SQL query string
• Execution of the SQL command
• Specifically, the user will indicate which fields
  from the Stars table to display and will indicate
  the gender (M, F or both) to display
• A use of the Fields collection to make the Form

33
The Critic Insertion Code
ltHTMLgt lt!-- INCLUDE FILE"adovbs.inc" --gt lt!--
INCLUDE FILE"DataStore.inc" --gt ltHEADgt
ltTITLEgtFind Contactslt/TITLEgt lt/HEADgt ltBODYgt ltFORM
NAMEStarInfo ACTION"StarsQuery.asp"
METHOD"POST"gt ltH2gtFind Stars by
Genderlt/H2gt Enter the Gender (M/F) to
find ltINPUT TYPETEXT NAME"Gender"gt ltPgtPlease
select which fields you would likeltPgt lt dim
objRec Set objRec Server.CreateObject
("ADODB.Recordset") objRec.Open "movieStar",
strConnect, adOpenForwardOnly, _ adLockReadOnly,
adCmdTable ' create a checkbox in the form for
each field in the recordset For Each objField in
objRec.Fields Response.Write "ltINPUT
TYPECHECKBOX NAME" vbQuot _ "Field"
vbQuot " VALUE" vbQuot objField.Name
vbQuot _ "gt" objField.Name "ltBRgt"
vbCR Next objRec.Close Set objRec
Nothing gt ltINPUT TYPESUBMIT VALUE"Find"gt ltINPUT
TYPERESET VALUE"Clear"gt lt/FORMgt lt/BODYgt lt/HTMLgt
34
The Critic Insertion Code
ltHTMLgt lt!-- INCLUDE FILE"adovbs.inc" --gt lt!--
INCLUDE FILE"DataStore.inc" --gt lt!-- INCLUDE
FILE"RecToTable.asp" --gt ltHEADgt ltTITLEgtFind
Contactslt/TITLEgt lt/HEADgt ltBODYgt lt Dim strSQL '
SQL String Dim objRec ' Recordset object Dim
intCount ' number of fields selected ' find out
which fields are to be selected strSQL "" For
intCount 1 to Request.Form("Field").Count strS
QL strSQL Request.Form("Field")(intCount)
", " Next ' strip off the trailing comma and
space added in the loop strSQL Left(strSQL,
Len(strSQL) - 2) strSQL "SELECT " strSQL "
FROM movieStar" If Request.Form("Gender") ltgt ""
Then strSQL strSQL " WHERE Gender '"
Request.Form("Gender") "'" End If Set
objRec Server.CreateObject ("ADODB.Recordset")
objRec.Open strSQL, strConnect,
adOpenForwardOnly, adLockReadOnly,
adCmdText Response.Write RecToTable
(objRec) objRec.Close Set objRec
Nothing gt lt/BODYgtlt/HTMLgt
Link to Page
35
(No Transcript)
36
(No Transcript)
37
Example 7 Updating a record

• In this example, well see
• Use an HTML form to specify id of movie star to
  update
• An ASP file to check that star exists and return
  values in a form
• Another ASP file to do the updating

38
Update - Get stars id
39
Update - Get stars info
40
Update - Do the update
41
Go to next presentation