NOTE: I am looking for an ASP web server and until I locate one the examples and most of the downloads on this page will not work. The PHP page has working examples that are usually like those here on the ASP page. Hugh Poynor; June, 2004

ASP database connectivity supports a variety of popular database formats, and many ASP applications use database connections. It is popular in eCommerce because it is efficient and works with a wide variety of incompatible database formats.

You will be introduced to a newspaper site that uses simple text files to update stories on a news page. This example illustrates how to write a Web page by reading small text files that hold news stories. You also will be introduced to text database files, cookies, and session variables. You can learn this without having database skills necessary for more complex tasks like shopping carts.

Also, you will see how to create an online catalog and shopping cart. Learning to program this level of ASP requires that you already have an understanding of databases, SQL and recordset programming. Thus you may want to review your SQL and ADO recordset notes, or find resources on the Web to review.

Data in text files

Content in text files

In this information era, headline stories change rapidly, and pages have to be rewritten daily. ASP can insert the latest news stories on to a news site. Because several news departments would contribute to the central news page, you would want to prevent having too many authors working on a single page. You can use ASP to avoid the problem of having too many authors working on one page.

To employ the help of ASP, you must first assign sections of the main news page to various departments, each responsible for only their part of the main page. Then, use ASP to assemble the sections and create the composite page. Below is the fictitious "News Analyst" section we will use as an example of a finished section.

There are three parts to this small section.

Outer frame "On CNN TV" (a background)
Image (optional)
Story description

Our imaginary department is responsible only for updating the last two. The background ("On CNN TV") remains constant on the page.

The imaginary CNN news piece will be rewritten daily as fresh stories break. We want a CNN department to place all the markup for the small "On CNN TV" region in a text file on the Web server. The larger page will contain many other regions and their content as well.

The small text file will be called newsanalysis.txt, and it will contain all the HTML markup and content necessary to fit into the allotted space on the large page. Here is that markup in the text file and how it looks when rendered alone.

<img style="float:left" src="images/web-wolf_blitzer.jpg"
       alt="Wolf head" width="66" height="51" border="0" />
<a href="#" style="color:blue;font-weight:bold">
Wolf Blitzer Reports:</a><br />
Operation Anaconda: mission accomplished?<br />
Or will al Queda rise to fight again? Join Wolf Blitzer
in the "War Room." <strong>(7p.m. E.T.)</strong>

Wolf Blitzer Reports:
Operation Anaconda: mission accomplished?
Or will al Queda rise to fight again? Join Wolf Blitzer in the "War Room." (7p.m. E.T.)

Parallel code for PHP can be found in the PHP lesson.

Scripting content from file

The ASP source file below opens and reads newsanalysis.txt after a server connection is made. Then, it writes the contents of the file (which is HTML) to the page. It is a standard practice to mix VBScript and HTML on an ASP page. We have colored the HTML green to make it clearer.

<%@ language="vbscript" %>
<% option explicit %>
<html><body>
<div class="newsanalyst">
    <% response.buffer = True
    Dim fs, f, txtFile
    Const ForReading=1
    Set fs = Server.CreateObject("Scripting.FileSystemObject")
    f = Server.MapPath ("../db/newsanalysis.txt")
    Set txtFile = fs.OpenTextFile( f, ForReading )
    Response.write txtFile.ReadAll  ' read text then write to the page
    txtFile.Close
    %>
</div>
</body></html>

For a real page of news stories this process would be repeated many times, once for each region on the news page, and the source code above would need to open and read many text files.

Parallel code for PHP can be found in the PHP lesson.

Writing ASP - news

The HTML and ASP source files below create and write news stories for the example above. The first program, WolfNews.html, is used by someone writing the news story to enter the story and the HTML markup for use by the next program. See the Wolf Blitzer story above for an example of the HTML markup. The next program, GetNews.asp, pulls the new markup from the HTML form then writes it into newsanalysis.txt. We have colored the HTML green to make it clearer.

These steps are examples of creating a news story and placing it on the server. They do not display the news story. See the example above (Content in text files) for displaying the news stories.

WolfNews.html

<html><head><title>News Story</title></head>
<body><h3>Enter in News story</h3>
<form name="frmnews" method="post" action="GetNews.asp">
<table border="0" cellspacing="0" cellpadding="5">
<tr><td>Story :</td><td>
<textarea name="story" rows="5" cols="50"></textarea>
</td></tr>
<tr><td colspan="2" align="center">
<input type="submit" value="submit"> </td></tr>
</table></form></body></html>

