Database Connectivity

1

• Database Connectivity
• Session 2

2
Topics Covered

• ADO Object Model
• Database Connection
• Retrieving Records
• Creating HTML Documents on-the-fly

3
Database Access

• ASP provides easy access to databases
• It contains Database Access Component
• Contains ActiveX Data Objects (ADO)
• Object Model
• ADO methods to manipulate databases and build
  dynamic pages
• ADO can use ODBC interface to RDBs.
• ADO can also use other interfaces OLE-DB
  Provider

4
ADO Object Model
Connection Object
Recordset Object
Fields Collection
Properties Collection
Field Object
Property Object
Command Object
Parameters Collection
Properties Collection
Parameter Object
Property Object
Properties Collection
Errors Collection
Property Object
Error Object
5
ADO Objects

• Connection Object
• Establish active connection to gain access to
  data
• Command Object
• Used to execute SQL queries or manipulate data
  directly
• Recordset Object
• Gives us access to data that is returned from
  executing the SQL query, a stored procedure, or
  by opening the table
• Properties Collection
• Collection of Properties for Connection, Command,
  and Recordset objects
• Fields Collection
• Individual fields (values) within each record

6
Connecting to Data Sources

• Before connecting to a data store, we need to
  specify what it is and where it is
• Three ways to supply this information when
  creating the connection
• Connection String
• Simple character string that lists all of the
  information needed to connect to a data source
• Data Link Files
• Create a Universal Data Link (UDL) file that
  stores the info.
• Data Source Names (DSN)
• Use ODBC drivers to set up the DSN
• Now considered as outdated approach OLE-DB
  provider is considered better because of
  efficiency

7
Connection String

• A typical connection string will contains some or
  all of the following key pieces of information
• Provider the type of OLE-DB provider used in the
  connection
• Driver type of ODBC driver used if not using
  OLE-DB provider
• Initial File Name or Data Source the physical
  database path and the file name
• Initial Catalog the name of the database
• User ID the user name needed to connect to the
  database
• Password the password for the specified user
• Persist Security Info a boolean variable set to
  TRUE if you want windows to remember the password

8
Examples of Connection String

• For Access Database
• MS Access uses the Microsoft Jet Engine, so the
  OLE-DB provider is specified as the Jet Engine
• If we are accessing the same database using the
  ODBC driver for MS Access (instead of the OLE-DB
  provider), we use the following
• For SQL Server database, the connect string may
  look like

• Provider Microsoft.Jet.OLEDB.4.0 _
• Data Source c\mydatabases\test.mdb _
• Persist Security Info False

• Driver Microsoft Access Driver (.mdb) _
• DBQ c\mydatabases\test.mdb

• Provider SQLOLEDB Persist Security Info
  False _
• User ID joe Password shmoe _
• Initial File Name c\mydatabases\test.mdf

9
DSN-less Connection String

• Make a folder called db in your web folder
• Put your database into the db folder you just
  created. 
