A1256655147ShwBn

1
Module 2 in 2nd Term
Rules, Roles and Laws
Computer Technology III
Topic ActiveX Data Objects (ADO)
By Shiela Marie B. Pelayo
2
Day 1
Rules are gems!
3
Objectives

• At the end of the module, you will be able to
• enumerate and explain the data hierarchy and
  database concepts
• create a table using MS Access
• display, add, and delete records online using the
  ActiveX Data Objects (ADO)

4
The MMDA traffic enforcer has my friends driving
license because he did not follow the traffic
rules.
John, tell your friend to follow traffic rules
always to avoid problems. Lets check the LTO
site to find out the list of traffic rules.
5
Pen Alert!
Let us see if you are still aware of the rules or
laws that govern you everyday. Please do the
activity below.

1. Get your notebook and pen.
2. Write 3 rules and regulations of the school which
   are very familiar to you. Are these rules
   important? Why?
3. You are given 6 minutes to do this.
4. Be ready to share your answers to the class.

6
Just like in the school, rules are also needed
in records management. Syntax of commands must be
observed. Steps must be followed to get the right
output. Familiarization with data hierarchy and
the meaning of ADO is an important step in the
activity. Group the class into three(3) members
per group. Assign a recorder. Lets get to
know database concepts in the next number. 2.
Please read the topic, data hierarchy, on
http//en.wikipedia.org/wiki/Data_hierarchy and
ActiveX Data Objects (ADO) on http//www.w3schools
.com/ado/ado_intro.asp . Reading session 8
minutes only 3. After reading and understanding
the topics, please answer the mind map in your
notebook.
7
Mind Map
Note Write only the number that corresponds to
your answers.
Records of a table can be deleted in the web page
using ADO.(1)
The integration of files is called records.(2)
The EOF is a recordsets property.(6)
Which statements are true about table and ADO?
The records deleted in the web page are also
deleted in the table.(5)
The records of a table can be viewed in the web
page using ADO.(3)
The database name is usually used in the SQLs
Insert statement.(4)
8
Based from what you have read on
http//www.w3schools.com/ado/ado_intro.asp ,
kindly answer the question below
What is ADO? _____________________________________
__________________________________________________
_________
Be ready to share your answers to the class.?
9
MS ACCESS is usually used in creating a table.
The tables field names and data types are
necessary in ADO. The tables field names and
data types can be specified in MS ACCESS Design
View.
The student table has four(4) fields namely
studnum, name, section, and birthdate. The
available data types for a field are text, memo,
number, date/time, currency, autonumber,Yes/No,
etc.
10

• Data Types
• Text - letters and numbers. 255 character
  maximum
• Memo- Up to 64K of text
• Number- This category includes Byte , integer,
  long, single, and double
• Date/Time- stores date and time information
• Currency- gives the accuracy needed for financial
  data
• Autonumber- replaces the Counter as an
  automatically assigned number
• Yes/No- for logical field that is answerable by
  Yes/No, On/Off
• Hyperlink- store the location of a file on your
  computer, a local network, or the world wide web
  . The field contains three parts separated by
  pound signs (), Ex Access Tipshttp//allenbr
  owne.com/tips.html
• OLE Object- Use this type to store pictures,
  audio, video
• Knowledge about data hierarchy, table design, and
  SQL statements are important in ADO.

11
The table below was done in MS ACCESS. The table
name is student . It has 8 records. The fields
are studnum, name, section, and bdate.
12
In order to extract information form the
database, SQL statements must be reviewed. You
have used SQL in your Phonebook Project last
year. Reviewing wont be that hard now. SQL
stands for Structured Query Language. It is a
standard language for accessing and manipulating
databases.
Lets go to the next slide for your SQL review.
13
SQL Select

• The SELECT statement is used to select data from
  a database.
• Syntax SELECT column_name(s) FROM table_name
• SELECT FROM table_name
• Examples
• Select all records of the Juniors table.
• Select From Juniors
• 2. Select all student names from the Juniors
  table (The field is Name)
• Select Name From Juniors
• 3. Select Jazmin Ysmaels record only from the
  Juniors table.
• Select From Juniors where NameJazmin
  Ysmael

14
SQL Delete

• The DELETE statement is used to delete records in
  a table .
• Syntax DELETE FROM table_name WHERE condition
• Examples
• Delete all records of the Juniors table.
• Delete From Juniors
• 2. Delete all student numbers from the Juniors
  table (The field is studnum)
