Transcript Database Connectivity Session 2

Database Connectivity
Session 2
Topics Covered
ADO Object Model
Database Connection
Retrieving Records
Creating HTML Documents
on-the-fly
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 – OLEDB Provider
ADO Object Model
Connection Object
Recordset Object
Fields Collection
Field Object
Properties Collection
Property Object
Command Object
Parameters Collection
Parameter Object
Properties Collection
Property Object
Properties Collection
Property Object
Errors Collection
Error Object
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
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
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
Examples of Connection String
For Access Database
“Provider = Microsoft.Jet.OLEDB.4.0;” & _
“Data Source = c:\mydatabases\test.mdb;” & _
“Persist Security Info = False”
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 OLEDB provider), we use the following:
“Driver = {Microsoft Access Driver (*.mdb)};” & _
“DBQ = c:\mydatabases\test.mdb”
For SQL Server database, the connect string may
look like:
“Provider = SQLOLEDB; Persist Security Info = False;” & _
“User ID = joe; Password = shmoe;” & _
“Initial File Name = c:\mydatabases\test.mdf ”
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 code:
set objConn= Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; _
DBQ= " & server.mappath("db/DBName.mdb")
Modify the DBName.mdb to match your
actual database name.
Connection Object
Create an instance of connection object
– set myconn=Server.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.
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.
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
RecordSet Object
Contains query results
Syntax
set myset = connection.execute(CommandText,
RecordsAffected, Options)
set myset = command.execute(RecordsAffected, Parameters,
Options)
Example
set mycon=server.createobject(“ADODB.Connection”)
mycon.open “Northwind” “System DSN”
set myset = mycon.execute(“select * from
products”)
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.
RecordSet Object (Continued)
Fields
Collection
Current Record
Pointer
0
1
2
3
4
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
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
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
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.
Examples
Example 1 - Get all the customers
Example 2 – Get products with CategoryID = 3
Example 3 - Retrieve data based on user input
Example 1 Code (customers_new.asp)
<HTML><HEAD><TITLE>Results</TITLE></HEAD>
<BODY BGCOLOR=cyan>
<%@ LANGUAGE="VBSCRIPT"%>
Define a simple sql statement
<%
SQL = "SELECT * FROM CUSTOMERS;“
create an instance of the
connection object
SET DbObj = Server.CreateObject("ADODB.CONNECTION")
Construct the DSN-less
connection string
Notice the continuation
character ( _ )
myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
server.mappath("db_f03/Northwind.mdb")
DbObj.Open myvar
Open the connection to
the data source
SET oRs = DbObj.Execute(SQL)
%>
(continued in
next slide)
execute the sql string and store the
results in the recordset object
Example 1 Code (Continued)
<h1>Customer Information</h1>
Create a Table. Hard code the
<TABLE BORDER=3>
attribute names in the first row
<TR>
<TD><b><center>CustomerID</b></center></TD>
<TD><b><center>CompanyName</b></center></TD>
<TD><b><center>ContactName</b></center></TD>
<TD><b><center>Address</b></center></TD>
</TR>
while the recordset is not empty
<% WHILE NOT oRs.EOF %>
<TR>
<TD> <%= oRs.Fields("CUSTOMERID").Value %>
</TD>
<TD> <%= oRs.Fields("COMPANYNAME").Value %> </TD>
<TD> <%= oRs.Fields("CONTACTNAME").Value %> </TD>
<TD> <%= oRs.Fields("ADDRESS").Value %>
</TD>
</TR>
Add the value of each attribute into
<% oRs.MoveNext %>
HTML table using the <%= operator
<% WEND %>
</TABLE>
Move the cursor to the next
available record
</BODY></HTML>
End of the while loop
Example 2 Code (products_new.asp)
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title></head>
<body>
Establish connection with the nortwind database
<h1>Products with Category ID 3</h1>
<%
set my_conn= Server.CreateObject("ADODB.Connection")
Construct the DSN-less connection string
myvar = "Driver={Microsoft 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)
Open the connection to the data source
From the products table, retrieve the products
with categoryid=3 and store the records in the
MyRs recordset object
if MyRs.BOF or MyRs.EOF then
response.write "ah, yeah: That didn't work.<br>There was an error. your gonna hafta to go " & _
"ahead, and ah, try again.<a href=javascript:history.back();>back</a> okay? thanks a lot."
response.end
If no records found are found, i.e., the
end if
Recordset object is empty, then send
%>
the user back to the previous page
Example 2 Code (Continued)
Now, go through the record set and create the HTML Table
<p><br>
<table border =1 width="100%">
<tr>
Get the field count
<%
howmanyfields=MyRs.fields.count
Output the field names for the first row
for i=0 to howmanyfields-1 %>
<td><b><font color = "darkblue"><%= MyRs(i).name %></font></b></td>
<% next %>
Now, go through each record and print out the values of
</tr>
each field. Start with the first record. Within each record,
<%
for each field get its value and store it in the variable
do while not MyRs.eof %>
<tr>
<% for I = 0 to howmanyfields-1
cur_field = MyRs(I).value
Output the value of the variable
%>
into the table data element
<td valign=top><%= cur_field %></td>
<% next %>
</tr>
Move the record pointer
<%MyRs.movenext
to the next record
loop%> <!-- end of do while loop -->
<%MyRs.close
Set MyRs= Nothing
My_Conn.Close
set My_Conn=nothing%>
<br></table></body></html>
Close the recordset and the connection
and set them to nothing
Example 3 Code (select_new.asp)
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Get Product from Form</title>
</head>
<%
'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 = "Driver={Microsoft 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.<br>There was an error. " & _
"Oh well, try again.<a href=javascript:history.back();>back</a>"
response.end
end if
%>
Example 3 Code Continued (select_new.asp)
<body>
<h1>Available Products..</h1>
<h2>Please select a product and click on Submit Query to get more information.</h2>
<!-- Create a select object with product ids from the retrieved tuples -->
<!-- From that list the user can select a particular product -->
<!-- When the user clicks on the submit button, the form, along with its data, -->
<!-- is sent as input to the get_product_new.asp file -->
<form method="post" action="get_product_new.asp"> <!-- call the next ASP page -->
Select Product:
<select name="product"> <!– creating the drop down list -->
<%
do while not rs.eof
%>
<option value="<%=rs("productid")%>"><%=rs("productname")%></option>
<%
rs.movenext 'move the cursor to the next record in the recordset
loop
my_Conn.Close
set my_conn = nothing
%>
‘close th e connection object
<input type="submit"></form>
</body>
</html>
Example 3 Code (get_product_new.asp)
<html> <head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Get Product from Form</title>
</head>
<%
if request.form("product")="" then
response.write "You must select a product in <a href=""select_new.asp"">select_new.asp</a> 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 = "Driver={Microsoft 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.<br>There was an error. Oh well, " & _
"try again.<a href=javascript:history.back();>back</a>"
response.end
end if
%>
Example 3 Code Continued (get_product_new.asp)
<body>
<h1>Information about the " <%=rs("productname")%> " product:</h1>
<table border="1">
<!-- Create the first row of the table with headings -->
<tr><td>Product ID</td><td>Product Name</td><td>Supplier ID</td><td>Quantity Per Unit</td>
<td>Price</td><td>Units in Stock</td></tr>
<%
do while not rs.eof
%>
<!-- Display the information corresponding to the selected product -->
<tr><td><%=rs("productid")%> </td><td><%=rs("productname")%></td>
<td><%=rs("supplierid")%></td><td> <%=rs("QuantityPerUnit")%></td>
<td><%=rs("UnitPrice")%></td><td><%=rs("unitsinstock")%> </td></tr>
<%
rs.movenext
loop
'End of do while loop
my_Conn.Close ‘Close the connection object
set my_conn = nothing
%>
</table>
</body>
</html>
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