ASP-Session-3

Transcript ASP-Session-3

ASP Application Development
Session 3
Topics Covered
Using SQL Statements for:
– Inserting a tuple
– Deleting a tuple
– Updating a tuple
Using the RecordSet Object for:
– Inserting a tuple
– Deleting a tuple
– Updating a tuple
ASP Session Object
The Session object is used to store information
about, or change settings for a user session.
Variables stored in the Session object hold
information about one single user, and are
available to all pages in one application
<%
Session("username")=“JoeShmoe"
Session("age")=23
%>
Welcome <%Response.Write(Session("username"))%>
The example below sets a timeout interval of 5 minutes:
<%
Session.Timeout=5
%>
ASP Application Object
A group of ASP files that work together to
perform some purpose is called an application.
The Application object in ASP is used to store
variables and access variables from any page,
just like the Session object.
The difference is that all users share ONE
Application object, while with Sessions there is
one Session object for each user.
<%
Sub Application_OnStart
application("vartime")=""
application("users")=1
In the example we have created
two Application variables:
"vartime" and "users".
End Sub
%>
There are
<%
Response.Write(Application("users"))
%> active connections.
You can access the
value of an Application
variable like this.
Controlling Application Behavior
You can create Application variables in
"Global.asa"
ASP The Global.asa file:
– The Global.asa file is an optional file that can
contain declarations of objects, variables, and
methods that can be accessed by every page
in an ASP application
ASP Including Files :
– The #include directive is used to create
functions, headers, footers, or elements that
will be reused on multiple pages
<html><body>

</p>Hello…… <p>
</p>
</body> </html>
RecordSet Object
Recordset objects can support two types of
updating:
– Immediate updating
all changes are written immediately to the
database once you call the Update method
– Batch updating
the provider will cache multiple changes
and then send them to the database with
the UpdateBatch method
Cursor Types
In ADO there are 4 different cursor types defined:
– Forward-only cursor
Allows you to only scroll forward through the Recordset.
Additions, changes, or deletions by other users will not be visible.
– Keyset cursor
Like a dynamic cursor, except that you cannot see additions by
other users, and it prevents access to records that other users have
deleted. Data changes by other users will still be visible.
– Dynamic cursor
Allows you to see additions, changes, and deletions by other users.
– Static cursor
Provides a static copy of a recordset for you to use to find data or
generate reports. Additions, changes, or deletions by other users
will not be visible. This is the only type of cursor allowed when
you open a client-side Recordset object.
Cursor Types
Lock Types
Inserting a Tuple using SQL Statement
User inputs new data in a form
Gather that data and create an SQL
statement
Open connection to the database
Execute the SQL statement
Syntax for SQL statement:
– insert into tablename (attribute list) Values
(actual values)
– Example
insert into tblStudent (StudentNo, FirstName, LastName,
Year, Major) values (45, ‘Joe’, ‘Shmoe’, 2000, MIS)
insert into tblStudent values (45, ‘Joe’, ‘Shmoe’, 2000, MIS)
Inserting a Tuple – Form to input data
<html> 
Call the ASP page
<body>
<form method=post action=insert_student.asp>
<br><br>
<center>
<font size=5 face=arial color=#336699>
<b>Student Information Form</b><br>
<br></font>
<font size=4 face=arial color=black>
Please enter the following student information and click on the Add button.<br>
<br><br>
Text Boxes for attributes
<table>
<tr>
<td>Student Number</td> <td> <input type=text name=sno size=20></td></tr>
<tr><td>First Name </td> <td> <input type=text name=fname size=20></td></tr>
<tr><td>Last Name </td> <td> <input type=text name=lname size=20></td></tr>
<tr><td>Year</td> <td> <input type=text name=year size=20></td></tr>
<tr><td>Major </td> <td> <input type=text name=major size=20></td></tr>
</table><br>
<input type=submit value="Add Student Information">
</center>
</font>
Submit button to send the values
</form>
</body>
Demo the Example
Inserting a Tuple – ASP Page
<html> <head> 
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Get Product from Form</title>
</head>
<%
'get the user entered data
sn=request.form("sno")
fn=request.form("fname")
Get the user input and store in variables
ln=request.form("lname")
yr=request.form("year")
mj=request.form("major")
'Establish the connection with the admin database
set my_conn= Server.CreateObject("ADODB.Connection")
'Construct the connection string using relative path for the database file
myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
server.mappath("db/admin.mdb")
DSN-less connection string
'Open the connection to the data source
my_conn.Open myvar
Construct the DML statement using the
variables that contain the user values
'create sql query using the user entered data
StrSql= "insert into tblStudent (StudentNo, FirstName, LastName, Year, Major) values (" & _
sn & ",'" & fn & "','" & ln & "'," & yr & ",'" & mj & "');"
'continued on the next slide
String values are delimeted using single quotes
Need to add comma to separate each value
Inserting a Tuple – ASP Page (continued)
For debugging purposes, it is a good idea
to print out the sql statement that is constructed
'print out the SQL query to see if it is constructed properly
response.write "<h3>SQL Statement Created: </h3> &nbsp&nbsp&nbsp" & StrSql
'execute the query which will insert the tuple into the table tblStudent
my_conn.Execute (StrSql)
Execute the SQL statement
'display message saying the typle is inserted
response.write "<br><br>Inserted the tuple for <b>" & fn & " " & ln & "</b>"
my_conn.Close
%>
Just a confirmation message
<br><br>Click on this <a href=select_student.asp><b>link</b></a> to see
if the student has been added.
<body>
</body>
Call another ASP page that lists all the student names
</html>
in the table to make sure the tuple was actually inserted.
Don’t have to do this in your application
Deleting a Tuple
User identifies the tuple to delete
Construct the delete SQL statement
Execute the SQL statement
Syntax of delete statement
– DELETE From Tablename WHERE
primarykey = value
– Example
"DELETE FROM tblStudent WHERE
StudentNo = 87"
Deleting a Tuple Example – Get User Input

