Web Access to Database - PowerPoint PPT Presentation

1 / 41
About This Presentation
Title:

Web Access to Database

Description:

Makes access more efficient if you are doing several operations on the ... 'Datastore.inc' contains a string we use to connect to Hollywood.mdb. Datastore.inc ... – PowerPoint PPT presentation

Number of Views:45
Avg rating:3.0/5.0
Slides: 42
Provided by: tomwh
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com