IST 331 Lectures Notes Chapter 12

DEMO 1

<HTML>

<HEAD>

sub cmd1_click()

msgbox "Hello World !",0,"Demo1-1"

end sub

sub cmd2_click()

msgbox "Hasta la vista Universe !",0,"Demo1-2"

end sub

</script>

</HEAD>

<BODY>

<B>DEMO1:</b> Intrinsic Input Buttons

<hr>

Push these buttons to get a message

</BODY>

</HTML>

DEMO 3

<HTML>

<HEAD>

sub cmd1_onclick()

dim myForm

dim theName

dim theMsg

set myForm = document.frmDemo3

theName = myform.txtUserName.value

theMsg = "Hello " & theName

msgbox theMsg, 0,"Demo3"

end sub

</script>

</HEAD>

<BODY>

<B>DEMO3:</b> A simple TEXTBOX & BUTTON

<P> Please enter your name here

</FORM>

</BODY>

</HTML>

DEMO 5

<HTML>

<HEAD>

<TITLE>Demo5: Movie-&-Munchies </TITLE>

sub vbTalk()

dim myForm

dim theName

dim theMsg

set myForm = document.frmDemo5

theName = myform.txtUserName.value

theMsg = "Movie type: " & theName

msgbox theMsg, 0,"Demo5"

'show the data provided by radio buttons

theMsg= ""

for i = 0 to 2

theValue = myform.radMovie(i).value

theCheck = myform.radMovie(i).checked

theMsg = theMsg & theValue & " " & theCheck & vbCR

msgbox theMsg, 0,"Demo5: " & myform.radMovie(0).name

'show the data provided by the check boxes

theMsg= ""

for i = 0 to 3

theValue = myform.junkFood(i).value

theCheck = myform.junkFood(i).checked

theMsg = theMsg & theValue & " " & theCheck & vbCR

msgbox theMsg, 0,"Demo5: " & myform.junkFood(0).name

end sub

</script>

</HEAD>

<BODY>

<B>DEMO5:</B> Using textBox, Radio Buttons, and Check Boxes

<P> <INPUT type=radio name = radMovie value=Comedy checked> Comedy

<BR> <INPUT type=radio name = radMovie value=Drama > Drama

<BR> <INPUT type=radio name = radMovie value=Action > Action

<P> <input type=checkbox name=junkFood value=popCorn checked> Pop Corn

<BR> <input type=checkbox name=junkFood value=hotDog > Hot Dog

<BR> <input type=checkbox name=junkFood value=pizza > Pizza

<BR> <input type=checkbox name=junkFood value=sodaPop checked> Soda

</FORM>

</BODY>

</HTML>

Classic ASP Model

ASP Server
/ Request
/ / Application

Session

Server

Local Resources
· Databases
· Graphics
· Documents
· Email
· etc. / Contents (collection)

Client

(Internet Explorer, Netscape, etc.) / Response

IST 331 – Lectures Notes – Chapter 12

Web-Databases Using ODBC – OLE DB – ADO

In this mini-project you will use classic ASP and ADO to reach an MS-Access database using the Internet.

Setp1. Create the ODBC Data Source.

1. Select Control Panel | Administrative Tools | Data Sources (ODBC).

2. Create a new System DSN. Use the Microsoft Access driver. Name your DSN webCompany. See the screens below (assume the table is in c:\Access-Data\CompanyXP.mdb)

3. Example 1. Get employee name

Use notepad to enter the following files. They should be saved as GetName01.asp, and GetName02.asp. Save the two files in the folder c:\inetput\webtest.

GetName01.asp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML>

<HEAD>

<title>Company Database - Get Name</title</HEAD>

<body>

<h1>Company database</h1>

<p>Enter Employee SSN <input type="text" name="txtSSN"</p>

<p<input type=submit name="btnGetName" value="Get name"</p>

</form>

</body>

</HTML>

GetName02.asp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>

<head>

<title>GetName02</title>

</head>

<body>

<h1>Company Database</h1>

dim theSSN

thessn = request("txtSSN")

response.Write("<p>Employee SSN: " & theSSN & "</p>")

dim myCnn

set myCnn = server.CreateObject("ADODB.Connection")

mycnn.Open("DSN=webCompany")

dim mySQL

mySQL = "Select Fname & ' ' & Lname as theName, salary from employee where ssn =" & theSSN

dim myRS

set myRS = server.CreateObject("ADODB.recordset")

myRS.Open mySQL, myCnn

