server side programming asp1 server side programming database integration (cont.) internet systems...
TRANSCRIPT
Server Side Programming ASP 1
Server Side ProgrammingDatabase Integration (cont.)
Internet Systems Design
Server Side Programming ASP 2
Overview
Review of Server Side Programming– ASP– VBScript
Advanced Database Interfacing with SQL– Insert– Delete– Update
Examples with Code
Server Side Programming ASP 3
How can database be accessed by web browser?
Web browser Web browser
Web browser
Server Side Programminge.g. Microsoft IIS server :
ASP (VBScript, JScript, SQL)
Internet
Database
Server Side Programming ASP 4
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 5
What are Active Server Pages?
Runs on IIS Can provide compile-free application
environment
Server Side Programming ASP 6
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 7
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 8
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 9
VBScript Basics
VBScript Operators:– Arithmetic: +, -, *, /, ^– Comparison: =, <>, <, >, <=, >=– Logical (for Boolean variables): Not, And,
Or, Xor
Server Side Programming ASP 10
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 11
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 13
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 14
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 15
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 16
Homework 3
1st part will involve inserting, deleting, and updating records in the database used in Homework 2
Server Side Programming ASP 17
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 18
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 19
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 20
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 stringdim strSqlSet MyConn = Server.CreateObject("ADODB.Connection")MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Candy.mdb")
'grab the values from the formcandy = 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 themstrSql = "INSERT INTO Products (Candy, Price) SELECT '" & candy & "'," & price &""MyConn.Execute strSqlResponse.Write "Added the candy with the sql statement: " & strSqlSet MyConn = nothing
%><p></html>
Server Side Programming ASP 21
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 22
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 23
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 stringdim strSqlSet MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Candy.mdb")
'grab the candy name from the formcandy = Request.form("txtCandy")
'create the SQL statement that will insert the data to the table'remember text values need single quotes to wrapped around themstrSql = "Delete FROM Products Where Candy = '" & candy & "'"MyConn.Execute strSqlResponse.Write "Deleted the candy with the sql statement: " & strSqlSet 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 24
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 25
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 26
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 stringdim strSqlSet 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 themstrSql = "UPDATE Products SET Price = " & price & " WHERE Candy = '" & candy & "'"MyConn.Execute strSqlResponse.Write "Update the candy price with the sql statement: " & strSqlSet MyConn = nothing
%></body></html>