GetNews.asp

<%@ language="vbscript" %>
<% option explicit %>
<html><body>
    <% response.buffer = True
    Dim fs, f, txtFile
    Const ForWriting=2, CreateOK=true
    Set fs = Server.CreateObject("Scripting.FileSystemObject")
    f = Server.MapPath ("../db/newsanalysis.txt")
    Set txtFile = fs.OpenTextFile( f, ForWriting, CreateOK )
    txtFile.WriteLine Request("story")
    txtFile.Close
    response.write("Successfull action")
    %>
</body></html>

Upload files instead of rewriting files

ASP can be used to upload intact files as well as allowing users to type in file contents as in the news story example above. Look into the information at this reference site for a free third party component that can be installed on the server. Some ASP installations, such as your school, do not allow file uploads. Other server installations may not support your requests for installing third party components, whether free or not. Another approach is using what has been called a pure-ASP code solution for the upload. Here is an untested resource for ASP upload code.

ASP lists in file

Before we introduce you to ASP databases with a shopping cart example, let's continue to study text files. Text files are easy to use, easy to understand, and cheap. As long as we have fewer than about 200 records, text files will be sufficient to meet our needs for storage and retrieval.

A file named fruit.txt holds a list of fruit. Visitors to the Web page are expected to choose from a pull down list on an HTML page. To make the example clearer, only the list appears on the page.

Apples
Apricots
Bananas
Berries
Cherries
Guavas
Plums
Watermelons

This example uses ASP to read fruit.txt and to insert the records into the pull down list. It then sends the page to the browser. Source code for this example follows.

Parallel code for PHP can be found in the PHP lesson.

Scripting ASP lists

Here is the ASP source code for the pull down list of fruit. HTML markup has been color-coded green to distinguish it from the VBScript.

<%@ language="vbscript" %>
<% option explicit %>
<html><body>
<form name="frmExample" method="get" action="showChoice.asp">
<select name="fruit">
    <% response.buffer = True
    Dim fs, f, txtFile, aline
    Const ForReading=1
    Set fs = Server.CreateObject("Scripting.FileSystemObject")
    f = Server.MapPath ("../db/fruit.txt")
    Set txtFile = fs.OpenTextFile( f, ForReading )

    Do While Not txtFile.AtEndOfStream
        Response.write "<option>" & txtFile.ReadLine & "</option>"
    Loop
    txtFile.Close
    %>
</select></form></body></html>

Parallel code for PHP can be found in the PHP lesson.

Writing ASP lists

Here is the HTML and ASP code to populate or write into fruit.txt text file. It pulls the information from the html form named fruit.html and addfruit.asp then writes it into the fruit.txt text file. We have colored the HTML green to make it clearer.

fruit.html

<html><head>

<title>Fruit</title>

</head><h1>Add another fruit</h1>

<form name = "frmfruit" action = "addfruit.asp"
method = "post">

<table border="0" cellspacing="0" cellpadding="5">

<tr><td>Fruit :</td><td><input type="text" name="Newfruit"></td></tr>

<tr><td colspan="2" align="center"><input type="submit" value="submit" />
</td></tr>
</table></form></body></html>

addfruit.asp

<%@ language="vbscript" %>
<% option explicit %>
<html><body>
    <% response.buffer = True
    Dim fs, f, txtFile
    Const ForAppending=8, CreateOK=true
    Set fs = Server.CreateObject("Scripting.FileSystemObject")
    f = Server.MapPath ("../db/fruit.txt")
    Set txtFile = fs.OpenTextFile( f, ForAppending, CreateOK )
    txtFile.WriteLine Request("NewFruit")
    txtFile.Close
    response.write("Successfull action")
    %>
</body></html>

Text databases

Now we will see how a tilde-delimited text file (if it were comma-delimited it is called CSV by Microsoft) can be used to hold employee records and show them in a table on a Web page.

Tilde-delimited file

You can use any character to act as the delimiter or seperator, but the delimiter character should be something that certainly does not occur in the input. We chose the "~".

The fields of data are ID, FirstName, LastName, HireDate, ReviewDate, Salary, Sex and IsSelected. You will see how to read the records as text and split them into elements of an array. The array is coded rec(), so rec(3) is the element that holds HireDate.