if myRS.EOF then

response.Write ("<p>Sorry, no data found </p>")

else

response.Write("<p> Name: " & myRS("theName") & "</p>")

response.Write("<p> Salary: " & myRS("salary") & "</p>")

end if

</form>

</body>

</html>

4. Example 2. Show PROJECT table

Use notepad to enter the following file. It should be saved as WebForm001.asp. Place the file in the folder c:\inetput\wwwroot.

<html>

<head>

<title>Using Classic-ASP and ODBC</title>

</head>

<H1 align="center"> <font color="#0000FF"<p>Company - Projects</p> </font>

</h1>

<a href="http://grail.cba.csuohio.edu/~matos" target="_parent">Back

to Home-Page V.Matos </a</font>

</h4>

on error resume next

'Using ODBC to call a predefined Access DataSource

Set cn = server.createobject("ADODB.Connection")

cn.Open "DSN=webCompany"

'Apply SQL to get the Project table using the current connection

Set rs = server.createObject("ADODB.Recordset")

mySQL = "select * from project"

rs.Open mySQL , cn

'Check for errors

if cn.errors.count > 0 then

'Get out of here when invalid table name, data type, etc.

response.write( "<H1> Oh Oh - Problems. </H1>")

response.end

end if

'Syntax is good, table exists, but no data

if (rs.eof) then

response.write( "<H1 > Sorry, there is no data in the PROJECT table </H1>")

response.end

end if

<table bgcolor=yellow border=1 >

'put captions on top of the Project table

response.write ("<TR>")

For iField = 0 To rs.Fields.Count - 1

response.write ( " <TD>" & rs(iField).Name & " </TD> " )

response.write("</TR>")

'put Project data in the HTML table

dim lineCounter

lineCounter=-1

rs.MoveFirst

While Not rs.EOF

lineCounter = lineCounter + 1

'alternate background color white to lightYellow

if (lineCounter mod 2 = 0) then

response.write ("<TR bgcolor= white>")

else

response.write ("<TR bgcolor= lightyellow>")

end if

'put data in the table cells

For iField = 0 To rs.Fields.Count - 1

response.write ( " <TD align=center>" & rs(iField) & "</TD>")

response.write ("</TR>")

rs.MoveNext

Wend

</table>

'dump the connection and recordset

Set rs = Nothing

Set cn = Nothing

<BR>

<a href="http://www.microsoft.com" target="_parent">Click here to jump to Microsoft site</a>

</body>

</html>

5. Place this file under the c:/InetPub/wwwroot subdirectory of your computer (you must have the IIS Internet Information Service installed in you machine. It is available in Windows 2000 (service pack 2?), NT, and Windows XP Professional)

6. Click on Internet Explorer. The URL should be http://localhost/webform1.asp

Example3

<html>

<%@ LANGUAGE = VBSCRIPT%>

<head>

<title> Demo1: ASP Tutorial - ADO Connection</title>

</head>

<body>

<CENTER<H1<font color=#ff0066red>Using Active Server Pages to access an ODBC database</font</H1</CENTER>

Set conn = Server.CreateObject("ADODB.Connection")

Set curPerson = Server.CreateObject("ADODB.Recordset")

'Oracle database connection

'conn.Open "DSN=sora;uid=scott;pwd=tiger;"

'Access database connection

conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\Access-Data\CompanyXP.mdb"

mySQL = "SELECT * from employee"

Set curPerson = conn.Execute(mySQL)

<TR<TH>Last Name </TH<TH>First </TH<TH>SSN </TH<TH>Dno </TH</TR>

<% Do While Not curPerson.EOF %>

<TR>

<TD<%= curPerson("Lname")%> </TD>

<TD<%= curPerson("Fname")%</TD>

<TD<%= curPerson("SSN")%> </TD>

<TD<%= curPerson("Dno")%</TD>

</TR>

<!-Next Row = Record Loop and add to html table-->

curPerson.MoveNext

LOOP

curPerson.Close

conn.Close

</TABLE>

<!--

Sub window_onLoad()

MsgBox "This Shows Client-Side Visual Basic Script!",0,"Sample Box"

End Sub

-->

</SCRIPT>

<HR>

<BR> Web navigation: <A HREF = "http://www.msn.com">Click here to reach other web page.</A>

<P> Sending e-Mail: <a HREF="mailto:">Click here to email Mr. Dilbert at </a>

</body>

</html>

V. Matos IST331-Web-ASP-ADO-Intro Page. 2