<html> <head> <title>Get Product from Form</title> </head>
<% 'Establish the connection with the nowrthwind database
set my_conn= Server.CreateObject("ADODB.Connection")
'Construct the connection string using relative path for the database file
myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("db/admin.mdb")
'Open the connection to the data source
my_conn.Open myvar
'Get the student information from the tblStudent table
StrSql= "Select * from tblStudent"
set rs = my_conn.Execute (StrSql)
%>
<body>
<h1>Deleting a Student from tblStudent Table</h1>
<h3>Please select a student and click on the Delete Student button <br> to remove the student.</h3>
<form method="post" action="delete_SQL.asp">
Create the drop-down list called “student”
Select a student Name: &nbsp
<select name="student">
<% do while not rs.eof %>
<option value="<%=rs("StudentNo")%>"><%=rs("FirstName")%>&nbsp<%=rs("LastName")%></option>
<% rs.movenext
loop
my_Conn.Close
For each option, the value is set as the StudentNo, which
will be sent to the next ASP page
set my_conn = nothing
%>
</select> <br><br><input type="submit" value="Delete Student"></form> </body> </html>
Demo the Example
Deleting a Tuple Example – ASP page
<html> <head>
<title>Delete a Tuple</title> </head>

<% 'get the student number that was selected
selected_id = request.form("student")
'Establish the connection with the admin database
set my_conn= Server.CreateObject("ADODB.Connection")
'Construct the connection string using relative path for the database file
myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
server.mappath("db/admin.mdb")
'Open the connection to the data source
my_conn.Open myvar
'create the sql query using the user entered data for student number
StrSql= "DELETE FROM tblStudent WHERE StudentNo = " & selected_id
'print out the SQL query to see if it is constructed properly
response.write "<h3>SQL Statement Created: </h3> &nbsp&nbsp&nbsp" & StrSql
'execute the query which will insert the tuple
my_conn.Execute (StrSql)
my_conn.Close
%>
<br><br>Click on this <a href=select_student.asp><b>link</b></a>
to see if the student has been deleted.
<body> </body></html>
Updating a Tuple
User identifies the tuple to update
Get the new values for attributes
Create the appropriate SQL statement
with new values
Execute the SQL statement
Syntax of update statement
UPDATE Tablename
AttributeName
AttributeName
WHERE primarykey
– Example
SET
= new value
= new value
= value
UPDATE tblStudent SET LastName = “Shmoe”
WHERE StudentNo = 87"
Updating a Tuple - Example
<html> <body>

<% set conn=Server.CreateObject("ADODB.Connection")
myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
Conection string
server.mappath("db/admin.mdb")
conn.Open myvar
Get all the tuples from the tblStudent table
strsql = "SELECT * FROM tblStudent"
and create the recordset object “rs”
set rs = conn.execute(strsql) %>
<h2>Student Information </h2>
<table border="1" width="60%"> <tr>
Create a table and output the attribute names
<%
as the first row of the table
for each x in rs.Fields
response.write("<th>" & ucase(x.name) & "</th>")
next
Subsequent rows contain values for each student.
%> </tr>
First field in each row of the table corresponds to
<% do until rs.EOF %> <tr>
StudentNo and contains a form with a submit
<%
button. When clicked, calls the next ASP page
for each y in rs.Fields
if y.name="StudentNo" then%>
<td> <form method="post" action="update_student.asp"> <center />
<input type="submit" style="width:75px" name="StudentNo" value="<%=y.value%>"></td></form>
<%else%>
<td><%Response.Write(y.value)%></td>
<%end if
Next
Output the values of other attributes in each row
rs.MoveNext%>
of the table.
</tr>
<%
End of the do … until loop
loop
conn.close
%>
</table></body></html>
Demo the Example
Updating a Tuple Continued (ASP Page)
<html><body><h2>Update Student Record</h2>