1~James~Carlile~2/2/2001~10/13/2001~23200.00~M~1
2~Andrew~Frank~2/9/1997~2/9/1999~46276.92~M~1
3~Janet~Lydell~6/25/1994~6/25/1999~68674.72~F~0
4~Margo~ONiell~11/16/1994~11/16/1999~56834.25~F~0
5~Edward~Jones~11/17/1998~11/17/1999~62088.20~M~1
6~Harry~Jones~9/22/1978~10/1/1999~43920.23~M~1
7~Jimmy~Smith~2/24/2000~2/24/2001~55703.70~M~0
8~Hugh~Poynor~9/12/1989~9/30/1999~28923.08~M~1
9~Edward~Smith~3/6/2000~2/24/2001~25000.00~M~1

Parallel code for PHP can be found in the PHP lesson.

Fill a table from file

<%@ language="vbscript" %>
<% option explicit %>
<html><body>
<table border='1' cellspacing='0' cellpadding='5'
       width="440" style="border-collapse:collapse;
       font-family:sans-serif;">
<tr><th colspan="2">Employee</th><th>Sex</th>
<th>Hired</th><th>Salary</th></tr>
<% response.buffer = True
Dim fs, f, txtFile, aline, row, rec, rowStart
Const ForReading=1
Const ID=0, FirstName=1, LastName=2, HireDate=3
Const ReviewDate=4, Salary=5, Sex=6, IsSelected=6

Set fs = Server.CreateObject("Scripting.FileSystemObject")
f = Server.MapPath ("../db/Employee.txt")
Set txtFile = fs.OpenTextFile( f, ForReading )
row = 0
aline = txtFile.ReadLine
Do While Not txtFile.AtEndOfStream
    rec = split(aline,"~")
    if row mod 2 = 0 then
        rowStart = "<tr style='background-color:oldlace'><td>"
    else
        rowStart = "<tr style='background-color:snow'><td>"
    end if
    Response.write rowStart & rec(FirstName) & "</td><td>" & _
                              rec(LastName)  & "</td><td>" & _
                              rec(Sex)       & "</td><td>" & _
                              rec(HireDate)  & "</td><td>" & _
               FormatCurrency(rec(Salary))   & "</td></tr>"
    row = row + 1
    aline = txtFile.ReadLine
Loop
txtFile.Close
Response.write "</table></body></html>"
%>

Parallel code for PHP can be found in the PHP lesson.

VBScript Cookies

You learned about writing client-side cookies in JavaScript. Now you will learn to use ASP to create cookies on the server-side. In both cases the cookie will be written to the client workstation. The basics are knowing how to set and get the cookie. There are more details about ASP cookies on w3schools ASP cookie page.

Set Cookies

To set a cookie value:

Response.Cookies("name")="Phillip"
'sets a value for the cookie

Response.Cookies("name")("firstKey")="Phillip"
Response.Cookies("name")("secondKey")="Frances"
'sets mulitple values for the same cookie using keys

To set a cookie value from a form:

Response.Cookies("login")("Name")=Request.Form("username")
'sets the username value from a form to the login cookie

Response.Cookies("Login")("Name")=Request.Form("username")
Response.Cookies("Login")("Email")=Request.Form("email")
'sets the username and email values from a form to the Login cookie

You also need to set up the expiration date. Otherwise, the cookie will be removed once the browser is closed. The script below sets the cookie to expire after 30 days.

Response.Cookies("name").Expires = Date + 30

Get Cookies

To retrieve a value from a cookie:

Dim strName
strName = Request.Cookies("name")
Response.Write("Welcome " & strName)
'retrieves the value from the name cookie to be set
'to a local variable strName
'allows the value to be used later in the page,
'such as to write a welcome message

To check whether or not a cookie holds multiple values:

Request.Cookies("name").HasKeys
'if the cookie has keys, or multiple values,
'this returns true, else, it returns false

Example:

In login.asp:

If Request.Cookies("Login").HasKeys Then
   Response.Redirect "Welcome.asp?cookie=1"
End If

'checks to see if the cookie Login has values,
'indicating a returning visitor
'If true, then redirect the user to the welcome page
'Note the attached query string is set to 1

In welcome.asp:

Dim strUserName
If Request.QueryString("cookie") = 1 Then
   strUserName = Request.Cookies("Login")("Name")
   Response.Write("Welcome " & strUserName)
End If