• Delete studnum From Juniors
• 3. Delete Sarah Livelos record only from the
  Juniors table.
• Delete From Juniors where NameSarah Livelo
• Lets go to the next slide for the EXTRA
  Challenge!

15
SQL Insert Into
The Insert statement is used to insert new
records in a table . Syntax INSERT INTO
table_name VALUES (value1, value2, value3,...)
Example Table name Juniors Fields
studnum, name, section, compgrade Target Insert
the data S1996889,Jose Marella, Junior B and 16
in the Juniors table. INSERT INTO Juniors
VALUES (S1996889,Jose Marella, Junior B,16)
Lets go to the next slide for the EXTRA
Challenge!
16
Pen Alert!
Directions Please get your pen and notebook. Use
the table below. Answer the questions below as a
group.

1. What SQL statement selects student number column
   from student table?
2. What SQL statement selects student number and
   name columns from student table?
3. What SQL statement selects Michelle Tans record
   from student table?

17
Steps in Accessing a Database from an ASP Page
Create an ADO connection to a database
Open the database connection
Create an ADO recordset
You will see the script of these steps in the
next hands-on activities.
Open the recordset
Extract the data you need from the recordset
Close the recordset
Close the connection
18
The ADO Recordset object is used to hold a set
of records from a database table.  The
recordsets property EOF(End Of File) and the
recordsets methods Close and MoveNext shall be
used in todays activity. The EOF property
returns True if the current record position is
after the last record, otherwise false.
The Close method closes the recordset. The
MoveNext method moves the record pointer to the
next record. You are now ready for an
activity that uses ADO. Lets go to the next
slide.
19
Hands-On
In this activity, you shall learn how to create a
table using MS Access. The records of the table
shall be displayed in the webpage. You will be
able to add and delete records online
also. Please follow the table design carefully
to avoid problems later. This is the first rule
that you have to know. FOLLOW THE DESIGN OF THE
TABLE.
20

• Create the subfolder ADO Display.
• Open MS ACCESS.

