Available

1
Making Student-Specific Information
Available
Via The Internet
Thy An Bui - Thesis Presentation
2
Agenda

• Problem statement
• Applicable technology
• The Internet
• Microsoft Office
• Visual Basic for Application
• Cold Fusion
• Active Server Pages
• My programs
• Excel worksheet with Visual Basic for Application
• Access database with Cold Fusion
• Access database with Active Server Pages
• Analysis

3
Problem Statement

• Overview
• Spreadsheet
• Database
• Web site
• Users
• Teachers
• Students
• Parents
• School Officials

4
Applicable Technology

• Stand-alone programming
• not interface with the server
• Example Visual Basic for Application
• Client-side scripting
• Runs on the client machine
• Does not use up host resources
• sends the actual code out to browser
• Examples JavaScript or VBscript
• Server-side scripting
• Does everything in the server
• Has access to the software and data resources of
  the host
• can not see the actual program
• Examples Cold Fusion or Active Server Pages

5
Excel Worksheet with Visual Basic for Application
(VBA)

• What is Visual Basic for Application?
• Created by Microsoft
• Work within existing software program
• Implemented in Microsft Word, Excel, Access, and
  PowerPoint
• My Excel worksheet and VBA program

6
Tools gtMacro gtVisual Basic Editor
7
Sub update() Dim colB As String, colA As String,
p As String, Name As String Dim repeater As
Integer Dim last_student As Integer Dim temp1 As
Variant, temp2 As String Dim HW As Variant, quiz
As Variant, test As Variant, avg As Variant Dim
num_col As Variant Dim column As Integer,
row2startD As String Dim h As Integer, q As
Integer, t As Integer, a As Integer Dim i As
Integer, j As Integer, ff As Integer Dim hwval As
Variant, quizval As Variant, testval As Variant,
avgval As Variant Dim number_order As String,
homework_grade As String, quiz_grade As
String Dim test_grade As String, average_grade As
String ReDim num_col(90) ReDim hwval(0)
'hwval is the array contains all of the
homework ReDim quizval(0) ReDim testval(0) ReDim
avgval(0) last_student 16 h -1 q -1 t
-1 a -1 For column 0 To 89 Step 1 If
column lt 22 Then num_col(column)
Chr(68 column) ' Chr() is a function for
character set ElseIf column gt 22 And column
lt 48 Then num_col(column) "A" Chr(65
column - 23) ElseIf column gt 48 And column
lt 74 Then num_col(column) "B" Chr(65
column - 49) Else num_col(column)
"C" Chr(65 column - 75) End If Next
column For i 0 To 89 Step 1 ' row2startD
is variable name that contain row 2 col D, which
held all the ref. variables to verify what col is
hw, q, and test. row2startD num_col(i)
"2" 'num_col(i) specify that it must start at D
If Range(row2startD) "h" Then h h
1 ReDim Preserve hwval(h) ' redefine
the homework array to the value h, resize the
array with the number of homework
hwval(h) num_col(i) ' keep track of how many
homework ElseIf Range(row2startD) "q" Then
q q 1 ReDim Preserve
quizval(q) quizval(q) num_col(i)
ElseIf Range(row2startD) "t" Then t t
1 ReDim Preserve testval(t)
testval(t) num_col(i) ElseIf
Range(row2startD) "a" Then a a 1
ReDim Preserve avgval(a) avgval(a)
num_col(i) End If Next i If h gt -1 Then '
error checking check to see that is any hw,
quizes, and tests ReDim HW(h) ' Else
ReDim HW(0) ' Will print out a blank when there
are no homework End If If q gt -1 Then ReDim
quiz(q) Else ReDim quiz(0) End If If t gt -1
Then ReDim test(t) Else ReDim test(0) End
If If a gt -1 Then ReDim avg(a) Else ReDim
avg(0) End If
For repeater 4 To last_student Step 1 'first
student start at row 4 For j 0 To h Step 1
temp1 hwval(j) LTrim(Str(repeater)) '
hwval(j) HW(j) Range(temp1) Next j
For j 0 To q Step 1 temp1
quizval(j) LTrim(Str(repeater)) quiz(j)
Range(temp1) Next j For j 0 To t Step
1 temp1 testval(j) LTrim(Str(repeater)
) test(j) Range(temp1) Next j
For j 0 To a Step 1 temp1 avgval(j)
LTrim(Str(repeater)) avg(j)
Range(temp1) Next j colA "A"
LTrim(Str(repeater)) colB "B"
LTrim(Str(repeater)) p Range(colB) p
"d\cs330\HTML" "\" p ".htm" Name
Range(colA) Name Mid(Name, 4, 20)
Open p For Output As 1 Print 1,
"lthtmlgt" Print 1, "ltbodygtltBgt"
Print 1, "Name " Print 1, Name
Print 1, "lt/BgtltBRgtltBRgt" For ff 1 To h 1
Step 1 If ff 1 Then
number_order "lttdgtltcentergtltfont color'red'gt"
Str(ff) "lt/tdgtlt/centergtlt/fontgt" Else
number_order number_order
"lttdgtltcentergtltfont color'red'gt" Str(ff)
"lt/tdgtlt/centergtlt/fontgt" End If Next
ff Print 1, "lttable border5gtlttrgtlttdgtlt/tdgt"
Print 1, number_order "lt/trgt"
Print 1, "lttrgtlttdgtltfont color'blue'gtHomeworklt/td
gtlt/fontgt" For ff 0 To h Step 1
temp2 HW(ff) If ff 0 Then
homework_grade "lttdgtltcentergt" temp2
"lt/tdgtlt/centergt" Else
homework_grade homework_grade "lttdgtltcentergt"
temp2 "lt/tdgtlt/centergt" End If
Next ff Print 1, homework_grade
"lt/tdgtlt/trgt" Print 1,
"lttrgtlttdgtltfont color'blue'gtQuizzeslt/tdgtlt/fontgt"
For ff 0 To q Step 1 temp2 quiz(ff)
If ff 0 Then quiz_grade
"lttdgtltcentergt" temp2 quiz_grade
quiz_grade "lt/tdgtlt/centergt" Else
quiz_grade quiz_grade "lttdgtltcentergt"
temp2 quiz_grade quiz_grade
"lt/tdgtlt/centergt" End If Next ff
Print 1, quiz_grade "lt/tdgtlt/trgt"
Print 1, "lttrgtlttdgtltfont color'blue'gtTestslt/tdgtlt/
fontgt" For ff 0 To t Step 1 temp2
test(ff) If ff 0 Then
test_grade "lttdgtltcentergt" temp2
test_grade test_grade "lt/tdgtlt/centergt"
Else test_grade test_grade
"lttdgtltcentergt" temp2 test_grade
test_grade "lt/tdgtlt/centergt" End If
Next ff Print 1, test_grade "lt/tdgtlt/trgt"