'check if the query string cookie value is set to 1
'if yes, then this page was called
' due to a cookie login  and it displays a
' personalized welcome message
' based on the cookie's value

Delete Cookies

Here is a safe technique for deleting a cookie.

Response.Cookies("name").Expires = Date - 1000
'sets the cookie's Expires property
' to any date prior to today

Session variables

Instead of using cookies, ASP offers another way to store user information by employing session variables. Session is a pre-defined object that is created when an ASP page is loaded. As with cookies, the basics of session variables including knowing how to set and get values. More details on the ASP session object can be found on the w3schools session page.

Store and retrieve values

'To store values in a session variable:
Session("name")="Phillip"

'To retrieve the value:
Dim strName
strName = Session("name")
Response.Write("Welcome " & strName)

Session timeout and removal

By default, the session will expire in 20 minutes. However, you are able to modify the expiration time (in minutes).

Session.Timeout=30   'Session will expire in 30 minutes

'To remove a specific variable:
Session.Contents.Remove("name")

'To remove all session variables:
Session.Contents.RemoveAll()

Checkpoint (answer then click)

1. How do you write and retrieve a cookie value using ASP?

2. How do you write and retrieve a session variable value?

1. Writing and retrieving cookie value:

Response.Cookies("myInfo")="myDetails"   'write cookie value
Response.Cookies("myInfo").Expires = Date() + 30

strInfo = Request.Cookies("myInfo")   'retrieve cookie value

2. Writing and retrieving session variable value:

Session("myInfo") = "myDetails"   'write session variable value

strInfo = Session("myInfo")   'retrieve session variable value

Shopping cart example

This lesson uses Microsoft Access 2000 because it is widely supported and easy to learn. You could also use many other databases besides Access. If you have a small inventory of products in a catalog, a tilde-delimited text file would work for a shopping cart as well. For this Access example, product data will be stored in tblProduct inside wsdx-product.mdb.

Study the structure of the database table below.

Steps for ASP applications

Create HTML skeleton
Establish database connection
Construct and manipulate recordset

1. Create html skeleton

Construct a form and use the keyword action to refer to the file containing your ASP script. There are two examples: one shows a search by product name and the other by category.


<form name="frmSearchName" action=
"http://classweb2.bus.utexas.edu.../wsdx-43-150.asp" method="post"> <table>
   <tr>
      <td>Search by product name:</td>
      <td><input type="text" name="txtProduct" /></td>
      <td><input type="submit" value="Search" /></td>
   </tr>
</table>
</form>

Search by product category


<form name="frmSearchCategory" action=
"http://classweb2.bus.utexas.edu.../wsdx-43-150.asp" method="post">
<table>
   <tr>
      <td>Choose a category:</td>
      <td>
         <select name="cboCategory">
            <option value="fish">Fish</option>
            <option value="supplies">Supplies</option>
         </select>
      </td>
      <td><input type="submit" value="Search" /></td>
   </tr>
</table>
</form>

2. Establish database connection

Next we create the ASP file (this was named wsdx-43-150.asp in the previous example). You have to establish a database connection before you can do recordset programming. Study the script below.


<!-- #INCLUDE FILE="adovbs.inc" -->
<%
Dim OBJdbConnection, strProvider
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    server.mappath("/MIS373-Poynor/Hugh.Poynor/db/")&"\wsdx-product.mdb;"

OBJdbConnection.Open strProvider
%>

Include files

Adovbs.inc contains the declarations of global variables that are necessary to run ASP using recordsets. Often, students will spend hours examining their code for errors, only to discover that they simply forgot to include the adovbs.inc file. If you cannot get a recordset to work, always remember to check to see that you have included this file. You can create your own include files and save them with the.inc extension, as you will see soon.

Download the adovbs.inc file and place it in your asp folder. To include the file, write this line outside the asp tags.

<!-- #INCLUDE FILE="adovbs.inc" -->

ASP connections

Remember, ASP code must be enclosed within <% and %> tags. Inside the ASP tags, you can declare variables using the Dim keyword and can set the database object.


<%
' declare variables
Dim OBJdbConnection, strProvider
' set database object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")

Setting database path

Change the path and name of the database to make your ASP work (see previous example).


strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    server.mappath("your database path")&"\dbName.mdb;"

Open database connection

The next step is to open the connection to the database path you have specified above. Don't forget to close the ASP tag.


OBJdbConnection.Open strProvider   'open database connection
%>

Checkpoint (answer then click)