• Use the following connection string in your ASP
  codeset objConn Server.CreateObject("ADODB.Con
  nection")objConn.Open "DRIVERMicrosoft Access
  Driver (.mdb) _ DBQ " server.mappath("db/DBN
  ame.mdb") 
• Modify the DBName.mdb to match your actual
  database name.

10
Connection Object

• Create an instance of connection object
• set myconnServer.CreateObject(ADODB.Connection)
  
• Scope of Connection
• Create the connection every time you access the
  data
• Create the connection once and use it for
  different operations
• Connection creation statement can be placed in
  the Session_onStart or Application_onStart
  routines in global.asa
• Connection object contains methods and properties
  to open, and close connections, execute commands
  on the data source specified in the connection,
  and controlling transactions.

11
Methods of Connection Object

• Open Opens a new connection to a data source
• Close Closes an existing open connection
• Execute Execute a query, SQL statement or
  stored procedure
• BeginTrans Begins a new transaction
• CommitTrans Saves any changes made and ends
  the transaction. May also start a
  new transaction
• RollbackTrans Cancels any changes made and ends
  the transaction. May also
  start a new transaction
• OpenSchema For server side scripts, allows the
  view of database schema, such as
  tables, columns, etc.

12
Command Object

• Command object can be used to directly execute
  commands
• It provides methods and properties to manipulate
  individual commands.
• Methods
• CreateParameter
• Execute
• Properties
• ActiveConnection, CommandText, Command Timeout,
  CommandType, Name, Prepared, State

13
RecordSet Object

• Contains query results
• Syntax
• set myset connection.execute(CommandText,
  RecordsAffected, Options)
• set myset command.execute(RecordsAffected,
  Parameters, Options)
• Example
• set myconserver.createobject(ADODB.Connection)
• mycon.open Northwind System DSN
• set myset mycon.execute(select from
  products)

14
RecordSet Object (Continued)

• The ADO Recordset object is used to hold a set of
  records from a database table
• A Recordset object consist of records and columns
  (fields)
• Most important and the most used object to
  manipulate data from a database
• When you first open a Recordset, the current
  record pointer will point to the first record and
  the BOF and EOF properties are False.
• If there are no records, the BOF and EOF property
  are True.

15
RecordSet Object (Continued)
0
1
2
3
4
Fields Collection
MyRs.fields.count will return 5 MyRs(0).Name
returns the name of the first field MyRs(0).Value
returns the value of the first field
Current Record Pointer
BOF
First Record
RecordSet Object (say MyRs)
Last Record
EOF
When the recordset object is created, the
Current Record Pointer will be pointing to the
First Record and BOF and EOF properties will be
False If the recordset is empty, BOF and EOF will
be True
16
Methods of RecordSet Object

• AddNew Creates a new record in an updatable
  recordset
• CancelBatch Cancels a pending batch update
• CancelUpdate Cancels any changes made to the
  current or new record
• Clone creates a duplicate of the current
  recordset
• Close closes an open recordset and any
  dependent objects
• Delete deletes the current record in an open
  recordset
• GetRows Extract a number of rows into an array
• Move Moves the cursor forward or backward by
  specified number of records
• Example move(5) moves the cursor forward by
  5 records
• move(-3) moves the cursor
  backward by 3 records

17
Methods of RecordSet Object (contd.)

• MoveFirst, MoveLast, MoveNext, MovePrevious
• Moves to the first, last, next, or previous
  record in the recordset, and makes that the
  current record
• NextRecordset Move to the next recordset in the
  query
• Open Opens a curser on a recordset
• Requery Updates data by re-executing the
  original query
• Resync Refreshes the data, but does not
  re-execute the query. This allows updates to be
  seen but no new rows.
• Supports Determines whether the recordset
  supports certain functions
• Update Saves any changes made to the current
  record
• UpdateBatch Writes all pending batch updates to
  disk.

18
Examples

• Example 1 - Get all the customers
• Example 2 Get products with CategoryID 3
• Example 3 - Retrieve data based on user input

19
Example 1 Code (customers_new.asp)
ltHTMLgtltHEADgtltTITLEgtResultslt/TITLEgtlt/HEADgt ltBODY
BGCOLORcyangt lt_at_ LANGUAGE"VBSCRIPT"gt lt SQL
"SELECT FROM CUSTOMERS SET DbObj
Server.CreateObject("ADODB.CONNECTION")
myvar "DriverMicrosoft Access Driver
(.mdb) DBQ" _
server.mappath("db_f03/Northwind.m
db") DbObj.Open myvar SET oRs
DbObj.Execute(SQL) gt (continued in next
slide)

Define a simple sql statement
create an instance of the connection object
Construct the DSN-less connection string
Notice the continuation character ( _ )
Open the connection to the data source
execute the sql string and store the results in
the recordset object
20
Example 1 Code (Continued)
lth1gtCustomer Informationlt/h1gt ltTABLE
BORDER3gt ltTRgt ltTDgtltbgtltcentergtCustomerIDlt/bgtlt/cent
ergtlt/TDgt ltTDgtltbgtltcentergtCompanyNamelt/b
gtlt/centergtlt/TDgt ltTDgtltbgtltcentergtContactNamelt/bgtlt/ce
ntergtlt/TDgt ltTDgtltbgtltcentergtAddresslt/bgtlt/centergtlt/TD
gt lt/TRgt lt WHILE NOT oRs.EOF gt ltTRgt ltTDgt
lt oRs.Fields("CUSTOMERID").Value gt lt/TDgt
ltTDgt lt oRs.Fields("COMPANYNAME").Value gt
lt/TDgt ltTDgt lt oRs.Fields("CONTACTNAME").Valu
e gt lt/TDgt ltTDgt lt oRs.Fields("ADDRESS").Val
ue gt lt/TDgt lt/TRgt lt oRs.MoveNext gt lt WEND
gt lt/TABLEgt lt/BODYgtlt/HTMLgt
Create a Table. Hard code the attribute names in
the first row
while the recordset is not empty
Add the value of each attribute into HTML table
using the lt operator
Move the cursor to the next available record
End of the while loop
21
Example 2 Code (products_new.asp)
lthtmlgtltheadgt ltmeta http-equiv"Content-Type"
content"text/html charsetwindows-1252"gt ltmeta
name"GENERATOR" content"Microsoft FrontPage
4.0"gt ltmeta name"ProgId" content"FrontPage.Edito
r.Document"gt lttitlegtNew Page 1lt/titlegtlt/headgt ltbod
ygt lth1gtProducts with Category ID 3lt/h1gt lt set
my_conn Server.CreateObject("ADODB.Connection")
myvar "DriverMicrosoft Access Driver
(.mdb) DBQ" server.mappath("db_f03/Northwind
.mdb") my_conn.Open myvar StrSql "Select
from products where CategoryID 3" set MyRs
my_conn.Execute (StrSql) if MyRs.BOF or MyRs.EOF
then response.write "ah, yeah That didn't
work.ltbrgtThere was an error. your gonna hafta to
go " _ "ahead, and ah, try again.lta
hrefjavascripthistory.back()gtbacklt/agt okay?
thanks a lot." response.end end if gt
Establish connection with the nortwind database
Construct the DSN-less connection string
Open the connection to the data source
From the products table, retrieve the products
with categoryid3 and store the records in the
MyRs recordset object
If no records found are found, i.e.,
the Recordset object is empty, then send the user
back to the previous page
22
Example 2 Code (Continued)
Now, go through the record set and create the
HTML Table
ltpgtltbrgt lttable border 1 width"100"gt lttrgt lt
howmanyfieldsMyRs.fields.count for i0 to
howmanyfields-1 gt lttdgtltbgtltfont color
"darkblue"gtlt MyRs(i).name gtlt/fontgtlt/bgtlt/tdgt
lt next gt lt/trgt lt do while not MyRs.eof
gt lttrgt lt for I 0 to howmanyfields-1
cur_field MyRs(I).value gt lttd
valigntopgtlt cur_field gtlt/tdgt lt next gt
lt/trgt ltMyRs.movenext loopgt lt!-- end of do
while loop --gt ltMyRs.close Set MyRs Nothing
My_Conn.Close set My_Connnothinggt
ltbrgtlt/tablegtlt/bodygtlt/htmlgt
Get the field count
Output the field names for the first row
Now, go through each record and print out the
values of each field. Start with the first
record. Within each record, for each field get
its value and store it in the variable
Output the value of the variable into the table
data element
Move the record pointer to the next record
Close the recordset and the connection and set
them to nothing
23
Example 3 Code (select_new.asp)
lthtmlgt ltheadgt ltmeta http-equiv"Content-Type"
content"text/html charsetwindows-1252"gt
lttitlegtGet Product from Formlt/titlegt lt/headgt
lt 'Establish the connection with the
nowrthwind database set my_conn
Server.CreateObject("ADODB.Connection") 'Construc
t the connection string using relative path for
the database file myvar "DriverMicrosoft
Access Driver (.mdb) DBQ"
server.mappath("db_f03/Northwind.mdb") 'Open the
connection to the data source my_conn.Open
myvar 'Get the products from the products_new
table StrSql "Select from products" set rs
my_conn.Execute (StrSql) 'Display a message if
no products found if rs.BOF or rs.EOF then ' No
records found response.write "Hmm... That
didn't work.ltbrgtThere was an error. " _
"Oh well, try again.lta
hrefjavascripthistory.back()gtbacklt/agt"
response.end end if gt
24
Example 3 Code Continued (select_new.asp)
ltbodygt lth1gtAvailable Products..lt/h1gt lth2gtPlease
select a product and click on Submit Query to get
more information.lt/h2gt lt!-- Create a select
object with product ids from the retrieved tuples
--gt lt!-- From that list the user can select a
particular product --gt lt!-- When the user clicks
on the submit button, the form, along with its
data, --gt lt!-- is sent as input to the
get_product_new.asp file --gt ltform method"post"
action"get_product_new.asp"gt lt!-- call the
next ASP page --gt Select Product ltselect
name"product"gt lt! creating the drop down list
--gt lt do while not rs.eof gt ltoption
value"ltrs("productid")gt"gtltrs("productname")
gtlt/optiongt lt rs.movenext 'move the cursor
to the next record in the recordset loop my_Conn.
Close close th e connection
object set my_conn nothing gt ltinput
type"submit"gtlt/formgt lt/bodygt lt/htmlgt
25
Example 3 Code (get_product_new.asp)
lthtmlgt ltheadgt ltmeta http-equiv"Content-Type"
content"text/html charsetwindows-1252"gt
lttitlegtGet Product from Formlt/titlegt lt/headgt
lt if request.form("product")"" then
response.write "You must select a product in lta
href""select_new.asp""gtselect_new.asplt/agt
first." response.end end if 'Establish the
connection with the nowrthwind database set
my_conn Server.CreateObject("ADODB.Connection") '
Construct the connection string using relative
path for the database file myvar
"DriverMicrosoft Access Driver (.mdb) DBQ"
server.mappath("db_f03/Northwind.mdb") 'Open
the connection to the data source my_conn.Open
myvar 'Construct the SQL statement by
concatenating the value selected by the
user StrSql "Select from products where
productid" request.form("product") 'Execute
the SQL statement and create a recordset set rs
my_conn.Execute (StrSql) 'Display a message if
no products found if rs.BOF or rs.EOF then ' No
records found response.write "Hmm... That
didn't work.ltbrgtThere was an error. Oh well, "
_ "try again.lta
hrefjavascripthistory.back()gtbacklt/agt"
response.end end if gt
26
Example 3 Code Continued (get_product_new.asp)
ltbodygt lth1gtInformation about the "
ltrs("productname")gt " productlt/h1gt lttable
border"1"gt lt!-- Create the first row of the
table with headings --gt lttrgtlttdgtProduct
IDlt/tdgtlttdgtProduct Namelt/tdgtlttdgtSupplier
IDlt/tdgtlttdgtQuantity Per Unitlt/tdgt
lttdgtPricelt/tdgtlttdgtUnits in Stocklt/tdgtlt/trgt lt
do while not rs.eof gt lt!-- Display the
information corresponding to the selected product
--gt lttrgtlttdgtltrs("productid")gt
lt/tdgtlttdgtltrs("productname")gtlt/tdgt
lttdgtltrs("supplierid")gtlt/tdgtlttdgt
ltrs("QuantityPerUnit")gtlt/tdgt
lttdgtltrs("UnitPrice")gtlt/tdgtlttdgtltrs("unitsinsto
ck")gt lt/tdgtlt/trgt lt rs.movenext loop
'End of do while
loop my_Conn.Close Close the connection
object set my_conn nothing gt
lt/tablegt lt/bodygt lt/htmlgt
27
Assignment 2

• Create two asp pages.
• The first asp page should list the Company names
  in a drop-down list. The user can select a
  Company and click on the submit button. It
  should call the next asp page which lists the
  Company information as well as the sponsorship
  information
• Database to be used exercise2.mdb
• Tables to be used Company and Projects
• Click to see the assignment demo