using Microsoft FrontPage and Microsoft Access Software

1
CMAC Web User Group January 15, 1998 State Armory
Media Room, Hartford
InterActive Web Page Development
using Microsoft FrontPage and Microsoft Access
Software
presented by

Linda DeConti e-mail linda.deconti_at_po.state.ct.
us voice 860-418-6248 -- fax
860-418-6495 Office of Policy and Management
(OPM) OPM Webmaster http//www.state.ct.us/opm/
CMAC Webmaster http//www.state.ct.us/cmac/ ITMA
Webmaster http//www.state.ct.us/itma/
Beverley Bellisio e-mail b.bellisio_at_snet.net
voice 860-418-6055 -- fax [phone]
Department of Mental Retardation 460 Capitol
Avenue Hartford, CT 06106 DMR Webmaster
http//www.state.ct.us/dmr
http//www.state.ct.us/
2
Outline

• What is ASP?
• Demonstration CMAC Membership Examplehttp//www.
  state.ct.us/cmac/scripts/member.asp
• Understanding the Files Understanding the CMAC
  Sample Files
• Step-By-Step Instructions
• Build Your Own DMR Example - How to Modify the
  Files http//vvv.state.ct.us/dmr/scripts/nwpro.asp
  
• Questions Answers
• APPENDIX other Examples, Tutorials and Resources

3
What is ASP?
Microsoft Active Server Pages (ASP) is a
server-side scripting environment that you can
use to create and run dynamic, interactive,
high-performance Web server applications. When
your scripts run on the server rather than on the
client, your Web server does all the work
involved in generating the Hypertext Markup
Language (HTML) pages that you send to browsers.
You need not worry whether a browser can process
your pages your Web server does all the
processing for it. You need only a working
knowledge of HTML to begin using ASP. Active
Server Pages is a feature of and can be used with
the following Web servers

• Microsoft Internet Information Server (IIS)
  version 3.0 on Windows NT Server
• Microsoft Peer Web Services Version 3.0 on
  Windows NT Workstation
• Microsoft Personal Web Server on Windows 95

ASP will work with any Database with an available
Open Data Base Connectivity (ODBC) compliant
driver, such as Microsoft Access
4
Demonstration

• CMAC Membership Example
• http//www.state.ct.us/cmac/scripts/member.asp

NOTE We plan to log-on to the Internet and
visit the CMAC Website and view the web page
listed above online. However, if technical
difficulties are encountered during the
presentation, we ask that you please visit the
site when you have an opportunity to do so after
the presentation. Thank you for your patience.
5
Understanding the Files
1 search tool (form)
1 2 3 4 5 6 7 8 9
1 database
9 different views of the data
6
Internet Browser View of Sample File cmac.asp
7
Step-By-Step

• STEP 1 Get CMAC Sample Files
• STEP 2 Import Sample Files into your AGENCY web
• STEP 3 Making the Database Connection with the
  Server
• Personal Web Server
• DOIT State Servers (VVV and WWW)
• STEP 4 How to Modify the Sample Files
• Understanding the Code

8
STEP 1 Get CMAC Sample Files

• Using your Windows Explorer create a temporary
  file folder called TEMP on your hard drive (for
  reference we will use C)
• Open your Internet Browser (e.g. Netscape or
  Internet Explorer) goto Address
  ftp//ftp.state.ct.us/pub/cmac/
• Click on the scripts.exe and choose save to disk
  then select your C/TEMP folder
• Using your Windows Explorer goto your C/TEMP
  folder and double click on the scripts.exe file
  to extract all the ZIP files
• After extraction is complete delete the
  scripts.exe file

9

• STEP 2 Import Sample Files into your AGENCY web
• Using Microsoft FrontPage, open your AGENCY web
• Create a scripts folder in your AGENCY web (File,
  New, Folder then rename as scripts). You must
  set the folder properties to allow scripts or
  programs to be run.
• Select and Import the files extracted (NOT the
  folder) from your C/TEMP folder into the new
  scripts folder in your AGENCY web. These files
  include the database cmac.mdb and all the active
  server pages (.asp)

10
STEP 3 Making the Database Connection with the
Server Personal Web Server (ONLY)

• In the Windows Control Panel open the ODBC Data
  Source Administrator
• Select System DSN (Data Set Name) and press ADD
  to define a new entry
• Select .mdb as the Data Source Type
• Locate the database CMAC.mdb using the Browse
  button
• Next accept all defaults to finish the
  registration process
• To check to see if the connection is working
  properly, in your Internet Browser goto your
  AGENCY web and open the web page /scripts/cmac.asp

NOTE We are still experiencing technical
difficulties with the connection to the PWS.
Currently the example works on VVV and WWW only.
However, we expect to provide more information on
this after these issues are resolved. Thank you
for your patience. IMPORTANT In addition to
your PWS if your website is located at DOIT on
WWW and/or VVV servers proceed to the next
section about the State Servers at DOIT (WWW
and/or VVV)
11
STEP 3 Making the Database Connection with the
Server DOIT State Servers (WWW and/or VVV)
IMPORTANT A database name must be unique on the
server. Please rename your copy of the CMAC
database cmac.mdb. Since the server is shared
by many agencies, please use the following naming
convention for all the databases that you plan to
register for your agency

