Share via

Module One: Creating ASP Pages

  • Article

In Module One, you create ASP pages (.asp files) using HTML and Microsoft Visual Basic Scripting Edition (VBScript). This module includes the following lessons:

  • Lesson 1: Writing an ASP Page: Four examples of how to write and run ASP pages using HTML and VBScript.
  • Lesson 2: Submitting Information Using Forms: Develop a form on an HTML page so that users can enter their personal data.
  • Lesson 3: Creating a Guest Book: Use forms to gather information from visitors, store their information in a Microsoft Access database, and display the database contents on a Web page.
  • Lesson 4: Displaying an Excel Spreadsheet: Display a Microsoft Excel spreadsheet on a Web page.

Lesson 1: Writing an ASP Page

The best way to learn about ASP is to look at examples; alter integer values, strings, and statements you are curious about; and determine what changes occur in the browser.

In this lesson you perform the following tasks:

  • Example 1: Create, save, and run an ASP page using HTML and VBScript.
  • Examples 2, 3, and 4: Add functionality and logic to your ASP page by using built-in functions and conditional script statements.

VBScript is the default scripting language for ASP pages; however, the delimiters are the same for JScript. Use angle brackets as delimiters around HTML tags just as you would in any .htm page, as follows:

<example></example>

Use percent signs with brackets as delimiters around script code, as follows:

<% example %>

You can put many script statements inside one pair of script delimiters, as in the following example:

  <font face="MS Gothic">
<%
'Create a variable.
dim strGreeting

'Set the greeting.
strGreeting = "Hello  World!"

'Print out the greeting, using the ASP Response object.
Response.Write strGreeting & "<BR>"

'Also print out the greeting using the <%= method.
%>
<%=strGreeting%>
</font>

This code displays the following text in the browser:

 Hello World!
 Hello World!

Here is the previous example using JScript:

  <%@ Language=JScript %>

<font face="MS Gothic">
<%
//Create a variable.
var strGreeting;

//Set the greeting.
strGreeting = "Hello World!";

//Print out the greeting, using the ASP Response object.
Response.Write(strGreeting + "<BR>");

//Also print out the greeting using the <%= method.
%>
<%=strGreeting%>
</font>

To create ASP pages, use a text editor such as Notepad and save the page with the .asp extension instead of .htm. The .asp filename extension tells IIS to send the page through the ASP engine before sending the page to a client. (Note: In the Notepad Save As dialog box, when Text Documents (*.txt) is selected in the Save as type box, Notepad automatically appends .txt to the filename. To prevent this, select All Files in the Save as type box, type the full filename MyFile.asp in the File Name field, and then click Save.)

Example 1

This example displays a greeting, the date, and the current time. To run this example, copy and paste the following code into an empty file and save it in the x:\Inetpub\Wwwroot\Tutorial directory as Example1.asp. View the example with your browser by typing https://localhost/Tutorial/Example1.asp in the address bar.

   <%@ Language=VBScript %>

  <html>
  <head>
  <title>Example 1</title>
  </head>
  <body>
  <font face="MS Gothic">

  <H1>Welcome to my Home Page</H1>
  <%
   'Create some variables.
   dim strDynamicDate
   dim strDynamicTime

   'Get the date and time.
   strDynamicDate = Date()
   strDynamicTime = Time()

   'Print out a greeting, depending on the time, by comparing the last 2 characters in strDymamicTime to "PM".
   If "PM" = Right(strDynamicTime, 2) Then
      Response.Write "<H3>Good Afternoon!</H3>"
   Else
      Response.Write "<H3>Good Morning!</H3>"
   End If
  %>
  Today's date is <%=strDynamicDate%> and the time is <%=strDynamicTime%> 

  </font>
  </body>
  </html>

In the browser, you should see something like the following (depending on the date and time you perform this exercise):

Welcome to my Home Page

Good Afternoon!

Today's date is 10/20/2000 and the time is 7:29:50 PM

The Web server processes Example1.asp in the following sequence:

  1. <%@ Language=VBScript %> tells the ASP engine to use the VBScript engine to translate the script code.
  2. The ASP engine ignores the HTML code blocks.
  3. The ASP engine executes the code in the <%...%> blocks and replaces the blocks with placeholders. The results of the Response.Write strings and <%=...%> strings are saved in memory on the server.
  4. The results of the Response.Write strings and <%=...%> strings are injected into the HTML code at the matching placeholders before the page leaves the ASP engine.
  5. The complete page leaves the ASP engine as a file of HTML code, and the server sends the page to the client.

Example 2

This example incorporates a For...Next loop in the ASP page to add a little dynamic logic. The For...Next loop is one of six conditional statements available to you. The others are Do...Loop, For Each...Next, If...Then...Else...End If, Select..Case...End Select, and While...Wend. These statements are documented at Windows Script Technologies under VBScript.

Copy and paste the following code in your text editor, and save the file as Example2.asp. View the example with your browser by typing https://localhost/Tutorial/Example2.asp in the address bar.

The processing order is the same as in Example1.asp.

  <%@ Language=VBScript %> 
<html>
<head>
<title>Example 2</title>
</head>
<body>
<font face="MS Gothic">