<% set conn=Server.CreateObject("ADODB.Connection")
myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
server.mappath("db/admin.mdb")
The first time this page is called, it will
conn.Open myvar
have value for only “StudentNo”
sid=Request.Form("StudentNo")
strsql = "SELECT * FROM tblStudent WHERE StudentNo=" & sid
If first time, execute sql and
if Request.form("FirstName")="" then
retrieve information for the
set rs = conn.execute(strsql) %>
selected sid. Create a form with
<form method="post" action="update_student.asp"> <table>
text fields to edit
<%for each x in rs.Fields%>
<tr> <td><%=x.name%></td> <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
<%next%>
Output the field name followed by
</tr></table><br><br>
a text box with the value. User can
<input type="submit" value="Update record"> </form>
edit the values and click on submit.
<%
The same ASP page is called again
else
sql="UPDATE tblStudent SET "
sql=sql & "FirstName='" & Request.Form("FirstName") & "',"
sql=sql & "LastName='" & Request.Form("LastName") & "',"
sql=sql & "Year=" & Request.Form("Year") & ","
sql=sql & "Major='" & Request.Form("Major") & "'"
sql=sql & " WHERE StudentNo=" & sid
The second time around, the text fields
on error resume next
will have the new values. Construct an
conn.Execute sql
Update statement using those values
end if
and execute that query.
conn.close %>
</body>
</html>
Adding A Record Through RecordSet
<html> <head> <title>Add a Record</title> </head> <body>

<% 'Establish the connection with the admin database
set my_conn= Server.CreateObject("ADODB.Connection")
'Construct the connection string
myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
server.mappath("db/admin.mdb")
Create an instance of RecordSet object
'Open the connection to the data source
called oRS and set the appropriate
my_conn.Open myvar
CursorType and LockType
Dim oRS
Set oRS=Server.CreateObject("ADODB.Recordset")
Execute SQL statement and get the
oRS.CursorType = 2
current records from the table
oRS.LockType = 3
strsql = "select * from tblStudent"
Add a new record to the recordset object
oRS.Open strsql, my_conn
oRS.AddNew
oRS.Fields("StudentNo")=Request.Form("sno")
Provide values for each of the attributes
oRS.Fields("FirstName")=Request.Form("fname")
oRS.Fields("LastName")=Request.Form("lname")
oRS.Fields("Year")=Request.Form("year")
Update the recordset object which then
oRS.Fields("Major")=Request.Form("major")
updates the base table
oRS.Update
'display message saying the typle is inserted
response.write "<br><br>Added the record for <b>" & Request.Form("fname") & _
" " & Request.Form("lname") & "</b>"
oRS.Close
my_conn.Close
%>
<br><br>Click on this <a href=select_student.asp><b>link</b></a>
to see if the student has been added.
</body></html>
Demo the Example
Deleting a Record using RecordSet Object
<html> <head>

<title>Delete a Tuple</title> </head>
<% 'get the student number that was selected
selected_id = request.form("student")
Get the selected StudentNo and
store it in a variable
'Establish the connection with the admin database
set my_conn= Server.CreateObject("ADODB.Connection")
'Construct the connection string
myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
server.mappath("db/admin.mdb")
'Open the connection to the data source
my_conn.Open myvar
Create the RecordSet object oRS and set
The appropriate cursor type and lock type
Dim oRS
Set oRS=Server.CreateObject("ADODB.Recordset")
oRS.CursorType = 2
Open the oRS recordset object by executing
oRS.LockType = 3
the SQL statement (strsql) through the
strsql = "select * from tblStudent"
connection object (my_conn)
oRS.Open strsql, my_conn
oRS.Find "StudentNo=" & selected_ID
oRS.delete
my_conn.Close %>
Find the record with the selected StudentNo
The current record pointer points to that record
Delete that record by calling the delete method
<br><br>Click on this <a href=select_student.asp><b>link</b></a>
to see if the student has been deleted.
<body> </body></html>
Demo the Example
ASP Exercise 3
Create an ASP application to manage a table
Use the Employee table in exercise3.mdb
Initial page with choices for viewing the data, adding,
deleting, or updating a record
View option
– Call an ASP page to display the records in the employee table
Add option
– Call an ASP page for entering employee data
– Call another ASP page for adding the record to the
employee table
Delete option
– Call an ASP page to indicate the record to be deleted
– Call an ASP page to delete the record
Update option
– Call an ASP page to indicate the record to be updated
– Call an ASP page to update the record

ASP-Session-3

Transcript ASP-Session-3

Directory