Transcript ISD_July12
Server Side Programming
Database Integration (cont.)
Internet Systems Design
Server Side Programming ASP
1
Overview
Review of Server Side Programming
– ASP
– VBScript
Advanced Database Interfacing with SQL
– Insert
– Delete
– Update
Examples with Code
Server Side Programming ASP
2
How can database be accessed by
web browser?
Server Side Programming
e.g. Microsoft IIS server :
ASP (VBScript, JScript, SQL)
Database
Internet
Web browser
Web browser
Web browser
Server Side Programming ASP
3
ASP Definition
“Microsoft Active Server Pages (ASP) is a server-side
scripting environment that you can use to create and
run dynamic, interactive Web server applications.
With ASP, you can combine HTML pages, script
commands, and COM components to create
interactive Web pages or powerful Web-based
applications, which are easy to develop and modify.”
-Microsoft’s Latest Definition
Server Side Programming ASP
4
What are Active Server Pages?
Runs on IIS
Can provide compile-free application
environment
Server Side Programming ASP
5
ASP Basics
ASP file is a text file with the extension
.asp
it contains any combination of:
– Text
– HTML Tags
– ASP Script Commands/Components
Server Side Programming ASP
6
ASP Script
ASP Script could be VBScript or Jscript
(ECMAScript)
A script is a series of commands or
instructions.
Script command instructs the web
server to perform an action.
VBScript is similar to Visual Basic and
Visual Basic for Application (VBA).
Server Side Programming ASP
7
VBScript Basics
Not case sensitive
Declaring Variables
VBScript does not require variable
declarations, but it is good scripting
practice to declare all variables before
using them. To declare a variable in
VBScript, use the Dim, Public, or
Private statement.
Server Side Programming ASP
8
VBScript Basics
VBScript Operators:
– Arithmetic: +, -, *, /, ^
– Comparison: =, <>, <, >, <=, >=
– Logical (for Boolean variables): Not, And,
Or, Xor
Server Side Programming ASP
9
Running ASP
Ensure .asp extensions are enabled in IIS
Save .htm, .asp, and .mdb on server in same
folder (or give full folder extensions of file
locations in your code)
Client accesses the file in similar fashion to
.htm (or .html) file
Server detects .asp extension and runs script
within ASP tags
Results sent to client
Server Side Programming ASP
10
Using ASP
Homework 2 review
– HTML form took input from a list box and invoked
the .asp file
– The .asp file retrieved data from a database using
VB script, SQL and displayed the results to the
user
Homework 3 will expand on these concepts
Server Side Programming ASP
11
Advanced Database Interfacing
Server Side Programming ASP
12
Database Connectivity
When a database is tied to a web site, .ASP
uses an object library called ActiveX Data
Objects, or ADO
– E.g. The Connection object:
• Set objConn =
Server.CreateObject("ADODB.Connection")
Several ways to connect to a database
– http://www.engineering.uiowa.edu/~ie181/Docume
nts/DatabaseConnectionsFromASP.htm
Server Side Programming ASP
13
Structured Query Language
SQL is a standard computer language
for accessing and manipulating
databases
SQL (Structured Query Language) is a
syntax for executing queries. But the
SQL language also includes a syntax to
update, insert, and delete records.
– http://www.w3schools.com/sql/sql_intro.asp
Server Side Programming ASP
14
Structured Query Language
These query and update commands together
form the Data Manipulation Language (DML)
part of SQL:
SELECT - extracts data from a database
table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
INSERT INTO - inserts new data into a
database table
Server Side Programming ASP
15
Homework 3
1st part will involve inserting, deleting,
and updating records in the database
used in Homework 2
Server Side Programming ASP
16
Examples of Insert, Delete, Update
Statements
"INSERT INTO Products (Candy, Price)
SELECT '" & candy & "'," & price &""
"DELETE FROM Products Where
Candy = '" & candy & "'"
"UPDATE Products SET Price = " &
price & " WHERE Candy = '" & candy &
"'"
Server Side Programming ASP
17
Example 1: Inserting Records
See http://128.255.21.191/Example5/AddCandy.htm
Only viewable in the ALF lab
Step 1: Create an Access Database
Server Side Programming ASP
18
Example 1: Inserting Records
Step 2: Create a .html form
<html>
<head>
<title>Candy</title>
</head>
<body>
<form name=frmAddCandy action="addCandy.asp" method=post>
<p>Candy: <input type="text" name="txtCandy" size="20"> Price:
$<input type="text" name="txtPrice" size="20"></p>
<p><input type="submit" value="Add Candy" name="butAdd">
<input type="reset" value="Clear Form" name="butCancel" size="20"></p>
<p><a href='DeleteCandy.htm'>Delete Candy</a></p>
</form>
</body>
</html>
Server Side Programming ASP
19
Example 1: Inserting Records
Step 3: Create the .asp file
<%@ LANGUAGE='VBSCRIPT'%>
<html>
<head>
</head>
<body>
<%
'strSql will be the variable used to hold the sql statement string
dim strSql
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Candy.mdb")
'grab the values from the form
candy = Request.form("txtCandy")
price = Request.Form("txtPrice")
'create the SQL statement that will insert the data to the table
'remember text values need single quotes wrapped around them
strSql = "INSERT INTO Products (Candy, Price) SELECT '" & candy & "'," & price &""
MyConn.Execute strSql
Response.Write "Added the candy with the sql statement: " & strSql
Set MyConn = nothing
%>
<p>
</html>
Server Side Programming ASP
20
Example 1: Inserting Records
Step 4: Save the files to a folder in the wwwroot folder, run
the example and view changes…notice Licorice was added
to the Products table
Server Side Programming ASP
21
Example 2: Deleting Records
http://128.255.21.191/Example5/deleteCandy.htm
Step 1: Create a .html form
<html>
<head>
<title>Candy</title>
</head>
<body>
<form name=frmDeleteCandy action="deleteCandy.asp" method=post>
<p>Candy: <input type="text" name="txtCandy" size="20"> </p>
<p><input type="submit" value="Delete Candy" name="butDelete">
<input type="reset" value="Clear Form" name="butCancel" size="20"></p>
<p><a href='addCandy.htm'>Add Candy</a></p>
</form>
</body>
</html>
Server Side Programming ASP
22
Example 2: Deleting Records
Step 2: Create the .asp file
<%@ LANGUAGE='VBSCRIPT'%>
<html>
<head>
</head>
<body>
<%
'strSql will be the variable used to hold the sql statement string
dim strSql
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Candy.mdb")
'grab the candy name from the form
candy = Request.form("txtCandy")
'create the SQL statement that will insert the data to the table
'remember text values need single quotes to wrapped around them
strSql = "Delete FROM Products Where Candy = '" & candy & "'"
MyConn.Execute strSql
Response.Write "Deleted the candy with the sql statement: " & strSql
Set MyConn = nothing
%>
<p>
<a href='addCandy.htm'>Add Candy</a> - <a href='updateCandy.htm'>Update Candy</a> - <a href='deleteCandy.htm'>Delete Candy</a>
</p>
</body>
</html>
Server Side Programming ASP
23
Example 2: Deleting Records
Step 3: Save the files to a folder in the wwwroot folder, run
the example and view changes…notice Licorice was deleted
from the Products table
Server Side Programming ASP
24
Example 3: Updating Records
http://128.255.21.191/Example5/updateCandy.htm
Step 1: Create a .html form
<html>
<head>
<title>Candy</title>
</head>
<body>
<form method="Post" action="updateCandy.asp">
<p> <select name = "selCandy" method ="post" size ="1">
<option selected value="Gum">Gum</option>
<option selected value="Suckers">Suckers</option>
<option selected value="Taffy">Taffy</option>
<option selected value="Skittles">Skittles</option>
<option selected value="M&Ms">M&Ms</option>
<option selected value="Lifesavers">Lifesavers</option>
<option selected value="Snickers">Snickers</option>
</select> New Price:<Input type='text' name='txtPrice' size=10>
<input type="submit" value="Update Price">
</p>
</form>
</body>
</html>
Server Side Programming ASP
25
Example 3: Updating Records
Step 2: Create the .asp file
<%@ LANGUAGE='VBSCRIPT'%>
<html>
<head>
</head>
<body>
<%
'strSql will be the variable used to hold the sql statement string
dim strSql
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Candy.mdb")
'grab the candy name from the form
candy = Request.form("selCandy")
price = Request.form("txtPrice")
'create the SQL statement that will insert the data to the table
'remember text values need single quotes to wrapped around them
strSql = "UPDATE Products SET Price = " & price & " WHERE Candy = '" & candy & "'"
MyConn.Execute strSql
Response.Write "Update the candy price with the sql statement: " & strSql
Set MyConn = nothing
%>
</body>
</html>
Server Side Programming ASP
26
Example 3: Updating Records
Step 3: Save the files to a folder in the wwwroot folder, run
the example and view changes…notice Snicker’s price was
changed from $4.00 to $0.25 in the Products table
Server Side Programming ASP
27