<%
 'Create a variable.
 dim strTemp
 dim font1, font2, font3, font, size

 'Set the variable.
 strTemp= "BUY MY PRODUCT!"
 fontsize = 0

 'Print out the string 5 times using the For...Next loop.
 For i = 1 to 5

   'Close the script delimiters to allow the use of HTML code and <%=...
   %>
   <table align=center><font size= <%=fontsize%>> <%=strTemp%> </font></table>
   <%
   fontsize = fontsize + i

 Next

%>
<table align=center><font size=6><B> IT ROCKS! <B></font></table><BR>

</font>
</body>
</html>

In the browser, you should see the "BUY MY PRODUCT!" string displayed five times in increading sizes.

Here is Example 2 using JScript:

   <%@ Language=JScript %>

  <html>
  <head>
  <title>Example 2</title>
  </head>
  <body>
  <font face="MS Gothic">

  <%
  //Createa variable.
  var strTemp;
  var font1, font2, font3, font, size;

  //Set the variable.
  strTemp= "BUY MY PRODUCT!";
  fontsize = 0;

  //Print out the string 5 times using the For...Next loop.
  for (i = 1; i < 6; i++) {

  //Close the script delimiters to allow the use of HTML code and <%=...
  %>.
  <table align=center><font size= <%=fontsize%>> <%=strTemp%> </font></table>
  <%
  fontsize = fontsize + i;

  }

  %>
  <table align=center><font size=6><b> IT ROCKS! <b></font></table><br>

  </font>
  </body>
  </html>

Example 3

There are more multilingual Web sites every day, as businesses see the need to offer their products around the world. Formatting your date, time, and currency to match the user's locale is good for diplomacy.