1. What are the usual steps in creating an ASP application?
2. What are some common errors in ASP programming?

3. Construct recordset

The following recordset script does the searching. Because of its efficiency, SQL is used to open the recordset. If you are not familiar with SQL, the shopping cart example will be too complex. If you need a review of SQL, then see these SQL Examples.


Dim strProduct, Rs
'request the value from the form
strProduct=request("txtProduct")
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = OBJdbConnection

'use SQL as the source of the recordset and then open
Rs.Source = _
"SELECT * FROM tblProduct WHERE productName LIKE '%" & strProduct & "%';"
Rs.Open

'create a loop to display all the products found
Do Until Rs.EOF
    Response.Write(Rs("productName") & " " & Rs("price"))
    Rs.MoveNext
Loop

'close the recordset and free up the memory 
Rs.Close
Set Rs= Nothing

The script above illustrates how to search by product name. To search by product category, you only have to change the SQL statement.


'search by category, use this SQL statement instead
Rs.Source = _
"SELECT * FROM tblProduct WHERE productCategory ='" & cboCategory & "';"

Open a recordset


Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = OBJdbConnection

'use SQL  as the source of the recordset 
Rs.Source = _
"SELECT * FROM tblProduct WHERE productName LIKE '%" & strProduct & "%';"
Rs.Open

The idea is to create a recordset object that is later used to retrieve specific data using SQL from the database. You have to create the object, define its SQL source, and open the recordset.

Do Loop Until Rs.EOF


'create a loop to display all the products in the recordset
Do Until Rs.EOF
    Response.Write(Rs("productName") & " " & Rs("price"))
    Rs.MoveNext
Loop

To get all the relevant data in a recordset, you have to create a loop. This is because a recordset can display only one particular record at a time. You have to do the loop until the recordset reaches the end of file or Rs.EOF.

In order for the loop to work correctly and avoid infinite loop, don't forget to move from the current record to the next record; use Rs.MoveNext.

Response.write

If there is a match, then you would need to display it in HTML using Response.Write. The value will depend on the index or the field name. For example, the product name can be retrieved using Rs("productName") or Rs(1).

Again, study the database structure to understand the field index. Remember that the index starts from 0 thus Rs(0) or Rs("productNumber") can be used to refer to the product number.

Closing recordsets


'close the recordset and free up memory 
Rs.Close
Set Rs= Nothing

Don't forget to close the recordset using Rs.Close. If you are not using the recordset object anymore, you may want to free up the memory reserved for that particular object by setting the object to null.

ASP source script

Study the source code below.

Checkpoint (answer then click)

What are some common errors in recordset programming using ASP?

ASP examples

Embedding quotes in VBScript strings

Your VBScripts create HTML markup then send the markup to the browser. HTML attributes require quote marks, as does the VBScript response.write() function that sends the markup. Some confusion arises when you need to send a double quote as HTML inside a VBScript literal that is double quoted. Here is a before and after example script. The first row of the table is written without the benefit of constants for making the scripts easier to write and read. The second row is written the easy way.

