Server Side Programming Database Integration (cont.)

bubblesradiographerServers

Dec 4, 2013 (3 years and 11 months ago)

96 views

Server Side Programming ASP

1

Server Side Programming

Database 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 Programming

e.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

12

Advanced Database Interfacing

Server Side Programming ASP

13

Database Connectivity


When a database is tied to a web site, .ASP
uses an object library called
A
ctiveX
D
ata
O
bjects, 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


1
st

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">&nbsp;

<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 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

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">&nbsp;

<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 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

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 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

27

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