In Example 3, a predefined function displays the date and currency on your ASP page. The date and currency are formatted for different locales using the GetLocale function, the SetLocale function, the FormatCurrency function, and the FormatDateTime function. Locale identification strings are listed in the Locale ID Chart on MSDN. (This example doesn't cover changing the CodePage to display non-European characters on European operating systems. Please read CodePage topics in your IIS Documentation for more information.)

note Note There are more than 90 predefined functions in VBScript, and they are all well-defined at Windows Script Technologies. To view the documentation, select VBScript, select Documentation, select Language Reference, and select Functions.

Copy and paste the following code in your text editor, and save the file as Example3.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/Example3.asp in the address bar.

  <%@ Language=VBScript %>

<html>
  <head>
  <title>Example 3</title>
  </head>
  <body>
  <font face="MS Gothic">

  <H3>Thank you for your purchase.  Please print this page for your records.</H3>
  <%
   'Create some variable.
   dim saveLocale
   dim totalBill

   'Set the variables.
   saveLocale = GetLocale
   totalBill = CCur(85.50)

   'For each of the Locales, format the date and the currency
   SetLocale("fr")
   Response.Write"<B>Formatted for French:</B><BR>"
   Response.Write FormatDateTime(Date, 1) & "<BR>"
   Response.Write FormatCurrency(totalBill) & "<BR>"
   SetLocale("de")
   Response.Write"<B>Formatted for German:</B><BR>"
   Response.Write FormatDateTime(Date, 1) & "<BR>"
   Response.Write FormatCurrency(totalBill) & "<BR>"
   SetLocale("en-au")
   Response.Write"<B>Formatted for English - Australia:</B><BR>"
   Response.Write FormatDateTime(Date, 1)& "<BR>"
   Response.Write FormatCurrency(totalBill) & "<BR>"

   'Restore the original Locale
   SetLocale(saveLocale)
  %>

  </font>
  </body>
  </html>

In the browser, you should see the following:

Thank you for your purchase. Please print this page for your records.

Formatted for French:
vendredi 20 octobre 2000
85,50 F
Formatted for German:
Freitag, 20. Oktober 2000
85,50 DM
Formatted for English - Australia:
Friday, 20 October 2000
$85.50

Example 4

The most common functions used in ASP Scripts are those that manipulate strings. The most powerful string functions use regular expressions. Because regular expressions are difficult to adapt to, Example 4 shows you how to replace characters in a string by using a string expression and a regular expression. Regular expressions are defined at Windows Script Technologies. To view the documentation, select VBScript, select Documentation, and select Regular Expressions Guide.

Copy and paste the following code in your text editor, and save the file as Example4.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/Example4.asp in the address bar.

   <%@ Language=VBScript %>

  <html>
  <head>
  <title>Example 4</title>
  </head>
  <body>
  <font face="MS Gothic">

  <H3>Changing a Customer's Street Address</H3>
  <%
   'Create some variables.
   dim strString
   dim strSearchFor     ' as a string
   dim reSearchFor     ' as a regular expression
   dim strReplaceWith

   'Set the variables.
   strString = "Jane Doe<BR>100 Orange Road<BR>Orangeville, WA<BR>98100<BR>800.555.1212<BR>"
   '   Using a string object
   strSearchFor = "100 Orange Road<BR>Orangeville, WA<BR>98100"
   '   Using a regular expression object
   Set reSearchFor = New RegExp
   reSearchFor.Pattern = "100 Orange Road<BR>Orangeville, WA<BR>98100"
   reSearchFor.IgnoreCase = False

   strReplaceWith = "200 Bluebell Court<BR>Blueville, WA<BR>98200"

   'Verify that strSearchFor exists...
   '   using a string object.
   If Instr(strString, strSearchFor) Then
     Response.Write "strSearchFor was found in strString<BR>"
   Else
     Response.Write "Fail"
   End If
   '   using a regular expression object.
   If reSearchFor.Test(strString) Then
     Response.Write "reSearchFor.Pattern was found in strString<BR>"
   Else
     Response.Write "Fail"
   End If

   'Replace the string...
   Response.Write "<BR>Original String:<BR>" & strString & "<BR>"
   '   using a string object.
   Response.Write "String where strSearchFor is replaced:<BR>"
   Response.Write Replace(strString, strSearchFor, strReplaceWith) & "<BR>"
   '   using a regular expression object.
   Response.Write "String where reSearchFor is replaced:<BR>"
   Response.Write reSearchFor.Replace(strString, strReplaceWith) & "<BR>"
  %>

  </font>
  </body>
  </html>

In the browser, you should see the following:

Changing a Customer's Street Address

strSearchFor was found in strString
reSearchFor.Pattern was found in strString

Original String:
Jane Doe
100 Orange Road
Orangeville, WA
98100
800.555.1212

String where strSearchFor is replaced:
Jane Doe
200 Bluebell Court
Blueville, WA
98200
800.555.1212

String where reSearchFor is replaced:
Jane Doe
200 Bluebell Court
Blueville, WA
98200
800.555.1212

Lesson 2: Submitting Information Using Forms

A common use of intranet and Internet server applications is to accept user input by implementing a form in your Web page. ASP includes the following two collections in the Request object to help process form information: the QueryString collection and the Form collection.

caution Caution Accepting form input from clients gives malicious users a chance to send potentially unsafe characters to attack your Web application in any of the following ways:

  • Strings that are too long for you application to handle can cause your application to fail or write over existing data.
  • Strings that contain invalid characters can cause your application to fail or perform unexpected actions.
  • If your Web application is accessing a database, a carefully engineered string of characters can add or delete records from your database.

A general method of protection against these kinds of attacks is to Server.HTMLEncode all form input when your Web page accesses the input. Another method is to write a short function that tests form input for invalid characters. This tutorial uses the Server.HTMLEncode method. However, more information can be found by reading chapter 12 of Writing Secure Code, and using Checklist: ASP Security when you create your ASP applications.

In this lesson, you create an HTML page that accepts user input in an HTML form and sends the user input back to the Web server to the same page. The Web server then displays the user input. Later in this module, you use this knowledge about forms to build a guest book application that uses ASP scripting. To complete this lesson, you perform the following tasks:

  • Example 1: Display a selection of button elements in a form.
  • Example 2: Display text box elements in a form, accept the user input from the form, and display the user input on the Web page.

Example 1: Buttons

Forms can contain many different kinds of elements to help your users enter data. In this example, there are five input form elements called buttons. There are many types of buttons including RADIO buttons, SUBMIT buttons, RESET buttons, CHECKBOX buttons, and TEXT buttons.

After the user enters information in a form, the information needs to be sent to your Web application. When a user clicks the button labeled "Submit" in your Web page, the form data is sent from the client to the Web page that is listed in the ACTION element of the form tag. This Web page doesn't need to be the same as the calling page. In this example, the Web page listed in the ACTION element is the same as the calling page, which eliminates the need to call another page.

In this example, METHOD="POST" is used to send data from the Web client's browser to the Web server. When you use METHOD="POST" in a form, the user data ends up in the Form collection of the Request object.

Copy and paste the following code in your text editor, and save the file as Button.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/Button.asp in the address bar.

   <%@ Language=VBScript %>

  <html> 
  <head> 
  <title>Button Form</title> 
  </head> 
  <body> 
  <font face="MS Gothic"> 

  <FORM NAME="Button Example" METHOD="POST" ACTION="button.asp">
  <H3>Computer Programming Experience:</H3> 
  <p>
  <INPUT TYPE="RADIO" NAME= "choice" VALUE="Less than 1"> Less than 1 year.<BR> 
  <INPUT TYPE="RADIO" NAME= "choice" VALUE="1 to 5"> 1-5 years.<BR>
  <INPUT TYPE="RADIO" NAME= "choice" VALUE="More than 5"> More than 5 years.<BR>
  </p> 
  <p>
  <INPUT TYPE="SUBMIT" VALUE="Submit"> 
  <INPUT TYPE="RESET" VALUE="Clear Form">
  </p>
  </form> 

  <%
   'Check to see if input has already been entered. 
   dim strChoice
   strChoice = Server.HTMLEncode(Request.Form("choice"))

   If "" = strChoice Then
     Response.Write "<P>(No input yet.)</P>"
   Else
     Response.Write "<P>Your last choice was <B>" & strChoice & "</B></P>"
   End If
  %>

  </font> 
  </body> 
  </html>

In the browser, you should see the following:

Computer Programming Experience:

Radio button Less than 1 year.
Radio button 1-5 years.
Radio button More than 5 years.

(No input yet.)

Example 2: Input Form Elements

In this example, there are three input form elements called text fields and two input form elements called check boxes. Check boxes differ from option buttons because you can select more than one. We still need the default Submit button to send the data back to the server.

In this example, METHOD=GET is used to send data from the Web client's browser to the Web server. When you use METHOD=GET in a form, the user data ends up in the QueryString collection of the Request object.

Look at the address bar after you click Submit, and you should see the elements of the QueryString displayed at the end of the URL.

Copy and paste the following code in your text editor, and save the file as Text.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/Text.asp in the address bar.

   <%@ Language=VBScript %>  

  <html> 
  <head> 
  <title>Text and Checkbox Form</title>
  </head>
  <body>
  <font face="MS Gothic">

  <FORM NAME="TextCheckbox Example" METHOD="GET" ACTION="text.asp">
  <H3>Please fill out this form to get information on our products:</H3>
  <p>
  <table>
  <tr>
  <td><font face="MS Gothic">Name (required)</td>
  <td><INPUT TYPE="TEXT" NAME="name" VALUE="" SIZE="20" MAXLENGTH="150"></td>
  </tr><tr>
  <td><font face="MS Gothic">Company</td>
  <td><INPUT TYPE="TEXT" NAME="company" VALUE="" SIZE="25" MAXLENGTH="150"></td>
  </tr><tr>
  <td><font face="MS Gothic">E-mail (required)</td>
  <td><INPUT TYPE="TEXT" NAME="email" VALUE="" SIZE="25" MAXLENGTH="150"></td>
  </tr>
  </table>
  </p>
  <p>
  Requesting information on our:<BR>
  <INPUT TYPE="CHECKBOX" NAME= "info" VALUE="software">Software<BR>
  <INPUT TYPE="CHECKBOX" NAME= "info" VALUE="hardware">Hardware <BR>
  </p>
  <p>
  <INPUT TYPE="SUBMIT" VALUE="Submit">
  <INPUT TYPE="RESET" VALUE="Clear Form">
  </p>
  </form>

  <%
   'Check to see if input has already been entered.
   dim strName, strEmail, strCompany, strInfo
   strName = Server.HTMLEncode(Request.QueryString("name"))
   strEmail = Server.HTMLEncode(Request.QueryString("email"))
   strCompany = Server.HTMLEncode(Request.QueryString("company"))
   strInfo = Server.HTMLEncode(Request.QueryString("info"))

   'Display what was entered.
   If ("" = strName) OR ("" = strEmail) Then
     Response.Write "<P>(No required input entered yet.)</P>"
   Else
     Response.Write "<P>Your are " & strName
     If Not ("" = strCompany) Then
       Response.Write " from " & strCompany
     End If
     Response.Write ". <BR>Your email address is " & strEmail
     If Not ("" = strInfo) Then
       Response.Write " and you would like information on " & strInfo & ".</P>"
     End If
   End If
  %>

  </font>
  </body>
  </html>

In the browser, you should see the following:

Please fill out this form to get information about our products:

Name (required) Text box
Company Text box
E-mail (required) Text box

Requesting information about our:
Check boxSoftware
Check boxHardware

(No required input entered yet.)

Lesson 3: Creating a Guest Book using a Database

This lesson requires that you have Microsoft Access installed on your system and is not supported on 64-bit platforms until Access is developed for 64-bit platforms.

Lesson 3 develops a guest book application. Guest books allow your Web site visitors to leave behind information, such as their names, e-mail addresses, and comments. In this lesson, you perform the following tasks after creating an Access database:

  • Example 1: Create an ASP page to connect to the database using only the ADO Connection object.
  • Example 2: Create an ASP page to connect to the database using the Connection object and the Command object together.
  • Example 3: Create an ASP page to display the guest book information from the database in a browser.

Create an Access Database

Create an Access database called GuestBook.mdb, and save it in x:\Inetpub\Wwwroot\Tutorial. Create a table in the database called GuestBook. Use the Create Table Using Design View option in Access to add the following fields and properties:

Field Name Data Type Field General Properties
FID AutoNumber Field Size=Long Integer, New Values=Increment, Indexed=Yes(No Duplicates)
FTB1 Text Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No
FTB2 Text Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No
FTB3 Text Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No
FTB4 Text Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No
FMB1 Memo Required=No, Allow Zero Length=Yes

Create an ASP Page to Add Data to Your Access Database

Now that you have created a database, you can build an ASP page to connect to your database and read incoming data using Microsoft ActiveX Data Objects (ADO). ADO is a collection of objects with methods and properties that can manipulate data in almost any type of database. (If you plan to use databases frequently, you should purchase a programmer's reference book for ADO. Only the most basic ADO code is illustrated in the following examples, enough to open, read in, and write to a database.)

The next two examples produce the same results; however, the first example uses only the Connection object, and the second example gives part of the job to the Command object, which is much more powerful. Compare both examples to see how the objects become connected together. After you are comfortable with the objects, use an ADO programmer's reference to experiment with more methods and properties.

To see an example of an ADO error in your ASP page, try browsing to the page after renaming your database, after entering a typing mistake in the connection string, or after making the database Read Only.

Example 1: Using Only the ADO Connection Object

Copy and paste the following code in your text editor, and save the file as GuestBook1.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/GuestBook1.asp in the address bar.

   <%@ Language=VBScript %>

  <html>
  <head>
  <title>Guest Book Using Connection Object Only</title>
  </head>
  <body>
  <font face="MS Gothic">
  <h2>Guest Book Using Connection Object Only</h2>

  <%
   If Not Server.HTMLEncode(Request.QueryString("Message")) = "True" Then
     'No information has been input yet, so provide the form.
  %>
     <p>
     <FORM NAME="GuestBook1" METHOD="GET" ACTION="guestbook1.asp">
     <table>
       <tr>
       <td><font face="MS Gothic">From:</td><td><INPUT TYPE="TEXT" NAME="From"></td>
       </tr><tr>
       <td><font face="MS Gothic">E-mail Address:</td><td><INPUT TYPE="TEXT" NAME="EmailAdd"></td>
       </tr><tr>
       <td><font face="MS Gothic">CC:</td><td><INPUT TYPE="TEXT" NAME="CC"></td>
       </tr><tr>
       <td><font face="MS Gothic">Subject:</td><td><INPUT TYPE="TEXT" NAME="Subject"></td>
       </tr>
     </table>
     Message:<br><TEXTAREA NAME="Memo" ROWS=6 COLS=70></TEXTAREA>
     </p>

     <p>
     <INPUT TYPE="HIDDEN" NAME="Message" VALUE="True">
     <INPUT TYPE="SUBMIT" VALUE="Submit Information">
     </FORM>
     </p>
  <% 
   Else
     'The HIDDEN button above sets the Message variable to True.
     'We know now that form data has been entered.

     'Get the data from the form. We will be inserting it into the database.
     'Access doesn't like some characters, such as single-quotes, so encode the
     ' data using the HTMLEncode method of the ASP Server object.
     dim strTB1, strTB2, strTB3, strTB4, strMB1, strCommand
     strTB1 = Server.HTMLEncode(Request.QueryString("From"))
     strTB2 = Server.HTMLEncode(Request.QueryString("EMailAdd"))
     strTB3 = Server.HTMLEncode(Request.QueryString("CC"))
     strTB4 = Server.HTMLEncode(Request.QueryString("Subject"))
     strMB1 = Server.HTMLEncode(Request.QueryString("Memo"))

     'This is a connection string.  ADO uses it to connect to a database through the Access driver.
     'It needs the provider name of the Access driver and the name of the Access database.
     'Connection strings are slightly different, depending on the provider being used, 
     ' but they all use semicolons to separate variables.
     'If this line causes and error, search in your registry for
     ' Microsoft.JET to see if 4.0 is your version.
     strProvider = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\InetPub\Wwwroot\Tutorial\guestbook.mdb;"

     'This creates an instance of an ADO Connection object.
     'There are 4 other ADO objects available to you, each with different methods and
     'properties that allow you to do almost anything with database data.
     Set objConn = server.createobject("ADODB.Connection")

     'The Open method of the Connection object uses the connection string to
     ' create a connection to the database.
     objConn.Open strProvider

     'Define the query.
     'There are many types of queries, allowing you to add, remove, or get data.
     'This query will add your data into the database, using the INSERT INTO key words.
     'Here, GuestBook is the name of the table.
     'You need single-quotes around strings here.
     strCommand = "INSERT INTO GuestBook (FTB1,FTB2,FTB3,FTB4,FMB1) VALUES ('"
     strCommand = strCommand & strTB1 & "','" & strTB2 & "','" & strTB3 & "','" & strTB4 & "','" & strMB1
     strCommand = strCommand & "')"

     'Execute the query to add the data to the database.
     objConn.Execute strCommand

     Response.Write("Thank you! Your data has been added.")

     End If
  %>

  </font>
  </body>
  </html>

In the browser, you should see the following:

Guest Book Using Connection Object Only

From: Text box
E-mail Address: Text box
CC: Text box
Subject: Text box

Message:

Example 2: Using the Connection Object and the Command Object Together

Copy and paste the following code in your text editor, and save the file as GuestBook2.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/GuestBook2.asp in the address bar.

   <%@ Language=VBScript %>

  <html>
  <head>
  <title>Guest Book Using Connection Object and Command Object</title>
  </head>
  <body>
  <font face="MS Gothic">
  <h2>Guest Book Using Connection Object and Command Object</h2>

  <%
   If Not Server.HTMLEncode(Request.QueryString("Message")) = "True" Then
     'No information has been input yet, so provide the form.
  %>
     <p>
     <FORM NAME="GuestBook2" METHOD="GET" ACTION="guestbook2.asp">
     <table>
       <tr>
       <td><font face="MS Gothic">From:</td><td><INPUT TYPE="TEXT" NAME="From"></td>
       </tr><tr>
       <td><font face="MS Gothic">E-mail Address:</td><td><INPUT TYPE="TEXT" NAME="EmailAdd"></td>
       </tr><tr>
       <td><font face="MS Gothic">CC:</td><td><INPUT TYPE="TEXT" NAME="CC"></td>
       </tr><tr>
       <td><font face="MS Gothic">Subject:</td><td><INPUT TYPE="TEXT" NAME="Subject"></td>
       </tr>
     </table>
     Message:<br><TEXTAREA NAME="Memo" ROWS=6 COLS=70></TEXTAREA>
     </p>

     <p>
     <INPUT TYPE="HIDDEN" NAME="Message" VALUE="True">
     <INPUT TYPE="SUBMIT" VALUE="Submit Information">
     </FORM>
     </p>
  <% 
   Else
     'The HIDDEN button above sets the Message variable to True.
     'We know now that form data has been entered.

     'Get the data from the form. We will be inserting it into the database.
     'Access doesn't like some characters, such as single-quotes, so encode the
     ' data using the HTMLEncode method of the ASP Server object.
     dim strTB1, strTB2, strTB3, strTB4, strMB1
     strTB1 = Server.HTMLEncode(Request.QueryString("From"))
     strTB2 = Server.HTMLEncode(Request.QueryString("EMailAdd"))
     strTB3 = Server.HTMLEncode(Request.QueryString("CC"))
     strTB4 = Server.HTMLEncode(Request.QueryString("Subject"))
     strMB1 = Server.HTMLEncode(Request.QueryString("Memo"))

     'The Memo data type in the Access database allows you to set the field size.
     If strMB1 = "" Then
       iLenMB1 = 255
     Else
       iLenMB1 = Len(strMB1)
     End If

     'This is a connection string.  ADO uses it to connect to a database through the Access driver.
     'It needs the provider name of the Access driver and the name of the Access database.
     'Connection strings are slightly different, depending on the provider being used, 
     ' but they all use semicolons to separate variables.
     'If this line causes and error, search in your registry for
     ' Microsoft.JET to see if 4.0 is your version.
     strProvider = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\InetPub\Wwwroot\Tutorial\guestbook.mdb;"

     'This creates an instance of an ADO Connection object.
     'There are 4 other ADO objects available to you, each with different methods and
     'properties that allow you to do almost anything with database data.
     Set objConn = server.createobject("ADODB.Connection")

     'The Open method of the Connection object uses the connection string to
     ' create a connection to the database.
     objConn.Open strProvider

     'This creates an instance of an ADO Command object.
     'Although you could do most of your work with the Connection object,
     ' the Command object gives you more control.
     Set cm = Server.CreateObject("ADODB.Command")

     'The ActiveConnection property allows you to attach to an Open connection.
     'This is how you link the Connection object above to the Command object.
     cm.ActiveConnection = objConn

     'Define the query.
     'There are many types of queries, allowing you to add, remove, or get data.
     'This query will add your data into the database, using the INSERT INTO keywords.
     'Because we are using the Command object, we need to put our query into the
     ' CommandText property.
     'Here, GuestBook is the name of the table.
     cm.CommandText = "INSERT INTO GuestBook (FTB1,FTB2,FTB3,FTB4,FMB1) VALUES (?,?,?,?,?)"

     'This is where you see the power of the Command object.
     'By putting ? marks in the string above, we can use the Parameters collection
     ' to have ADO fill in the ? with the detailed parameters below.
     'cm.CreateParameter formats the parameter for you.
     'cm.Parameters.Append appends the parameter to the collection.
     'Make sure they are in the same order as (TB1,TB2,TB3,TB4,MB1).

     Set objparam = cm.CreateParameter(, 200, , 255, strTB1)
     cm.Parameters.Append objparam

     Set objparam = cm.CreateParameter(, 200, , 255, strTB2)
     cm.Parameters.Append objparam

     Set objparam = cm.CreateParameter(, 200, , 255, strTB3)
     cm.Parameters.Append objparam

     Set objparam = cm.CreateParameter(, 200, , 255, strTB4)
     cm.Parameters.Append objparam

     Set objparam = cm.CreateParameter(, 201, , iLenMB1, strMB1)
     cm.Parameters.Append objparam

     'Execute the query to add the data to the database.
     'Here, the Execute method of the Command object is called instead of
     ' the Execute method of the Connection object.
     cm.Execute

     Response.Write("Thank you! Your data has been added.")

     End If
  %>

  </font>
  </body>
  </html>

In the browser, you should see the same content as GuestBook1.asp, as follows:

Guest Book Using Connection Object Only

From: Text box
E-mail Address: Text box
CC: Text box
Subject: Text box

Message:

Example 3: Display the Database in a Browser

After information is entered in a database, you can use a Web page containing another script to view and edit the data. Not much changes in the ADO code, except the way you define your query. In the last two examples, you used the INSERT INTO query to add records to a database. In this example, you use the SELECT query to choose records from a database and display them in the browser. You also use the DELETE query to remove records from the database. The only query not used in these examples is the UPDATE query, whose syntax is like that of the INSERT INTO query. The UPDATE query allows you to change fields in a database.

Copy and paste the following code in your text editor, and save the file as ViewGB.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/ViewGB.asp in the address bar.

   <%@ Language=VBScript %> 

  <html> 
  <head> 
  <title>View Guest Book</title> 
  </head> 
  <body> 
  <font face="MS Gothic"> 
  <h2>View Guest Book</h2> 

  <%
  'Read in any user input. Any of these can be empty.
  'By doing this first, we can preserve the user input in the form.
  dim strTB1, strTB2, strTB3, strTB4, strMB1, strSort, iDelete
  strTB1 = Server.HTMLEncode(Request.QueryString("From")) 
  strTB2 = Server.HTMLEncode(Request.QueryString("EMailAdd")) 
  strTB3 = Server.HTMLEncode(Request.QueryString("CC")) 
  strTB4 = Server.HTMLEncode(Request.QueryString("Subject")) 
  strMB1 = Server.HTMLEncode(Request.QueryString("Memo")) 
  strSort = Server.HTMLEncode(Request.QueryString("sort"))
  iDelete = CInt(Request.QueryString("Delete"))

  'Because we use this variable, and it might not be set...
  If "" = strSort Then
    strSort = "FID"
  End If
  %>

  <p> 
  <FORM NAME="ViewGuestBook" METHOD="GET" ACTION="viewgb.asp"> 
  <table>
    <tr>
    <td><font face="MS Gothic">Sort by which column:</td>
    <td><SELECT NAME="sort" SIZE="1">
      <OPTION VALUE="FID">ID Number</OPTION>
      <OPTION VALUE="FTB1">Name</OPTION>
      <OPTION VALUE="FTB2">Email</OPTION>
      <OPTION VALUE="FTB3">CC</OPTION>
      <OPTION VALUE="FTB4">Subject</OPTION>
      <OPTION VALUE="FMB1">Memo</OPTION>
    </SELECT></td>
    </tr><tr> 
    <td><font face="MS Gothic">Name Contains:</td>
    <td><INPUT TYPE="TEXT" NAME="From" VALUE="<%=strTB1%>"></td> 
    </tr><tr> 
    <td><font face="MS Gothic">E-mail Address Contains:</td>
    <td><INPUT TYPE="TEXT" NAME="EmailAdd" VALUE="<%=strTB2%>"></td> 
    </tr><tr> 
    <td><font face="MS Gothic">CC Contains:</td>
    <td><INPUT TYPE="TEXT" NAME="CC" VALUE="<%=strTB3%>"></td> 
    </tr><tr> 
    <td><font face="MS Gothic">Subject Contains:</td>
    <td><INPUT TYPE="TEXT" NAME="Subject" VALUE="<%=strTB4%>"></td> 
    </tr><tr> 
    <td><font face="MS Gothic">Memo Contains:</td>
    <td><INPUT TYPE="TEXT" NAME="Memo" VALUE="<%=strMB1%>"></td> 
    </tr> 
  </table> 
  <INPUT TYPE="SUBMIT" VALUE="Submit Search Parameters">
  </p> 

  <%
  'Create your connection string, create an instance of the Connection object,
  ' and connect to the database.
  strProvider = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\InetPub\Wwwroot\Tutorial\guestbook.mdb;" 
  Set objConn = Server.CreateObject("ADODB.Connection") 
  objConn.Open strProvider 
 
  'Define the query. 
  If iDelete = 0 Then
    'If the Delete variable is not set, the query is a SELECT query.
    '* means all fields. ASC means ASCII. % is a wildcard character.
    strQuery = "SELECT * FROM GuestBook"
    strQuery = strQuery & " WHERE FTB1 LIKE '%" & strTB1 & "%'"
    strQuery = strQuery & " AND FTB2 LIKE '%" & strTB2 & "%'"
    strQuery = strQuery & " AND FTB3 LIKE '%" & strTB3 & "%'"
    strQuery = strQuery & " AND FTB4 LIKE '%" & strTB4 & "%'"
    strQuery = strQuery & " AND FMB1 LIKE '%" & strMB1 & "%'"
    strQuery = strQuery & " ORDER BY " & StrSort & " ASC"
  Else
    'We want to delete a record.
    strQuery = "DELETE FROM GuestBook WHERE FID=" & iDelete
  End If

  'Executing the SELECT query creates an ADO Recordset object.
  'This holds the data you get from the database.
  Set objRS = objConn.Execute(strQuery)

  'Now that you have the database data stored in the Recordset object,
  ' show it in a table.
 %>

  <p>
  <FORM NAME="EditGuestBook" METHOD="GET" ACTION="viewgb.asp"> 
  <table border=1 cellpadding=4 >
  <% 
    On Error Resume Next

    If objRS.EOF Then 
      If iDelete = 0 Then
        Response.Write "<tr><td><font face=&quot;MS Gothic&quot;>There are no entries in the database.</font></td></tr>" 
      Else
        Response.Write "<tr><td><font face=&quot;MS Gothic&quot;>Record " & iDelete & " was deleted.</font></td></tr>"
      End If
    Else

      'Print out the field names using some of the methods and properties
      ' of the Recordset object.
      Response.Write "<tr>"

      'For each column in the current row...
      For i = 1 to (objRS.Fields.Count - 1)
                   ' write out the field name.
          Response.Write "<td><font face=&quot;MS Gothic&quot;><B>" & objRS(i).Name & "</B></font></td>"
      Next

      Response.Write "<td><font face=&quot;MS Gothic&quot;><B>Delete</B></font></td>"
      Response.Write "</tr>"

      'Print out the field data, using some other methods and properties
      ' of the Recordset object. When you see a pattern in how they are used,
      ' you can look up others and experiment.

      'While not at the end of the records in the set...
      While Not objRS.EOF

        Response.Write "<tr>"

        'For each column in the current row...
        For i = 1 to (objRS.Fields.Count - 1)
          ' write out the data in the field.
          Response.Write "<td><font face=&quot;MS Gothic&quot;>" & objRS(i) & "</font></td>"
        Next

        'Add a button that will pass in an ID number to delete a record.
        %><td><INPUT TYPE="SUBMIT" NAME="Delete" VALUE="<%=objRS(0)%>"></td><%

        Response.Write "</tr>"

        'Move to the next row.
        objRS.MoveNext

      Wend

    End If   'objRS.EOF 
  %> 
  </table> 
  </FORM> 

  <%
  'Close the Connection.
  objConn.Close
  %>

  </font>
  </body> 
  </html>

In the browser, you should see the following:

View Guest Book

Sort by which column: Check box
Name Contains: Check box
E-mail Address Contains: Check box
CC Contains: Check box
Subject Contains: Check box
Memo Contains: Check box
There are no entries in the database.

Lesson 4: Displaying an Excel Spreadsheet

This lesson requires that you have Microsoft Excel installed on your system and is not supported on 64-bit platforms until Excel is developed for 64-bit platforms.

This lesson demonstrates how to display a Microsoft Excel spreadsheet in a Web page. As in the previous lesson, you use ADO. However, in this lesson you connect to an Excel spreadsheet instead of an Access database.

Prepare your Excel spreadsheet to display in an Active Server Page

  1. Using either Excel 98 or Excel 2000, create a spreadsheet and save it as ASPTOC.xls in x:\Inetpub\Wwwroot\Tutorial. Do not include any special formatting or column labels when creating the spreadsheet.
  2. Fill in some of the fields with random data. Treat the first row of cells as column names.
  3. Highlight the rows and columns on the spreadsheet that you want to display in the Web page. (Click one of the fields and drag your mouse diagonally to highlight a block of fields.)
  4. On the Insert menu, select Name, and click Define. This is where all your workbooks are defined by name and range of cells.
  5. Make sure the cell range you highlighted is displayed correctly at the bottom. Type the name MYBOOK for your workbook, and select Add. Whenever you change MYBOOK, make sure the correct cell range is displayed in the Refers to text box at the bottom of the Define Name window. Simply selecting MYBOOK after highlighting a new block of cells does not update the cell range.
  6. If the name shows up in the workbook list, click OK. Save your spreadsheet.
  7. Close Excel to remove the lock on the file so that your ASP page can access it.

In the examples in Lesson 3, you specified a provider name in the connection string, which maps to a specific ADO DLL. In this example, you use a driver name, which causes ASP to use the default provider for that driver name.

Copy and paste the following code in your text editor, and save the file as ViewExcel.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/ViewExcel.asp in the address bar.

   <%@ Language=VBScript %> 

  <html> 
  <head> 
  <title>Display an Excel Spreadsheet in a Web Page</title> 
  </head> 
  <body>
  <font face="MS Gothic"> 
  <h2>Display an Excel Spreadsheet in a Web Page</h2> 

  <% 
   'Create your connection string, create an instance of the Connection object, 
   ' and connect to the database. 
   strDriver = "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Inetpub\Wwwroot\Tutorial\MyExcel.xls;" 
   Set objConn = Server.CreateObject("ADODB.Connection") 
   objConn.Open strDriver 

   'Selects the records from the Excel spreadsheet using the workbook name you saved.
   strSELECT = "SELECT * from `MYBOOK`" 

   'Create an instance of the ADO Recordset object, and connect it to objConn.
   Set objRS = Server.CreateObject("ADODB.Recordset") 
   objRS.Open strSELECT, objConn 

   'Print the cells and rows in the table using the GetString method.
   Response.Write "<H4>Get Excel data in one string with the GetString method</H4>" 
   Response.Write "<table border=1 ><tr><td>" 
   Response.Write objRS.GetString (, , "</td><td><font face=&quot;MS Gothic&quot;>", "</font></td></tr><tr><td>", NBSPACE) 
   Response.Write "</td></tr></table>" 

   'Move to the first record.
   objRS.MoveFirst 

   'Print the cells and rows in the table using the ViewGB.asp method.
   Response.Write "<H4>Get Excel data using MoveNext</H4>" 

   'Print out the field names using some of the methods and properties 
   ' of the Recordset object. 
   Response.Write "<table border=1 ><tr>" 

   'For each column in the current row... 
   For i = 0 to (objRS.Fields.Count - 1) 
     ' write out the field name. 
     Response.Write "<td><font face=&quot;MS Gothic&quot;><B>" & objRS(i).Name & "</B></font></td>" 
   Next 

   'While not at the end of the records in the set...
   While Not objRS.EOF

     Response.Write "</tr><tr>" 

     'For each column in the current row... 
     For i = 0 to (objRS.Fields.Count - 1) 
       ' write out the data in the field. 
       %><td><font face="MS Gothic"><%=objRS(i)%></font></td><% 
     Next 

     'Move to the next row. 
     objRS.MoveNext 

   Wend 

   Response.Write "</tr></table>"

   'Close the Connection.
   objConn.Close
  %> 

  </font> 
  </body> 
  </html>

In the browser, you should see the following:

Display an Excel Spreadsheet in a Web Page

Get Excel data in one string with the GetString method

A2 B2 C2
A3 B3 C3

Get Excel data using MoveNext

A1 B1 C1
A2 B2 C2
A3 B3 C3

Up Next: Using COM Components in ASP Pages