Print 1, "lttrgtlttdgtltfont color'blue'gtAverageslt/td
gtlt/fontgt" For ff 0 To a Step 1 temp2
avg(ff) If ff 0 Then
average_grade "lttdgtltcentergt" temp2
average_grade average_grade
"lt/tdgtlt/centergt" Else
average_grade average_grade "lttdgtltcentergt"
temp2 average_grade average_grade
"lt/tdgtlt/centergt" End If Next ff
Print 1, average_grade "lt/tdgtlt/trgtlt/tablegt"
Print 1, "ltbrgt" Print 1, "lt/bodygt"
Print 1, "lt/htmlgt" Close 1 Next
repeater End Sub
8
Web Page Demonstration
students.htm
http//aurora.wells.edu/bthy/students.htm
Sign in
List of students names and a link to sign in
form.htm
Enter ID
A box will ask for students ID with the summit
button
grades.htm
A grades table will be displayed for a specific
student
9
Access Database with Cold Fusion

• My Grades database.

10
Cold Fusion
11
STUDENTS.CFM lt CFQUERY DATASOURCE"grades"
NAME"SInformation" gt SELECT FROM
SInformation lt/CFQUERYgt ltHTMLgtltHEADgt ltTITLEgtStude
ntslt/TITLEgtlt/HEADgt ltBODY text"purple"
link"purple" vlink"purple" alink"purple"gt ltcent
ergtltfont size12gtltigtWelcome Everyone!lt/igtlt/fontgtlt/
centergtltbrgt ltCFOUTPUT QUERY"SInformation"gt
ltcentergtltTDgtnamelt/agtlt/TDgtltbrgt lt/centergt lt/CFOUTP
UTgt ltbrgtltbrgtltbrgt ltcentergtltfont size5gt ltA
HREF"form.cfm"gtSIGN IN TO SEE YOUR
GRADESlt/agtlt/fontgtlt/centergt lt/BODYgt lt/HTMLgt
FORM.CFM lt CFQUERY DATASOURCE"grades"
NAME"SInformation" gt SELECT FROM
SInformation lt/CFQUERYgt lthtmlgt ltheadgtlttitlegtSign
inlt/titlegtlt/headgt lth1gtltfont size 6
color"purple"gtPlease enter your student's
IDlt/fontgtlt/h1gt ltCFOUTPUT QUERY"SInformation"gt ltF
ORM ACTION "grades.cfm?id"id"
METHOD"POST"gtlt/CFOUTPUTgt ltINPUT TYPE"text"
NAME"StudentID" SIZE"10"gtltbrgtltbrgt ltINPUT TYPE
"submit" VALUE"Submit"gtltINPUT TYPE "reset"
VALUE "Reset"gt lt/FORMgtlt/bodygtlt/htmlgt
GRADES.CFM ltCFQUERY DATASOURCE"grades"
NAME"all" gt SELECT FROM SInformation,
homework, quiz, test, average WHERE
(form.StudentID SInformation.id AND
SInformation.id homework.id AND
homework.id quiz.id AND quiz.id test.id AND
test.id average.id) lt/CFQUERYgt ltHTMLgt ltCFOUTPU
T QUERY"all"gt ltHEADgtltTITLEgtnamelt/TITLEgtlt/HEADgt
ltbody text"purple"gt ltigtltbgtltH3gtnamelt/H3gtlt/igtlt/bgt
ltTABLE BORDER"1" CELLSPACING"1"
CELLPADDING"2"gt lttrgt ltTD
BGCOLOR"silver"gtHomework lt/tdgt
ltTDgtweek1---week2---week3---week4lt/tdgt lt/t
rgt lttrgt ltTD BGCOLOR"silver"gtQuizzes
lt/tdgt ltTDgtquiz1---quiz2---quiz3---q
uiz4lt/tdgt lt/trgt lttrgt ltTD
BGCOLOR"silver"gtTests lt/tdgt
ltTDgttest1lt/tdgt lt/trgt lttrgt ltTD
BGCOLOR"silver"gtAverage lt/tdgt
ltTDgtavg1lt/tdgt lt/trgtlt/TABLEgt lt/BODYgtlt/HTMLgtlt/cfou
tputgt
12
Comparison between actual program and HTML output
at the browser
GRADES.CFM ltHTMLgt ltHEADgt ltTITLEgtCross,
Katherinelt/TITLEgt lt/HEADgt ltbody
text"purple"gt ltigtltbgtltH3gtCross,
Katherinelt/H3gtlt/igtlt/bgt ltTABLE BORDER"1"
CELLSPACING"1" CELLPADDING"2"gt lttrgt ltTD
BGCOLOR"silver"gtHomework lt/tdgt ltTDgt2,
2, 2, 2---2, 2, 2, 2---2, 2, 2, 2---2, 2, 2,
2lt/tdgt lt/trgt lttrgt ltTD BGCOLOR"silver"gtQui
zzes lt/tdgt ltTDgt35.0---23.0---20.0---17.0lt
/tdgt lt/trgt lttrgt ltTD BGCOLOR"silver"gtTes
ts lt/tdgt ltTDgt36.0lt/tdgt lt/trgt lttrgt
ltTD BGCOLOR"silver"gtAverage lt/tdgt
ltTDgt88.0lt/tdgt lt/trgt lt/TABLEgtlt/BODYgtlt/HTMLgt
GRADES.CFM ltCFQUERY DATASOURCE"grades"
NAME"all" gt SELECT FROM SInformation,
homework, quiz, test, average WHERE
(form.StudentID SInformation.id AND
SInformation.id homework.id AND
homework.id quiz.id AND quiz.id test.id AND
test.id average.id) lt/CFQUERYgt ltHTMLgt ltCFOUTPU
T QUERY"all"gt ltHEADgtltTITLEgtnamelt/TITLEgtlt/HEADgt
ltbody text"purple"gtltigtltbgtltH3gtnamelt/H3gtlt/igtlt/bgt
ltTABLE BORDER"1" CELLSPACING"1"
CELLPADDING"2"gt lttrgtltTD BGCOLOR"silver"gtHomework
lt/tdgt ltTDgtweek1---week2---week3---wee
k4lt/tdgtlt/trgt lttrgtltTD BGCOLOR"silver"gtQuizzes
lt/tdgt ltTDgtquiz1---quiz2---quiz3---quiz4
lt/tdgtlt/trgt lttrgtltTD BGCOLOR"silver"gtTests
lt/tdgt ltTDgttest1lt/tdgtlt/trgt lttrgtltTD
BGCOLOR"silver"gtAverage lt/tdgt
ltTDgtavg1lt/tdgtlt/trgt lt/TABLEgt lt/BODYgtlt/HTMLgtlt/cfou
tputgt
13
Web Page Demonstration
students.cfm
http//aurora.wells.edu/bthy/students.cfm
Sign in
List of students names and a link to sign in
form.cfm
Enter ID
A box will ask for students ID with the summit
button
grades.cfm
A grades table will be displayed for a specific
student
14
Active Server Pages (ASP)
15
GRADES.ASP lthtmlgtltheadgtlttitlegtYour
gradeslt/titlegtlt/headgt ltbody text"green"gt lt varS
tudentID request.form("id") dim oRSp Set
oRSpserver.createobject("ADODB.recordset") sqltex
t"Select from SInformation" sqltextsqltext "
WHERE SInformation.id" varStudentID
"" oRSp.open sqltext, "dsngrades" gt ltfont
size5gtltbgtltigtltoRSp("name")gtlt/igtlt/bgtlt/fontgtltbrgtlt
/fontgtltbrgt lt varStudentID request.form("id") di
m ORSp1 Set oRSp1server.createobject("ADODB.recor
dset") sqltext"Select from homework" sqltextsq
ltext " WHERE homework.id" varStudentID
"" oRSp1.open sqltext, "dsngrades" gt ltTABLE
BORDER"1" CELLSPACING"1" CELLPADDING"2"gt lttrgt
lttd BGCOLOR"silver"gtHomeworklt/tdgt lttdgtltoRSp1(
"week1")gt ---ltoRSp1("week2")gt
---ltoRSp1("week3")gtlt/tdgtlt/trgt lt dim
ORSp2 Set oRSp2server.createobject("ADODB.records
et") sqltext"Select from quiz" sqltextsqltext
" WHERE quiz.id"
varStudentID "" oRSp2.open sqltext,
"dsngrades" gt lttrgt lttd BGCOLOR"silver"gtQuizzes
lt/tdgt lttdgtltoRSp2("quiz1")gt
---ltoRSp2("quiz2")gt
---ltoRSp2("quiz3")gtlt/tdgtlt/trgt lt dim ORSp3 Set
oRSp3server.createobject("ADODB.recordset") sqlte
xt"Select from test" sqltextsqltext " WHERE
test.id" varStudentID "" oRSp3.open
sqltext, "dsngrades" gt lttrgt lttd
BGCOLOR"silver"gtTestslt/tdgt lttdgtltoRSp3("test1"
)gtlt/tdgtlt/trgt lt dim ORSp4 Set oRSp4server.create
object("ADODB.recordset") sqltext"Select from
average" sqltextsqltext " WHERE average.id"
varStudentID "" oRSp4.open sqltext,
"dsngrades" gt lttrgt lttd BGCOLOR"silver"gtAverage
lt/tdgt lttdgtltoRSp4("avg1")gtlt/tdgtlt/trgtlt/tablegt lt
/bodygtlt/htmlgt
STUDENTS.ASP lthtmlgtltheadgtlttitlegtStudentslt/titlegtlt
/headgt ltbody text"green" link"green"
vlink"green" alink"green"gt ltfont
color"green"gtltigtltbgt ltcentergtltfont size10
color"green"gtWelcome Everyone!lt/fontgt ltpgtltbrgt lt
dim oRSeof set oRSeofServer.createObject("ADODB.
recordset") oRSEOF.Open "SInformation",
"dsngrades" oRSeof.MoveFirst Do while NOT
oRSeof.EOF Response.write "lttdgt"
oRSeof("name") "lt/tdgtltbrgt" oRSeof.MoveNext Loop
gtlt/pgt ltA HREF"./form.asp"gtSIGN IN TO SEE YOUR
GRADESlt/Agt lt/centergtlt/bodygtlt/htmlgt
FORM.ASP lthtmlgtltheadgtlttitlegtSign
inlt/titlegtlt/headgt ltbody text"green"
link"green"gt ltfont size"6" color"green"gtPlease
enter your student's ID to view your
grades!lt/fontgt ltform method"POST"
action"grades.asp"gt ltpgtltinput type"text"
name"id" size"10"gtlt/pgt ltpgtltinput
type"submit" value"Submit"gtltinput type"reset"
value"Reset"gtlt/pgt lt/formgtlt/bodygtlt/htmlgt
16
Web Page Demonstration
students.asp
http//aurora.wells.edu/bthy/students.asp
Sign in
List of students names and a link to sign in
form.asp
Enter ID
A box will ask for students ID with the summit
button
grades.asp
A grades table will be displayed for a specific
student
17
Analysis
VISUAL BASIC FOR
COLD FUSION (CF)
ACTIVE SERVER
APPLICATION (VBA)
PAGES (ASP)




HTML output needs to

HTML output does

HTML outputs do not
be updated on the
not need to be updated
need to be updated on
server.
on the server.
the server.




VBA is not a Web

CF is a complete Web

ASP is a complete Web
server. It is just a
application server.
application server.
program.




HTML tags are

HTML tags are

HTML tags are
embedded inside the
embedded inside the
embedded inside the
program using Print
program as usual.
program as usual or
1.
within the quotation
marks if we use
Response.write




SQL statements are

SQL statements are

SQL statements are
not included.
included.
included.




VBA code is similar to

Cold Fusion code is

ASP code is similar as
Visual Basic code.
completely different
Visual Basic code.
from Visual Basic
code.




Harder coding

Easy coding

Hardest coding
18
Analysis