• use the 8 digit limit for future portability
• the first 3 to 4 digits should be the abbreviated
  agency name (same as your AGENCY web) and
• the remaining digits should define the database
  in some unique manner (e.g. opmphone.mdb)

After you have renamed your database, send an
E-Mail to Michael Varney at michael.varney_at_po.stat
e.ct.us requesting that he register your database
name with the ODBC on the WWW and/or VVV
servers. Remember to specify 1) where the file
exists in your AGENCY web and 2) your new
database name.
12
STEP 4 How to Modify the Sample Files

• Open your AGENCY web in Microsoft FrontPage and
  goto the scripts folder
• Open the cmac.asp by double clicking on the file
• Disregard the broken headers and or replace them
  with your AGENCY header or header.htm file

• From the menu bar select View/HTML
• In the HTML view, scroll down to the section
  containing the ADODB.Connection

13
Understanding the Code
ltSet Conn Server.CreateObject("ADODB.Connection
") Conn.Open "cmac" Set RS
Conn.Execute("SELECT FROM members ORDER BY
Agency, Last, First")gt lttable border"0"
cellpadding"2" cellspacing"0 width"100"gt
lttrgt lttd nowrapgtltfont
size"3"gtltstronggtNamelt/stronggtlt/fontgtlt/tdgt
lttd nowrapgtltfont size"3"gtltstronggtAgencylt/str
onggtlt/fontgtlt/tdgt lttd nowrapgtltfont
size"3"gtltstronggtPhonelt/stronggtlt/fontgtlt/tdgt
lttd nowrapgtltfont size"3"gtltstronggtE-Maillt/st
ronggtlt/fontgtlt/tdgt lt/trgt ltDo While Not
RS.EOFgt ltname RS("First") " "
RS("Last")gt lttrgt lttd valign"top"
nowrapgtlt name gtlt/tdgt lttd
valign"top" nowrapgtlt RS("Agency") gtlt/tdgt
lttd valign"top"gtlt RS("Phone")
gtlt/tdgt lt if RS("E-Mail") "n/a" then
gtlttd valign"top"gtN/Alt/tdgtlt else gt
lttd valign"top"gtlta href"mailtolt
RS("E-Mail") gt"gtlt RS("E-Mail") gtlt/agtlt/tdgt
lt/trgt ltEnd if RS.MoveNext
Loop RS.Close Conn.Closegt
lt/tablegt
Part 1
Part 2
IMPORTANTCode Abbreviations Conn - Connection
SELECT - All Records EOF - End of File RS -
Record Set -1 TRUE and 0 FALSE
14
Understanding the Code
ltSet Conn Server.CreateObject("ADODB.Connection
") Conn.Open "cmac" Set RS
Conn.Execute("SELECT FROM members ORDER BY
Agency, Last, First")gt lttable border"0"
cellpadding"2" cellspacing"0 width"100"gt
lttrgt lttd nowrapgtltfont
size"3"gtltstronggtNamelt/stronggtlt/fontgtlt/tdgt
lttd nowrapgtltfont size"3"gtltstronggtAgencylt/str
onggtlt/fontgtlt/tdgt lttd nowrapgtltfont
size"3"gtltstronggtPhonelt/stronggtlt/fontgtlt/tdgt
lttd nowrapgtltfont size"3"gtltstronggtE-Maillt/st
ronggtlt/fontgtlt/tdgt lt/trgt
Part 1
NOTE The table shown above, although seemingly
misaligned, displays correctly in the browser
15
Understanding the Code
ltDo While Not RS.EOFgt ltname RS("First")
" " RS("Last")gt lttrgt lttd
valign"top" nowrapgtlt name gtlt/tdgt
lttd valign"top" nowrapgtlt RS("Agency")
gtlt/tdgt lttd valign"top"gtlt
RS("Phone") gtlt/tdgt lt if RS("E-Mail")
"n/a" then gtlttd valign"top"gtN/Alt/tdgtlt else gt
lttd valign"top"gtlta href"mailtolt
RS("E-Mail") gt"gtlt RS("E-Mail") gtlt/agtlt/tdgt
lt/trgt ltEnd if RS.MoveNext
Loop RS.Close Conn.Closegt
lt/tablegt
Part 2
Microsoft Access Database View of Sample File
cmac.mdb
16
Internet Browser View of Sample File cmac.asp
17
Understanding the Code
The code for all other pages is similar, except
for the member.asp web page that includes the
search tool. Details for this coding can be
found in the APPENDIX of this presentation.
1 2 3 4 5 6 7 8 9
18
Build Your Own

• DMR Example - How to Modify the Files
  http//vvv.state.ct.us/dmr/scripts/nwpro.asp

FrontPage Explorer View of Modified Files
19
Microsoft Access (16 bit Version) Database View
of Modified File dmrpro.mdb
20
FrontPage Editor View of Modified File
dmrpro.asp
21
Internet Browser View of Executed Web Page
dmrpro.asp
22
Understanding the Code
Part 1
23
Understanding the Code
Part 2
24
APPENDIX
Online Resources
An excellent resource that Bev Bellisio
discovered through MSDN is located
athttp//premium.microsoft.com/msdn/library
However, you need to register a USERID and
PASSWORD to access this site. This registration
is FREE. Using your Internet Browser and goto
the Address/URL above. You will need to use the
left frame to navigate through this website.
For an ASP tutorial goto For more details
goto

• Tools and Technologies
• FrontPage
• FrontPage Database Integration

• Developer Products
• Visual InterDev
• Active Server Pages
• Active Server Pages Scripting Guide

Other Useful Links Microsoft FrontPage
http//www.microsoft.com/frontpage/ VB script and
ASP http//www.microsoft.com/mind/0897/vbscriptasp
/vbscriptasp.htm VB script documents and
reference http//www.microsoft.com/vbscript/us/tec
hinfo/vbsdocs.htm
25
APPENDIX
Search Tool
Understanding the Code
ltform action"member.asp" method"POST"gt
ltinput type"text" size"32 name"keys"gt ltinput
type"submit" name"submit" value"Search"gt lt/form
gt
26
APPENDIX
Search Tool
Understanding the Code
ltpgtltfont size"5"gtltstronggtSearch
lt/stronggtlt/fontgt lta name"Results"gtltstronggtResults
lt/stronggtlt/agtltfont size"5"gtltstronggtlt/stronggtlt/fo
ntgtlt/pgt lt Who UCase(Request.Form("keys")) If
Len(Who) gt 0 then Set Conn Server.CreateObject
("ADODB.Connection") Conn.Open "cmac" Set RS
Conn.Execute("SELECT FROM members ORDER BY
Last, First") MatchesFound0gt lttable
border"0" cellpadding"2" cellspacing"0"
width"100"gt lttrgt lttdgtltfont
size"4"gtNamelt/fontgtlt/tdgt lttdgtltfont
size"4"gtAgencylt/fontgtlt/tdgt lttdgtltfont
size"4"gtPhonelt/fontgtlt/tdgt lttdgtltfont
size"4"gtE-Maillt/fontgtlt/tdgt lt/trgt
27
APPENDIX
Search Tool
Understanding the Code
lt Do While Not RS.EOF gt lt name RS("First")
" " RS("Last") All UCase(name
RS("Agency") RS("Phone") RS("E-Mail")) If
instr(All,Who)gt0 then MatchesFound -1gt lttrgt
lttd valign"top" width"15" nowrapgtlt
name gtlt/tdgt lttd valign"top"
nowrapgtlt RS("Agency") gtlt/tdgt lttd
valign"top"gtlt RS("Phone") gtlt/tdgt lt if
RS("E-Mail") "n/a" then gt
lttd valign"top"gtN/Alt/tdgt lt else gt lttd
valign"top"gtlta href"mailtolt RS("E-Mail")
gt"gtlt RS("E-Mail") gtlt/agtlt/tdgt lt/trgt lt end
if End if RS.MoveNext Loop RS.Close
Conn.Close if not MatchesFound then gt
lttrgtlttdgtSorry, no matching entries were
found.ltpgt lt end if end if gtlt/pgt lt/tdgtlt/trgtlt/tab
legt
28
APPENDIX
Random Photo Generation
Understanding the Code
ConneCT -- the official State of Connecticut
Website http//www.state.ct.us/
ltheadgt lt dim a dim b dim upperbound
'The number of pictures upperbound 7
randomize a Int((upperbound ) Rnd 1) b
a while b a a Int((upperbound ) Rnd
1) wend gt lt/headgt lttablegtlttrgt lttdgt lt select
case a case 1 gtltimg src"P09.jpg"
alt"Photo 1a"gt lt case 2 gtltimg src"P13.jpg"
alt"Photo 2a"gt lt case 3 gtltimg src"P14.jpg"
alt"Photo 3a"gt lt case 4 gtltimg src"P01.jpg"
alt"Photo 4a"gt lt case 5 gtltimg src"P18.jpg"
alt"Photo 5a"gt lt case 6 gtltimg src"P20.jpg"
alt"Photo 6a"gt lt case 7 gtltimg src"P15.jpg"
alt"Photo 7a"gt lt end select gt lt/tdgt lttdgt lt
select case b case 1 gtltimg src"P09.jpg"
alt"Photo 1b"gt lt case 2 gtltimg src"P13.jpg"
alt"Photo 2b"gt lt case 3 gtltimg src"P14.jpg"
alt"Photo 3b"gt lt case 4 gtltimg src"P01.jpg"
alt"Photo 4b"gt lt case 5 gtltimg src"P18.jpg"
alt"Photo 5b"gt lt case 6 gtltimg src"P20.jpg"
alt"Photo 6b"gt lt case 7 gtltimg src"P15.jpg"
alt"Photo 7b"gt lt end select gt lt/tdgtlt/trgtlt/table
gt
In the ltHEADgt set the following parameters
To display a set of images, use the following
basic code substituting in your image files and
names. You can use this with or without a
ltTABLEgt. If you wish to change the number of
image cases be sure to change the upperbound
number above to match the total selection.