21
3. Click File Menu, select New.
22
4. Click the Blank database option.
23
5. Select your labserve folder in the Save In
option and name your database as juniors.mdb.
Select your labserve folder
juniors.mdb
6. Click the CREATE button.
24
7. Double click the highlighted text Create table
in Design view.
25
This is the tables design view. Use the Field
Name column, the Data Type column, and the
Caption property.
26
8. Input the following in the tables design view
window. Field Names Data Type
Caption studnum Text Student
Number name Text Name section Text Section
bdate Date/Time Birthdate 9. Click the Save
icon. Save it as student table. 10. Click View
Menu, select Datasheet View to input records.
27
11. Make 10 records. 12. Save the table. 13.
Open the NotePad. Copy the script
below. lt 'declare the variables dim
strDatabasename,strTablename,strConnectionPath di
m connectionobject,recordset 'set the
filename strDatabasename "juniors.mdb" 'set
the tablename strTablename "student"
Name of the database
Name of the table
28
'set the value of the connection object set
connectionobject server.createobject("adodb.conn
ection") 'set the path (where the database is
located) strConnectionPath "DBQ"
server.mappath(strDatabasename) 'set the driver
tell the server that 'you will use a Microsoft
Access Database connectionobject.Open
"DRIVERMicrosoft Access Driver (.mdb) "
strConnectionPath 'create a recordset object for
handling records set recordset
server.createobject("adodb.recordset") gt 9.
Save the file as connect.asp in the ADO Display
folder.
29
10. Copy the script in the NotePad. ltbody
bgcolorgreen textwhitegt ltfont faceverdana
size2gt lt!--include file"connect.asp"--gt
lt Set recordset connectionobject.execute("SEL
ECT FROM student") DO WHILE NOT
recordset.EOF Response.Write("ID"
recordset(studnum") "ltBRgt")
Response.Write("Name" recordset("name")
"ltBRgt") Response.Write(Section"
recordset(section") "ltBRgt")
Response.Write(Birthdate" recordset(bdate")
"ltBRgtltBRgt") recordset.movenext LOOP gt
Warning!!! Replace the RED thick double quotes
with thin double quotes .
30

• 11. Save the file as display.asp in the ADO
  Display folder.
• 12. Upload the ASP files by typing the URL below
  in the browsers address area.
• http//labserve/student number/term
  folder/subfolder/display.asp
• Exhttp//labserve/S1996123/Second/ADO
  Display/display.asp
• Add Records in ADO
• Records can be added to the table from the web
  page. The data shall be given in the form. The
  file in the action attribute of the form
  processes the data.
• Make a subfolder. Name it as ADOAddRecord.
• Copy connect.asp and juniors.mdb in the
  ADOAddRecord.
• Open the Notepad. Copy the script below in the
  NotePad.

31
lthtmlgt ltbody bgcolorgreen textwhitegtltfont
facearialgt Please input the information in the
boxes below.ltbrgt ltform name"addform"
method"post" action"add.asp"gt lttablegt lttrgt lttdgtI
Dlt/tdgt lttdgtltinput name"studnum"gtlt/tdgt lt/trgtlttrgt
lttdgtNamelt/tdgt lttdgtltinput name"name"gtlt/tdgt lt/trgtlt
trgt lttdgtSectionlt/tdgt lttdgtltinput
name"section"gtlt/tdgtlttrgt lttdgtBirthdatelt/tdgt lttdgtlt
input name"bdate"gtlt/tdgt lt/trgt lt/tablegt ltbr /gtltbr
/gt ltinput type"submit" value"Add New"gt ltinput
type"reset" value"Cancel"gt lt/formgt lt/bodygt lt/htm
lgt
32
4. Save the file as addform.asp in the
ADOAddRecord folder. 5. Make a new file in the
NotePad. Copy the script below. lt!--include
file"connect.asp"--gt lt 'declare the
variables dim strSQL dim strstudnum, strname,
strsection,strbdate 'get the values from the
form strstudnum request.form(studnum") strnam
e request.form("name") strsection
request.form("section") strbdate
request.form(bdate") 'create the insert
statement 'take note that the name of the table
is student" strSQL strSQL "insert into "
strTablename"(studnum,name,section,bdate)" 'str
SQL should now contain "insert into
student(studnum,name,section,bdate)"
Warning!!! Replace the RED thick double quotes
with thin double quotes .
33
'add "values(" to the string strSQL strSQL "
values(" 'add the "student number'," to
strSQL strSQL strSQL "'" strstudnum
"'," 'add the "name'," to strSQL strSQL
strSQL "'" strname "'," 'add the
"section'," to strSQL strSQL strSQL "'"
strsection ," 'add the "birthdate'," to
strSQL strSQL strSQL "'" strbdate
"'" 'add ")" to strSQL strSQL strSQL
")" 'execute the (SQL) insert statement on error
resume next connectionobject.Execute strSQL
Warning!!! Make the RED single quote thin. Check
the red single quote.
34
if errltgt0 then Response.Write("No update
permissions!") else Response.Write("lth3gt"
recaffected " record addedlt/h3gt") end
if 'close the recordset object set recordset
nothing set connectionobjectnothing gt 6. Save
the file as add.asp in the ADOAddRecord
subfolder. ADOAddRecord folder now contains
connect.asp, addform.asp, add.asp, and
juniors.mdb. 7.Upload the ASP files by typing
the URL below in the browsers address
area. http//labserve/student number/term
folder/subfolder/addform.asp Exhttp//labserve/
S1996123/Second/ADOAddRecord/addform.asp
35

• Deleting Records using ADO
• Records of the table can be deleted directly from
  the web page. The users are advise to be careful
  in deleting records in the web page because they
  cannot be retrieved anymore.
• Make a subfolder. Name it as ADODeleteRecord.
• Copy connect.asp and juniors.mdb in the
  ADODeleteRecord.
• Open the Notepad. Copy the script below in the
  NotePad.
• lthtmlgt
• ltbody bgcoloryellow textbluegt
• ltfont facearial size2gt
• Please input the information in the box
  below.ltbrgt
• ltform method"post" action"delete.asp"gt
• lttablegt
• lttrgt
• lttdgtNamelt/tdgt
• lttdgtltinput namename"gtlt/tdgt
• lt/trgt
• lt/tablegt

Warning!!! Replace the thick double quotes with
thin double quotes .
36
ltbr gtltbr gt ltinput type"submit" value"Delete"gt
ltinput type"reset" value"Cancel"gt lt/formgtlt/body
gt lt/htmlgt 4. Save the file as deleteform.asp in
the ADODeleteRecord folder. 5. Make a new file in
the NotePad. Copy the script below. ltbody
bgcoloryellow textbluegt ltfont facearial
size2gt lt!--include file"connect.asp"--gt lt
dim cname cnameRequest.Form(name")
sql"DELETE FROM student" sqlsql " WHERE
name'" cname "'" on error resume next
connectionobject.Execute sql
Warning!!! Replace the thick double quotes with
thin double quotes .
37
if errltgt0 then response.write("No update
permissions!") else response.write("Record
" cname " was deleted!") end if set
recordset nothing set connectionobjectnothing
gt 6. Save the file as delete.asp in the
ADODeleteRecord subfolder. ADODeleteRecord
folder now contains connect.asp, deleteform.asp,
delete.asp, and juniors.mdb. 7.Upload the ASP
files by typing the URL below in the browsers
address area. http//labserve/student
number/term folder/subfolder/deleteform.asp Exht
tp//labserve/S1996123/Second/ADODeleteRecord/dele
teform.asp
38
Did you enjoy the hands-on activities? Did you
get the correct output? Its time to check if
your answers are correct for the mind map after
doing the files. Mind Map Check the mind map
again. All statements are correct except The
integration of files is called records. (It is
database.) The database name is usually used in
the SQLs Insert statement. (The table name is
used in Insert statement.) Are your answers
correct? Let us test now whether you learn
something from the topics today. Please answer
the seatwork at the Q Online. Good luck!
39
Day 2
Take the challenge!
40
Are you ready for todays challenges! I bet you
are excited to do some online works again. But
before you do the hands-on activities, let us
have a review first of all the things that you
have learned about ADO. Meet with your
groupmates again and answer the questions in
slides 38 and 39. Be ready to share your
answers to class.
41
Group work
Directions Write your answers in ½ cross wise of
paper. LTO_Violation Table
Name Plate Number Violation Penalty
Allan Suarez AJS111 Driving without a license 750.00
Mike Chan UVV345 Expired driver's license 375.00
Jimmy Cortel UBH890 No sticker 150.00
Nick Sabilano WYS565 Illegal turn 150.00
Amanda Lee UCY333 Reckless driving (3rd offense) 1000.00
42
1. What would be the correct data type of
Penalty field? 2. What would be the result of
the SQL statement below? Select from
LTO_Violation where Violation likelicense 3.
What would be the result of the SQL statement
below? Select from LTO_Violation where
Penaltylt200 4. What SQL statement must be used to
select all records of LTO_Violation table? 5.
What SQL statement must be used to select Mike
Chans record?
43
Hands-on
This activity shall test your skills in
displaying and adding records online.

• Make a subfolder named TrafficRules.
• Make a database in MS ACCESS. Save it as
  RulesandPenalties. Save the table as
  trafficrules.
• The fields of the table are name, trafficrule,
  and penalty.
• Make 10 records.
• Copy the files, connect.asp, display.asp,
  addform.asp, and add.asp from your previous
  folders. Paste them in TrafficRules subfolder.
  Edit the files.
• connect.asp- Change the database name and the
  table name
• display.asp- Change the table name and the
  fields
• addform.asp- Change the fields
• add.asp- Change the variables (ex strstudnum)
• Change the fields

44
6. Display the trafficrules table in the web page
using ADO. The Add record feature (Add button)
must be present in the web page. 7. Add 5 more
records using Add button in the web page. 8.
Rate the output using the rating scale below.
Write the score in 1/8 cross wise.
45
Rating Scale 10 The table is displayed in the
web page. The Add feature/button is working
properly. 6 - The table is displayed in the web
page. The Add feature/button does NOT work. 4
The table is NOT displayed in the web page. The
Add feature/button does NOT work. The last
challenge is to get a very good score from the
quiz. Are you ready? Take the quiz now at the Q
Online.
Score please 10/10
46
Wrapping up
Check the scripts of the ADO files. Type the
scripts that specify the Steps in Accessing a
Database from an ASP Page at the right column of
the table. Use the script in connect. asp,
display. asp.
Steps Scripts
1. Create an ADO connection to a database
2.Open the database connection
3. Create an ADO recordset
47
4. Open the recordset
5.Extract the data you need from the recordset
6.Close the recordset
7.Close the connection
Be ready to share your answers to the class.
48
Valuing Rules (syntax) are present in SQL
statements. ADO steps must be followed to manage
records online. Rules are present
everywhere. The students must answer the
questions below in ¼ sheet of paper If you
will be asked to suggest a new traffic rule, what
would it be? Why? What shall be the penalty for
the offenders? Submit the paper to the teacher.