<%
    Public Const dQ = """"                  ' one double quote'
    Public Const sQ = "'"                   ' one single quote '
    Public Const BR = "<br />"
    Public Const bTD = "<td>"
    Public Const eTD = "</td>"
    Public Const TDTD = "</td><td>"
    Public Const bTR = "<tr>"
    Public Const eTR = "</tr>"

    Function fsQ( str )
        fsQ = sQ & str & sQ     ' function to allow for neater coding '
    End function

    Dim str1, str2, str3
    Dim t, template
    str1 = "Harold"
    str2 = "Harry"
    str3 = "Lloyd"
    str4 = "Male"
    dte1 = #12/31/2002#
    ccy1 = 2500

    response.write ( "<table border='1' cellspacing='0' " & _
                            " cellpadding='4' width='400' align='left'>")

    ' this row is written in the hard-to-read syntax
    response.write ( "<tr><td>" & str1 & "</td>")
    response.write ( "<td>" & "'" & str2 & "'" & "</td>")
    response.write ( "<td>" &  str3 & "</td>")
    response.write ( "<td>" & str4 & "</td>")
    response.write ( "<td>" &  dte1 & "</td>")
    response.write ( "<td>" & FormatCurrency(ccy1)  & "</td></tr>")

     ' this row is written in the easier-to-read syntax
    response.write ( bTR & bTD )
    response.write ( str1 & TDTD )
    response.write ( fsQ(str2) & TDTD )
    response.write ( str3 &  TDTD )
    response.write ( str4 & TDTD )
    response.write ( dte1 &  TDTD )
    response.write ( FormatCurrency(ccy1) & eTD & eTR)
    response.write ( "</table>")
%>

XML server scripting with ASP

This topic is covered on the XSLT page.

Working shopping cart

Online shops allow customers to save their items in a shopping cart. We have written a simple shopping cart example for a company called AquaCulture that sells exotic fish and aquarium supplies on the Web.

The script will be explained in the next few paragraphs, but you may want to try it out before you read further about the technical details. As you use the shopping cart, you will notice that it has very few features. Keeping it simple makes the scripting more understandable.

After you have tried out online shopping you can download the shopping cart files to look at the source code and read about the scripts next.

Adding new records

Here is the script for adding new items to the cart.


Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = OBJdbConnection
'open the shopping cart table
Rs.Open "tblShoppingCart", OBJDBConnection,
                    adOpenKeyset, adLockPessimistic, adCmdTable

Rs.Addnew
   'strCustomer will contain the customerID after a user logs in
   Rs("customerID") = strCustomer
   Rs("productName") = request("addcart")
   Rs("price") = request("price")
   Rs("quantity") = 1
Rs.Update
Rs.Close

Adding new records starts with Rs.AddNew. Then you enter the record value. You can refer to the record by field name or by the index number using this syntax: Rs("fieldName")="value" or Rs(indexNumber)="value"

The last step is to save the addition using Rs.Update and close the recordset.

Note: Primary key fields cannot have duplicate values. You should provide the logic for making duplicates in the primary key field impossible. (Hint: Delete the shopping cart records for the customer as soon as the session ends.)

Updating records

Here is the script for updating the cart.


Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = OBJdbConnection
'use SQL, UPDATE table SET field="new Value" WHERE condition
'add one to the quantity
strUpdate = "UPDATE tblShoppingCart SET quantity=(quantity+1) "
strWhere = "WHERE customerID=" & strCustomer & _
                    " and productName='" & strProduct & "';"
strSQL = strUpdate & strWhere
OBJdbConnection.Execute = strSQL   'execute the SQL

'redirect to the page displaying the shopping Cart
Response.Redirect("wsdx-cart.asp")

Deleting records

Below is the script for deleting records in the cart.


Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = OBJdbConnection
'the only difference is in the SQL statements,
'DELETE fieldName FROM table WHERE condition
strDelete = "DELETE FROM tblShoppingCart "
strWhere = "WHERE customerID=" & strCustomer & _
                   " and productName='" & strProduct & "';"
strSQL = strDelete & strWhere
OBJdbConnection.Execute = strSQL   'execute the SQL

'redirect to the page displaying the shopping Cart
Response.Redirect("wsdx-cart.asp")

Checkpoint (answer then click)

Use the following information to complete this checkpoint: Table name: tblProduct, field name: productNumber, quantity.

Use ASP recordset programming to:

1. Update the quantity to be 1000 for product having prodNum equal to 157CD.

2. Delete product having product number equal to 344DF.

Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = OBJdbConnection

'update record
strUpdate = "UPDATE tblProduct SET quantity=1000 WHERE productNumber='157C'"
OBJdbConnection.Execute = strUpdate

'delete record
strDelete = "DELETE FROM tblProduct WHERE productNumber='344DF'"
OBJdbConnection.Execute = strDelete

SQL Examples

Exercise

Create HTML and ASP for the following objectives:

Display current customers
Register new customers
Update customer information
Delete customers

Recordset Reference

This section presents references to ADO recordset programming, including recordset syntax and a basic SQL reference.

Opening Recordsets Using the Execute Method

There are two ways to open a recordset using ASP. The first option is to use the Execute method. The second option is to use the Open method. Each method has its own advantages and disadvantages which are discussed below.

This way of opening a recordset uses the Execute method of the connection object. The main disadvantage of using this method is that the resulting recordset is read-only and forward-only. This means that you cannot update recordset data and can only move forward in the recordset. These limitations allow this recordset to be very fast. This allows this method to be very useful if you need to write data to a web page or search for a specific record.

The syntax for the Execute method looks like this:

Set recordset = connection.Execute (CommandText, RecordsAffected, Options)

Parameters of the Execute Method

Parameter	Description
CommandText	This string variable is the source of the recordset. The user can input a SQL statement or table name as the record source.
RecordsAffected	This long variable is an optional argument that returns the number of records affected by an action command such as an update query.
Options	This long value is an optional argument that indicates how the server should evaluate the CommandText argument. Some of the common options and their descriptions can be found lower on the page.

Examples using the Execute Method


Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = OBJdbConnection

'Both of these statements open the shopping cart table 
set Rs = OBJdbConnection.Execute ("SELECT * from tblShoppingCart")
set Rs = OBJdbConnection.Execute ("tblShoppingCart")

Opening with the Open Method

Opening a recordset using the Open method of the recordset object allows the you to customize the functionality of the resulting recordset. This allows the resulting recordset to have much greater flexibility and useability than the Execute method recordset.

The syntax for the Open method looks like this:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Parameters of the Open Method

Parameter	Description
Source	This is the source of the recordset. The user can input a SQL statement or table name as the record source as long as the input corresponds with the proper option value.
ActiveConnection	This parameter defines which connection object to use.
CursorType	This parameter determines the type of cursor that the server should use when opening the recordset. For more information on CursorTypes, please see the CursorType table below.
LockType	This parameter determines what type of locking the server should use when opening the recordset. For more information on LockTypes, please see the LockType table below.
Options	This long value is an optional argument that indicates how the server should evaluate the CommandText argument. Some of the common options and their descriptions can be found lower on the page.

CursorTypes

CursorType	Numerical Value	Description
adOpenDynamic	2	This CursorType uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the recordset are allowed, except for bookmarks, if the server doesn't support them.
adOpenForwardOnly	0	This is the default CursorType. This CursorType uses a forward-only cursor. With this LockType you can only scroll forward through records. This improves performance when you need to make only one pass through a recordset.
adOpenKeyset	1	This CursorType uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your recordset. Data changes by other users are still visible.
adOpenStatic	3	This CursorType uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.
adOpenUnspecified	-1	This CursorType does not specify the type of cursor.

LockTypes

LockType	Numerical Value	Description
adLockBatchOptimistic	4	This LockType indicates optimistic batch updates. This is required for batch update mode.
adLockOptimistic	3	This LockType indicates optimistic locking, record by record. The server uses optimistic locking, locking records only when you call the Update method.
adLockPessimistic	2	This LockType indicates pessimistic locking, record by record. The server does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.
adLockReadOnly	1	This is the Default value. This LockType indicates read-only records. You cannot alter the data.
adLockUnspecified	-1	This LockType does not specify a type of lock.

Commonly Used Options

Option	Numerical Value	Description
adCmdUnspecified	-1	Does not specify the command type argument.
adCmdText	1	Evaluates CommandText as a textual definition of a command or stored procedure call. You would use this option if you entered the record source as a SQL statement.
adCmdTable	2	Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query. You would use this option if you entered a table name as the record source.
adCmdUnknown	8	This is the Default value. Indicates that the type of command in the CommandText property is not known.

Open Method


Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = OBJdbConnection

'This statement opens the same recordset as the Execute Method above.  

Rs.Open "tblShoppingCart", OBJDBConnection,
                    adOpenForwardOnly, adLockReadOnly, adCmdTable

'This statement opens the same recordset as the Execute Method above

Rs.Open "tblShoppingCart", OBJDBConnection, , , adCmdTable


'This statement creates a dynamic recordset using a SQL statement.   

Rs.Open "SELECT * FROM tblShoppingCart", OBJDBConnection,
                    adOpenDynamic, adLockPessimistic, adCmdText

Connection string

<body>

' MUST include adovbs.inc for this code to function
<!-- #INCLUDE FILE="adovbs.inc" -->

<%
' ASP code
Dim ObjConn    '  permissions set ONLY for db folder
Dim rs
Dim strConn
Set ObjConn = Server.CreateObject("ADODB.Connection")
strConn = "Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    server.mappath("/MIS373-YourInstructor/FirstName.LastName/db/")&"\sample.mdb;"
ObjConn.Open strConn
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = ObjConn
rs.Open "SELECT * FROM tblSample", ObjConn,
                adOpenKeyset, adLockPessimistic, adCmdText
' The parameter names in the line above are explained in the tables above.
%>
</body>

adovbs.inc

Download adovbs.inc which holds the parameters you need for the open method syntax shown above. Include this file in the html section of your asp programs. See Connection string above for an example of how it may be included.