In this chapter, you will learn about one of the most powerful Web development environments available to WWW database developers using Visual Basic: Active Server Pages (ASPs). By using ASPs, you can write powerful Web pages that enable you to generate dynamic HTML and perform state management without using CGI or ISAPI programs. You also will learn about the newest database object model called Active Data Objects (ADOs). Of course, the best way to learn about Active Server Pages is to create a few ASP applications. In this chapter, you will learn how to use ASPs to create a site counter, advertising pages, and a timesheet application. You will use the ASP environment (ActiveX Server framework) to generate more complex applications in Chapters 22, "Writing Server-Side Applications with VB 5 ActiveX Components."
Denali was the code name for Microsoft's server-side scripting framework now called
the ActiveX Server framework. The ActiveX Server framework is part of Microsoft IIS
3.0 running on Windows NT 4.0. Figure 21.1 shows the architecture of the ActiveX
Server framework.
FIGURE
21.1. The ActiveX Server framework architecture.
As shown in Figure 21.1, the ActiveX Server framework consists of a server-side scripting engine with a default script language of VBScript. As well as providing server-side scripting, the framework provides advanced features required to write complex Web applications, such as intrinsic objects, and ActiveX components, such as ADO. Active Server Pages (ASPs) are the building blocks of the ActiveX Server framework. ASP scripts can use ActiveX components, manage state, retrieve information from databases, store information, and generate HTML on-the-fly to send back to the browser.
Now refer to Figure 21.1 to see how the Active Server framework actually works. A client using a Web browser requests an ASP from the Website. The IIS receives the client HTTP request and passes it to the Active Server framework runtime--an ISAPI application. The ASP file is parsed. HTML found in the file is sent back to the browser. Scripts found in the file are processed on the server instead of the client. The ASP script's output (which can be static or dynamic HTML) is sent back to the client browser. Because the scripts execute on the Web server, you can take advantage of any databases or components to which the server has access. Because ASP can send back only HTML, you don't have to worry about the scripting capabilities of the client browser unless you are using client-side scripts.
Creating an ASP requires only standard knowledge of HTML. As a matter of fact,
you can create an Active Server page by taking a standard HTML file and changing
the file extension to asp, which is the extension of an ASP. One powerful
feature of ASP is the capability to mix inline HTML and script. The script is processed
on the server, and the static and dynamic HTML is sent to the client browser. If
the browser user decides to view the source of the Web page, he sees only the HTML--not
the script used to generate the dynamic parts of the HTML. Figure 21.2 shows a simple
Web page created by using ASP.
FIGURE
21.2. A sample Web page generated by an
Active Server Page.
Listing 21.1 shows the HTML sent back to the browser.
Listing 21.1. HTML Sent Back to a Browser from an ASP.
<HTML> <HEAD> <Title> Sample Active Server Page </TITLE> </HEAD> <BODY> <Font size="4"> The current date and time is : 1/8/97 1:42:37 PM <P> Standard HTML mixed with script. For example let's count to five and convert each number to HTML. Count number = 1 <BR> Count number = 2 <BR> Count number = 3 <BR> Count number = 4 <BR> Count number = 5 <BR> <HR> <H2>That's all folks!</H2> </BODY> </HTML>
Listing 21.2 shows the ASP used to generate the sample page.
Listing 21.2. The Active Server Page Used to Generate a Simple HTML pPage.
<HTML> <HEAD> <Title> Sample Active Server Page </TITLE> </HEAD> <BODY> <Font size="4"> The current date and time is : <%=Now%> <P> Standard HTML mixed with script. For example let's count to five and convert each number to HTML. <% For x=1 to 5 %> Count number = <% =x %> <BR> <% Next x %> <HR> <H2>That's all folks!</H2> </BODY> </HTML>
Notice the differences between the HTML page sent back to the Web browser in Listing 21.1 and the HTML and script used to create the Web page shown in Listing 21.2.
ASPs give Web page developers the following advantages over standard Web application development:
So what are the drawbacks of using ASPs? Well, I haven't encountered many. Debugging can be tricky. The error messages returned by ASPs are much better than some of the error messages encountered while writing VBScript client applications, but debugging still can be difficult. Also, if you want to save information about a user from session to session, you still need to use cookies.
Before going further into ASP, it is important to understand what makes up an ASP application. An ASP application consists of a virtual directory on a Web server and all the files and directories that exist in the virtual directory. Each ASP application can contain a single file named Global.asa, which must be stored in the application root directory. The Global.asa file contains several object events (discussed later in this chapter in the section "Application Object") that can be used to initialize the application or user sessions. The application's virtual directory must have read and execute privileges on the IIS.
Active Server Pages consist of the following elements:
The Active Server Page environment is very familiar to an HTML or Visual Basic developer, because it combines HTML and VBScript to create Web applications. As you learned earlier, the default scripting language for the ActiveX Server framework is VBScript. You can change the default scripting language to be JScript by modifying a Registry key on the NT server. You also can write pages that use both VBScript and JScript.
Regardless of the script you use, the delimiters <% and %> denote script commands from HTML syntax in ASP. HTML tags and features remain unchanged in an Active Server Page. For example, the line
<% For x=1 to 5 %>
is the start of a VBScript For Next loop. The delimiters <% and %> indicate to IIS that the line is script and not HTML. This example shows a mix of HTML syntax with script:
<BODY> <Font size="4"> The current date and time is: <%=Now%> <P>
The tags <BODY>, <FONT>, and <P> are sent back to the browser along with the text The current date and time is:. The text <%=Now%> is not sent back to the browser, because it is enclosed in the script delimiters <% and %>. The equal sign (=) specifies that the output should be sent to the browser. In this case, the VBScript function Now, which retrieves the current date and time, sends the current date and time back to the browser.
You also can mix script and HTML within loops and conditional statements, as shown in this example:
<% If iNum > 10 Then %> <H2>Welcome to the Old Timers Club</H2> <%Else%> <H2>Welcome to the New Timers Club </H2> <%End%>
Script can span lines, as this example shows:
<% If iNum > 10 Then strMsg = "Welcome to the Old Timers Club" Else StrMsg = "Welcome to the New Timers Club" End =StrMsg `Send to the Browser %>
You can use the <SCRIPT> tag in statements, procedures, or commands to use any valid scripting language. For example, the following syntax declares a JScript code block:
<SCRIPT LANGUAGE=JSCRIPT> //Do something in Script </SCRIPT>
You can add to the <SCRIPT> tag an additional attribute called RUNAT, which tells the Active Server framework where to execute the script. Currently, the only valid value for the RUNAT attribute is SERVER. By using the RUNAT=SERVER attribute when creating procedures and functions, you can call from within an ASP page or from other ASP pages; otherwise, you get syntax errors when calling the procedure.
ASP supports Server-Side Include files so that you can include information before an ASP page is processed. The syntax for relative paths follows:
<!--#INCLUDE FILE="path/myinclude.inc"-->
To use an IIS virtual directory, use this syntax:
<!--#INCLUDE VIRTUAL="/includes/myinclude.inc"-->
You can use other ASP files as include files.
As always, you should use many comments when developing ASP pages. When commenting HTML sections, you need to use the HTML comment delimiters <!-- and -->. Comments in script sections use the standard comment syntax of the scripting language: a single quotation mark (`) for VBScript and two forward slashes (//) for JScript. When writing scripts, include many comments; the comments are not sent back to the browser, because the scripts are executed on the server.
ASP scripts execute on the server and are not sent to the browser. You might be asking yourself, "How do I generate Web pages with client-side validation?" Simple: Use the <SCRIPT> tag and then enclose the client-side scripts within the HTML comment delimiters <!-- and -->. The following example sends back a client-side script to display a message box when the procedure cmdSend_OnClick is invoked:
<SCRIPT LANGUAGE="VBSCRIPT">
<--
Sub cmdSend_OnClick
`Client side script
MsgBox "Hello World!"
End Sub
-->
</SCRIPT>
ASP variables and objects can have application, session, or page scope. Application scope enables the variable or object to be shared by all users. Session scope enables any page in a user session to share the object or variable. Page scope, the default, is available only while the page is processing. You will learn in the next section how to create application- and session-level variables. When using ActiveX components, an application scope component is a single instance of the component shared by all users that is destroyed when the application ends. A session scope component is a single instance of the component per user session and is destroyed when the session ends. A page scope object is created while processing the page and is destroyed when the page processing completes.
Active Server Pages provide several objects that do not need to be instantiated (created) before using them in scripts. These intrinsic (built-in) objects enable you to perform many tasks that previously would have required ISAPI or CGI programs. The built-in objects follow:
This section takes a closer look at each object.
Properties: None
Methods: Lock, Unlock
Events: Application_OnStart, Application_OnEnd
Collections: None
You can use the Application object to store variables with application scope that can be shared by multiple users. You can add application scope variables by referencing the variable and setting a value. For example, the following code sets the value of the application scope variable Start to "Step1":
Application("Start") = "Step1"
You can use the Lock and Unlock methods to prevent multiple users from simultaneously modifying the same application variable. The Application object has no properties, but it does have two events--Application_OnStart and Application_OnEnd--that exist in the Global.asa file. The Application_OnStart event is fired once when the first user accesses the application. After the Application_OnStart event is fired, it will not run again until an Application_OnEnd event occurs. The Application_OnEnd event occurs when the IIS is shut down.
Properties: SessionId, Timeout
Methods: Abandon
Events: Session_OnStart, Session_OnEnd
Collections: None
Whereas the Application object shares information about an entire application, the Session object stores information required for a user session. Variables or objects stored in the Session object can be used throughout the entire user's session. Using session scope variables enables you to easily perform state management in a multiuser environment. The following line adds a session-scope variable named employee:
Session("employee") = "Mark Spenik"
You also can create a session-scope object by using this code:
Set Session("object name") = Server.CreateObject("Object to Create")
You can invoke a Session object's methods by using this syntax:
Session("object name").Method
Or, you can obtain a reference by using the set command. Using the <OBJECT> Tag for Application and Session Scope You can use the <OBJECT> tag in the Global.asa file to declare components with application or session scope. Using the <Object> tag is much faster for creating application-and session-scope objects than using the CreateObject method of the Server object, because ASP does not instantiate components declared in <Object> tags until they are referenced by script. Another advantage of using the <OBJECT> tag is that the component automatically goes into the application or session name space, which means that you can refer to the component without referencing the Application or Session object. This technique will be demonstrated shortly in the first ASP example.
Properties: ScriptTimeout
Methods: CreateObject, HTMLEncode, MapPath, URLEncode
Events: None
Collections: None
The Server object enables you to access utility functions. You can use the ScriptTimeout property to set the amount of time a script can execute before timing out. The method you will use quite frequently is CreateObject. The CreateObject method creates an instance of the ActiveX server component (just like VB5.0's CreateObject function). After you create the component, you can use the component's methods and properties. The following example uses CreateObject to create an ADO connection:
<%Set oDBTime = Server.CreateObject("ADODB.Connection")%>
Properties: None
Methods: None
Events: None
Collections: ClientCertificate, Cookies, Form, QueryString, ServerVariables
The Request object is one of the most powerful intrinsic objects and certainly the most popular object for people who have struggled with CGI programs and parsing HTTP requests. The Request object enables ASP developers to easily retrieve values during an HTTP request from a client browser to a server. The capability to easily retrieve client browser values is made possible by using the Request object's various collections. You can use the Request object collections to retrieve cookies, values from forms, query string values, and environment variables. To access variables contained in the Request object collection, use this syntax:
Request.CollectionName("variable")
Now take a look at the Form collection. Processing form values can become tedious when using regular HTML and CGI; however, by using the Form collection, you easily can retrieve values entered in a form and submitted to the Web server. There's no need to parse and interrupt the string sent to the server; ASP does it all for you. Suppose that you have a textbox on a form called UserName. To get text entered in the textbox, you use the Form collection:
<%=Request.Form("UserName")%>
Processing a query string is just as easy. Suppose that the following URL request is sent to the server:
<A HREF="addtime.asp?EmpName=Mark+Spenik&Client=SAMS">
The following code retrieves the values and stores them in a local variable:
<% strName = Request.QueryString("EmpName")
strClient = Request.QueryString("Client") %>
The variable strName contains the string Mark Spenik. As stated earlier, ASP parses out the form's query string format delimiters for you. If multiple EmpNames are sent, the QueryString object creates a collection named EmpName that contains the multiple names, which can be accessed with this code:
Request.QueryString("EmpName")(1)
Request.QueryString("EmpName")(N)
If multiple EmpNames are sent to the server and you access the variable without an index value, all the values are returned in a comma-delimited string.
Properties: Buffer, ContentType, Expires, ExpiresAbsolute, Status
Methods: AddHeader, AppendToLog, BinaryWrite, Clear, End, Flush, Redirect, Write
Events: None
Collections: Cookies
You use the Response object to send information back to the client browser by using the various methods of the Response object. You use the Redirect method to direct a user to a URL. You use the Write method to send string information back to the current HTTP session, and you use the Cookies collection to set a cookie value. The following example redirects a user to the Microsoft Website:
<%Response.Redirect "http://www.microsoft.com"%>
This example sends HTML back to the client browser:
<%Response.Write "<H2>Thanks for the visit, come again!</H2>"%>
By using ASP, you can use ActiveX components to enhance your Web-based applications. You can use ActiveX components that you write yourself using Visual Basic 5.0 (this is discussed in the next chapter) or the ActiveX components included with ASP. The ActiveX components included with ASP follow:
You will become familiar with some of the other ActiveX components in the examples in this chapter, as well as the examples presented in Chapter 22.
Before moving on to ADO and OLE DB, take a look at how you can use ASP and some
of the intrinsic objects and ActiveX components discussed so far to create a home
page. In this example, the home page will keep track of the number of site visitors
and use the Ad-rotator component to display a few advertisements. The visitor counter
will be stored in a text file so that, after the Web server is shut down and restarted,
the counter will reflect the accurate number of visitors. If the user clicks on one
of the advertisements displayed, the application will redirect them to the Macmillan
Publishing Website and store the number of redirects performed in a text file. Figure
21.3 shows the completed ASP application.
FIGURE
21.3. An active server home page with
an advertisement example.
The application uses the following intrinsic objects:
The following ActiveX components are used:
Now you'll examine how the application was put together. First, look at the contents of the file Global.asa, which contains the events for the Application and Session objects. Listing 21.3 shows the Application object Application_OnStart event.
Listing 21.3. The Application_OnStart Event.
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
SUB Application_OnStart
`Active Server Page Example
`Author: Mark Spenik
`Revision History: Jan. 6, 1997
` Global application file. Contains application and session startup and end events
`
`Application Startup Event
`Open a file with the site count. We are writing the count to a file
`since Application variables are lost when the IIS server is shut down.
`Set up the path for the file with the site count.
strVisitCountFile = Server.MapPath ("/TimeSheet") + "\visitc.txt"
`Get an instance of the FileSystemObject
Set oFile = Server.CreateObject("Scripting.FileSystemObject")
`Open the File
Set oCount= oFile.OpenTextFile (strVisitCountFile, 1, FALSE, FALSE)
`Initialize the Vistor Counter
Application("NumVisitors") = oCount.ReadLine
oCount.Close
END SUB
</SCRIPT>
In the Application_OnStart event, the file visitc.txt is read and stored in the Application variable NumVisitors. The file is opened by creating an instance of the Scripting.FileSystemObject using the CreateObject method of the intrinsic Server object.
After the Application_OnStart event fires, the Session_OnStart event shown in Listing 21.4 is executed.
Listing 21.4. The Session_OnStart Event.
<SCRIPT LANGUAGE=VBSCRIPT RUNAT=Server>
Sub Session_OnStart
`Let's use the Application Object to keep track of the number
`of visitors for this page.
`Set up the path for the file with the site count.
strVisitCountFile = Server.MapPath ("/TimeSheet") + "\visitc.txt"
`Get an instance of the FileSystemObject
Set oFile = Server.CreateObject("Scripting.FileSystemObject")
`Open the File
Set oCount= oFile.CreateTextFile (strVisitCountFile, TRUE, FALSE)
Application.Lock `Prevent another user from updating
`The counter while we update it.
Application("NumVisitors") = Application("NumVisitors") + 1
`Write out to the file
oCount.WriteLine(Application("NumVisitors"))
Application.UnLock `Unlock for other users
`Change the timeout for the session object.
Session.Timeout = 5 `Time the session out in 5 Minutes
oCount.Close
End Sub
Sub Session_OnEnd
End Sub
</SCRIPT>
In the Session object OnStart event, the number-of-visitors counter, NumVisitors, is incremented and written back to the file visitc.txt. The Application object methods Lock and Unlock are used to make sure that the global variable is not being incremented by another session. The application writes the number-of-visitors counter to the file for every session. In a heavily used Website, you might want to write the counter to the file only every 50 to 100 sessions. The Session.Timeout value that defaults to 20 minutes is set to 5 minutes. The application does not contain an Application_OnEnd or Session_OnEnd event. The last item in the Global.asa file is the creation of the application-level Ad-rotator object called oAd, shown here:
<OBJECT RUNAT=Server SCOPE=Session ID=oAd PROGID="MSWC.Adrotator"> </OBJECT>
The <OBJECT> tag is used instead of the CreateObject method for the performance and name-referencing benefits described earlier in this chapter. The file aspex2.asp is the starting page for the application and is shown in Listing 21.5.
Listing 21.5. The Active Server Page aspex2.asp.\
<HTML>
<!-- Active Server Page Example Using Objects and Componets-->
<!-- Author: Mark Spenik -->
<!-- Revision History: Jan. 6, 1997 -->
<!-- -->
<!-- This example uses several Active Server components and -->
<!-- intrinsic objects to display various advertisements. -->
<!-- This sample also includes a working page counter and -->
<!-- redirection to the advertiser's site. A counter is also -->
<!-- kept for each time a user uses this page to get to the -->
<!-- advertiser's site. -->
<!-- -->
<HEAD><TITLE>ASP Objects</TITLE></HEAD>
<STYLE>
H2 { color:Blue }
P { font-size: 12pt;color:Purple}
SPAN { background:yellow }
UL {font-family:arial;font-size 12pt;}
</STYLE>
<BODY BGCOLOR="ghostwhite">
<H2>WWW Database Developers Guide Sample ASP Object Page</H2>
<HR>
This sample page makes use of the following Active Server Page
objects and components:
<UL>
<LI>Application
<LI>AdRotor
<LI>Session
<LI>FileSystem
</UL>
<BR>
<SPAN>
Welcome Site Vistor Number <%=Application("NumVisitors")%>.
</SPAN>
<HR Size=2>
<P>
Other books by Spenik, Swank and Kittel...
<P>
<%= oAd.GetAdvertisement("adsams.txt") %>
<p>
<FORM METHOD="POST" ACTION="aspex2.asp">
<INPUT TYPE=SUBMIT Value="View Next Ad">
</FORM>
</BODY>
</HTML>
The file aspex2.asp consists almost entirely of standard HTML and only a few lines of script. The following line, for example, uses the Adrotator object to retrieve an advertisement using the rotator-schedule file shown in Listing 21.6:
<%= oAd.GetAdvertisement("adsams.txt") %>
The Ad-rotator component's schedule file has the following optional parameters:
Redirect URL Width Height Border
The Redirect parameter is the file or DLL to implement redirection. Height and Width specify the number of pixels to use for the advertisement, and Border specifies the border thickness of the hyperlink border around the advertisement. After the optional parameters come the parameters for each advertisement, which have the following format:
advertismentURL advertismentHomePageURL AdText AdImpressions
Here, advertismentURL is the URL to the advertisement's image file. advertismentHomePageURL is the address to the advertiser's home page. AdText is displayed if the browser does not support graphics and is displayed as a Tooltip in Internet Explorer 3.0. AdImpressions is a number between 0 and 4,294,967,295 that determines the frequency at which the advertisement is displayed on the page. For this example, AdImpressions was set to 35, 35, and 30. This translates to 35 percent, 35 percent, and 30 percent.
Notice that, because the <OBJECT> tag in the file Global.asa was used to create the application-scope object, you do not need to reference the Application object to use the Adrotator object oAd.
The last file used in the application is the redirect file defined in the Ad-rotator schedule file in Listing 21.6. When a user clicks on one of the advertisements displayed, the redirect file is executed. Listing 21.7 shows the redirect file cntad.asp.
Listing 21.6. The Ad-rotator schedule file: adsams.txt.
redirect cntad.asp width 125 height 155 border 1 * sqlsrv.gif http://www.mcp.com/mcp The DBA Survival Guide by Spenik & Sledge 35 wwdb.gif http://www.mcp.com/mcp/ The World Wide Web Database Developer's Guide by Swank & Kittel 35 index.gif http://www.mcp.com/mcp/ The Microsoft Index Server Guide by Swank & Kittel 30
Listing 21.7. The redirect ASP file: cntad.asp.
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
SUB UpdateCounter
` This script executes when the user clicks an Ad and redirects
` to the Macmillan Publishing site.
`Set up the file name
RedirectCountFilename = Server.MapPath ("/TimeSheet") + "\redirect.txt"
`Create the File System Object
Set oFileObject = Server.CreateObject("Scripting.FileSystemObject")
`Open The File
Set oFile= oFileObject.OpenTextFile (RedirectCountFilename, 1, FALSE, FALSE)
Application("iNumRedirects") = oFile.ReadLine
oFile.Close
Set oFile= oFileObject.CreateTextFile (RedirectCountFilename, True, FALSE)
`Lock the file so we have can update the counter value.
Application.lock
`Increment and write the value back to the file
Application("iNumRedirects") = Application("iNumRedirects") + 1
oFile.WriteLine(Application("iNumRedirects"))
`Unlock the application
Application.unlock
`Close the file
oFile.Close
END SUB
</SCRIPT>
<%Call UpdateCounter
`Now Redirect the browser to the Macmillan Publishing site.
Response.Redirect "http://www.mcp.com/mcp"
%>
The script used to update the number-of-redirects counter is similar to the script used to update the number-of-visitors counter. The following script uses the intrinsic object Response to redirect the browser to the Macmillan Publishing Website:
Response.Redirect "http://www.mcp.com/mcp"
The ADO component installed with Active Server Pages provides a simplified, high-powered
data object model that enables you to access databases using an OLE DB provider,
including the Microsoft ODBC provider. OLE DB is Microsoft's next-generation specification
for data access. The general idea of OLE DB is for data consumers (applications)
to access a wide variety of data through service providers. OLE DB is based on the
need to access and manipulate other forms of data besides relational databases using
a native dialect (not necessarily SQL). If you want to retrieve spreadsheet cells
from an Excel spreadsheet, you would use the service provider for Excel spreadsheets;
for ODBC databases, you would use the ODBC database service provider. OLE DB is an
API set that enables C++ developers to create data providers, service providers,
and data consumers. ADO enables Visual Basic developers to use the OLE DB ODBC service
provider to access and manipulate database. ADO resembles RDO and ODBC Direct in
some ways, but it has a much simpler object model, as Figure 21.4 shows.
FIGURE
21.4. The ADO model.
To simplify scripting and programming, many of the ADO objects can be created independent of other objects. The Recordset object, for example, does not require the programmer to create a Connection object first (a loose object hierarchy). Using independent objects might seem a bit strange to an experienced DAO programmer, but once you get used to the ADO model, you will find it simple to use. By using ADO, you can create fully scrollable recordsets that can be updated. ADO supports the following features:
For a Visual Basic database Web developer, ADO provides one of the simplest methods to use database information across the World Wide Web.
Because ADO uses the OLE DB ODBC service provider, you can access any ODBC database
to which your Web server has access. The only requirement for using a database with
ADO on your IIS 3.0 Web server is a system ODBC data source name (DSN). After the
system DSN is added, you can use ADO to access the database, as shown in Figure 21.5.
FIGURE
21.5. Accessing a database via the Web
using ADO.
Before using ADO in an Active Server Page, take a quick look at the ADO Connection object and Recordset object.
Use the Connection object to establish a session with an OLE DB data source. You can use the Connection object to close the connection to the data source, control transactions, create recordsets, or execute commands. The following example creates a Connection object to a Microsoft database using a DSN called TimeSheet:
<% `Create an instance of the ADO object
Set oDBTime = Server.CreateObject("ADODB.Connection")
`Open the database
oDBTime.Open "TimeSheet" %>
The Recordset object consists of the returned rows of a query or executed stored procedure. The Recordset object can be created independent of any other objects and supports many powerful features, such as the capability to support multiple query result sets. Recordset objects can be created by using the Execute method of the Connection object and the Command object or the Open method of the Recordset object (discussed later). The following code shows an example of creating an independent Recordset object:
`Create an ADO recordset to add a new record
set rsTimeSheet = CreateObject("ADODB.Recordset")
`Set the Recordset Properties
rsTimeSheet.CursorType = adOpenKeyset
rsTimeSheet.Source = "Select * From EmpTime"
rsTimeSheet.Open
The idea of creating a Recordset object without first defining a query or table will seem unusual to DAO programmers. When you create an independent Recordset object, a Connection object still is created, but it is not assigned to an object variable. You can assign an existing Connection object to a Recordset object before you open the Recordset object by using the ActiveConnection property or using the ActiveConnection argument in the Recordset Open method. The Recordset object can have one of the following cursor types:
Forward-Only Forward-Only creates a static cursor that provides forward scrolling only, which improves performance. Use a Forward-Only cursor when you need to make only a single pass through the data--for example, when you are filling a combo box on a Web page. Forward-Only is the default cursor used by ADO. You can make modifications to a Forward-Only cursor; however, changes made by other users are not visible. Keyset A Keyset cursor allows forward and backward movement, as well as bookmarks, through the recordset. The Keyset cursor is updatable, so you can add, delete, or modify records. Records added or deleted by other users are not visible; however, updates made by other users are visible. Dynamic The Dynamic cursor contains an accurate recordset of all the current records, including those being added, updated, or deleted by other users. All types of recordset cursor movement are provided, except for bookmarks, which are supported only if the provider supports them. Static A Static cursor is a copy (picture) of the query data at the time the cursor was created. A Static cursor provides all directions of movement as well as a cursor that can be updated. Changes made by other users do not appear until the recordset is refreshed.
You can use the following methods to move through the recordset:
You also can use the AbsolutePosition property to move to an absolute row position in the cursor.
To help determine whether you are at the end or start of the recordset, use the BOF and EOF properties of the Recordset object. The BOF and EOF properties return Boolean values (TRUE/FALSE) based on the current position of the recordset. If BOF or EOF is TRUE, there is no current row, and any attempt to move through the recordset results in an error. When a recordset is open and contains one or more rows, both BOF and EOF are FALSE. If both BOF and EOF are set to TRUE and the Recordset RowCount property is 0, no records are contained in the recordset.
If you have created a recordset that can be modified, you can use the AddNew method of the recordset, which has the following syntax:
Recordset.AddNew
The AddNew method creates a placeholder in the recordset for the new row. You then populate each column in the recordset with data and invoke the Update method to add the record. To modify an existing record, modify the fields you want to change and use the Update method.
To delete the current record in the recordset, use the Delete method, which has the following syntax:
Recordset.Delete AffectRecords
The parameter AffectRecords is an optional parameter that determines how many records are deleted. The default is to delete only the current record. The value adAffectGroup deletes all records that satisfy the Filter property of the recordset.
In this section, you'll try some ADO examples by creating an application that retrieves and adds records to a database using ASP and ADO. The sample application shown in Figure 21.6 is a timesheet application.
FIGURE
21.6.An ASP timesheet application.
I have worked for several computer consulting companies, and one thing always remains the same: you gotta do your timesheet daily! So this timesheet application enables you to enter your timesheet over the World Wide Web. After you fill out the timesheet and submit the form, the timesheet entry is saved on an Access database on the Web server.
Some important application features follow:
The timesheet application does not have a Global.asa file, so there are no application or session events. The main ASP file is times.asp. Listing 21.8 shows the ASP HTML and script for times.asp.
Listing 21.8. ASP HTML and script for the file times.asp.
<HTML>
<!-- Active Server Page Example Using Active Data Objects -->
<!-- Author: Mark Spenik -->
<!-- Revision History: Jan. 6, 1997 -->
<!-- This example uses Active Data Objects to fill a combo box -->
<!-- and to store the data entered in the timesheet. -->
<!-- This example also uses Active Server Pages to execute client and server -->
<!-- side scripts. -->
<!-- -->
<HEAD>
<Title>Active Server Page TimeSheet Application</TITLE>
</HEAD>
<BODY BGCOLOR="Tan">
<CENTER>
<H2>Timesheet Application using Active Server Pages and ADO</H2>
</CENTER>
<HR SIZE=2>
<!-- Declare the start of the form. -->
<FORM NAME="TimeSheet" METHOD="POST" ACTION="http://www.kscsinc.com/
Times/addtime.asp">
<B>Employee:</B> <INPUT TYPE="text" NAME="txtEmployee" Size=30 Value="">
<p>
<B>Select the Client</B>
<% `Create an instance of the ADO Recordset object
set rsClients = CreateObject("ADODB.Recordset")
`Open a resultset based on the SQL Statement
rsClients.Open "SELECT ClientName FROM Clients","DSN=TimeSheet"
%>
<!-- Notice in the following section the mixture of HTML with server side script -->
<SELECT NAME="cmbClient">
<% Do While Not rsClients.EOF %>
<OPTION><%=rsClients("ClientName")%>
<%
rsClients.MoveNext
Loop
rsClients.Close
Set rsClients = Nothing
%>
</SELECT>
<p>
<B>Select the billing code</B>
<SELECT ALIGN=CENTER Name="cmbCode">
<OPTION>Holiday
<OPTION>Vacation
<OPTION>Sick
<OPTION>Client Server Consulting
<OPTION>Work Group Consulting
<OPTION>Internet Consulting Services
</Select>
<p>
<B>Date:</B> <INPUT TYPE="text" NAME="txtWorkDate" Size=15 Value=<% =Date %>>
<p>
<B>Hours:</B> <INPUT TYPE="text" Name="txtHours" Size=4 Value="8.0">
<BR>
<HR>
<INPUT TYPE="Button" Name="cmdAdd" Value="Validate TimeSheet">
<INPUT TYPE=SUBMIT VALUE="Submit Form">
<SCRIPT LANGUAGE="VBScript">
<!--
Sub window_onLoad()
Dim CurrentForm
`Set the focus to the text box
Set CurrentForm = Document.TimeSheet
CurrentForm.txtEmployee.Focus
Set CurrentForm = Nothing
end sub
Sub cmdAdd_OnClick
Dim vTemp
Dim CurrentForm
`Set the focus to the text box
Set CurrentForm = Document.TimeSheet
`Perform client side validation before sending the
`results to the server.
vTemp = CurrentForm.txtEmployee.Value
If VTemp = "" Then
MsgBox "You must enter an Employee Name.",48, "Blank Name"
Exit Sub
End If
`Make sure the date is valid.
vTemp = CurrentForm.txtWorkDate.Value
If NOT IsDate(vTemp) Then
MsgBox "You must enter a valid date in the date field.",48, "Invalid Date"
Exit Sub
End If
`Make sure they at least worked 8 hours
vTemp = CurrentForm.txtHours.Value
If vTemp < 8.0 Then
MsgBox "You must enter at least 8 hours in the hours field.",
Â48, "Incorrect Number of Hours"
Exit Sub
End If
`If you wanted to submit the form from the client validation routine, you
`would have to parse the form and build the query string to send back.
`The following line is an example of what is required. Note only the
`first variable txtEmployee is shown. You would have to include all
`of the fields in the query string.
`Window.Location.HREF = "http://www.kscsinc.com/Times/
'addtime.asp?txtEmployee=Mark"
End Sub
-->
</SCRIPT>
</BODY>
</HTML>
Now look at some of the HTML and script used in the ASP file. First examine how an HTML selection list is populated using an ADO recordset. The script and HTML used to populate the selection list is a good example of combining ASP, ADO, and HTML to produce Web pages using database information. I think you will find from a developer perspective the improvement of ADO over htx and idc files. The selection list population script follows:
`Open a resultset based on the SQL Statement
Set rsClients = oDBTime.Execute(SQLQuery) %>
<!-- Notice in the following section the mixture of HTML with server side script -->
<SELECT NAME="cmbClient">
<% Do While Not rsClients.EOF %>
<OPTION><%=rsClients("ClientName")%>
<%
rsClients.MoveNext
Loop
%>
</SELECT>
This example shows you how to use ASP to send client-side validation scripts back to the client. The form validation could have been done on the server after the client had submitted the form.
Test the client-side validation by leaving the Employee Name field empty and click the Validate TimeSheet button (see Figure 21.6). A message box appears, notifying you that you must enter an employee name. For the sake of showing many different ASP features in a single form, the timesheet application has two buttons instead of one. Normally, the Validate TimeSheet button would validate and submit the form. To do this, you must set the IE Window.Location.HRef property to the proper ASP file with a parsed query string. I did not write the function to scan the form and build the proper query string. Instead, I added a Submit button to the page to show how you can use the Active Server Page Request object to parse a form. Listing 21.8 shows the client-validation script in the <SCRIPT> tag section between the HTML comment delimiters <!-- and -->.
The Submit button on the form calls the URL addtimes.asp, which is shown in Listing 21.9.
Listing 21.9. ASP addtimes.asp code.
<%
`Make sure user started at the proper pages and
`filled in the form values
If Request.Form("txtEmployee") = "" OR Request.Form("txtHours") = "" Then
Response.Redirect("Times.asp")
End If
%>
<HTML>
<HEAD>
<Title> TimeSheet Response </TITLE>
</HEAD>
<BODY>
<%
`Create an instance of the ADO object
Set oDBTime = Server.CreateObject("ADODB.Connection")
`Open the database
oDBTime.Open "TimeSheet"
`Create an ADO recordset to add a new record
set rsTimeSheet = CreateObject("ADODB.Recordset")
`Set the Recordset Properties
rsTimeSheet.CursorType = adOpenKeyset
rsTimeSheet.LockType = adLockOptimistic
rsTimeSheet.ActiveConnection = oDBTime
rsTimeSheet.Source = "Select * From EmpTime"
rsTimeSheet.Open
`Check to make sure Updates are supported
sResponse = "<H2>Sorry your timesheet was not added. Invalid server
Âcursor.</H2>"
If rsTimeSheet.Supports(adUpdate) Then
` rsTimeSheet.Open "SELECT * FROM EmpTime", oDBTime,
ÂadOpenKeyset, adLockBatchOptimistic
`Add a new record
rsTimeSheet.AddNew
`Set the data values
rsTimeSheet("Employee") = Request.Form("txtEmployee")
rsTimeSheet("WorkDate") = Request.Form("txtWorkDate")
rsTimeSheet("ClientName") = Request.Form("cmbClient")
rsTimeSheet("BillCode") = Request.Form("cmbCode")
rsTimeSheet("Hours") = Request.Form("txtHours")
`Add the record
rsTimeSheet.Update
`Check for Errors
If oDBTime.Errors.Count > 0 Then
Set oError = oDBTime.Errors(0)
If oError.Number <> 0 Then
sResponse = "<H3>Timesheet Error </H3><BR><P>"
sResponse = sResponse & "Error adding your timesheet
Âto the database."
sResponse = sResponse & "Error: " & oError.Description
End If
Else
sResponse = "<H2>Timesheet Added.</H2><BR><H3>Thanks!</H3><BR><P>"
End If
End If
Response.Write sResponse
rsTimeSheet.Close
oDBTime.Close
%>
</BODY>
<!--#include virtual="/ASPSamp/Samples/adovbs.inc"-->
</HTML>
The addtimes.asp script uses an example of state management and minimum validation to determine whether you have assessed the page without coming from the times.asp page or whether you have left some fields empty. If the fields are empty, the script redirects the user's browser to the starting ASP page, times.asp. The script to perform this feature follows:
<%
`Make sure user started at the proper pages and
`filled in the form values
If Request.Form("txtEmployee") = "" OR Request.Form("txtHours") = "" Then
Response.Redirect("Times.asp")
End If
%>
A Recordset object is created independently and then assigned to the existing connection. The Recordset object properties are set to create an updatable recordset, and the Recordset object is created using the Open method, as shown in this code:
`Create an ADO recordset to add a new record
set rsTimeSheet = CreateObject("ADODB.Recordset")
`Set the Recordset Properties
rsTimeSheet.CursorType = adOpenKeyset
rsTimeSheet.LockType = adLockOptimistic
rsTimeSheet.ActiveConnection = oDBTime
rsTimeSheet.Source = "Select * From EmpTime"
rsTimeSheet.Open
You can check the capabilities of a Recordset object by using the Supports method. The following line of script queries to check whether the Recordset object created can be updated:
If rsTimeSheet.Supports(adUpdate) Then
The code to add the new record and populate the new row will look very familiar to Visual Basic database developers. Notice how the data is retrieved from the form using the ASP Request object. The script follows:
`Add a new record
rsTimeSheet.AddNew
`Set the data values
rsTimeSheet("Employee") = Request.Form("txtEmployee")
rsTimeSheet("WorkDate") = Request.Form("txtWorkDate")
rsTimeSheet("ClientName") = Request.Form("cmbClient")
rsTimeSheet("BillCode") = Request.Form("cmbCode")
rsTimeSheet("Hours") = Request.Form("txtHours")
`Add the record
rsTimeSheet.Update
If an error occurs, an Error object is added to the Connection object's Errors collection. You can check to see whether an error has occurred by checking the number of Error objects in the collection, as this code shows:
If oDBTime.Errors.Count > 0 Then
If the record is saved successfully, a success string is sent back to the browser,
as shown in Figure 21.7.
FIGURE
21.7. The ASP timesheet application successful
entry screen.
The string is built and sent back to the client browser by using the Response object:
Response.Write sResponse
Finally, to use the constants for ADO in the script file, you must include the ADO include file adovbs.inc, which is installed with ASP with this code:
<!--#include virtual="/ASPSamp/Samples/adovbs.inc"-->
This chapter introduced you to the Microsoft ActiveX Server framework and Active Server Pages. You should be familiar with the available intrinsic objects and components supplied with ASP, as well as the general syntax used to create Active Server Pages. Active Server Pages provide Web developers with a very powerful toolset for generating dynamic and interactive Web pages. You also were introduced to a database model that uses OLE DB called Active Data Objects (ADO).
In the next chapter, you will look at creating your own ActiveX Server components and using them from ASP.
