Scripting the DE Object | Programming Microsoft Visual InterDev 6.0

The Data Environment provides a special object that you can use to script server-based database access: the DE object. This object exposes an object model that you can use to execute database commands and manage their recordsets. The DE object is a simplified version of the ActiveX Data Objects (ADO) model.

Before starting to script the DE object, you'll need to add a data connection to your Web project within Microsoft Visual InterDev. Once you have done this, Visual InterDev adds script to the global.asa file to create a DE object for you to work with. The Visual InterDev_generated script also specifies the connection information for the DE object. Figure 13-1 shows a sample global.asa file. You can see that the DE object is created using the Server.CreateObject syntax and is then stored in an Application-level variable named DE. All of this code was generated automatically when the data connection was created.

Figure 13-1. A global.asa file showing how the DE object is created in the Application_Onstart event.

<SCRIPT LANGUAGE=VBScript RUNAT=Server> 'You can add special event handlers in this file that will get  'run automatically when special Active Server Pages events occur. 'To create these handlers, just create a subroutine with a name  'from the list below that corresponds to the event you want to 'use. For example, to create an event handler for Session_OnStart, 'you would put the following code into this file (without the 'comments): 'Sub Session_OnStart '**Put your code here ** 'End Sub 'EventName              Description 'Session_OnStart        Runs the first time a user runs any page '                       in your application 'Session_OnEnd          Runs when a user's session times out or '                       quits your application 'Application_OnStart    Runs once when the first page of your '                       application is run for the first time by '                       any user 'Application_OnEnd      Runs once when the Web server shuts down </SCRIPT> <SCRIPT LANGUAGE=VBScript RUNAT=Server> Sub Application_OnStart     '==Visual InterDev Generated - startspan==     '--Project Data Connection     Application("VIBankconn_ConnectionString") = "DRIVER=SQL         Server;SERVER=ntserver;UID=sa;APP=Microsoft(R)         Windows NT(TM) Operating System;WSID=NTSERVER;         DATABASE=bank;User Id=sa;"     Application("VIBankconn_ConnectionTimeout") = 15     Application("VIBankconn_CommandTimeout") = 30     Application("VIBankconn_CursorLocation") = 3     Application("VIBankconn_RuntimeUserName") = "sa"     Application("VIBankconn_RuntimePassword") = ""     '-- Project Data Environment     Set DE = Server.CreateObject("DERuntime.DERuntime")     Application("DE") = DE.Load(Server.MapPath("Global.ASA"),         "_private/DataEnvironment/DataEnvironment.asa")     '==Visual InterDev Generated - endspan== End Sub </SCRIPT>

By scripting the DE object, you can achieve many of the same end results as when you use Recordset DTCs and data-bound controls. For example, you can execute database commands (including SQL queries, parameterized queries, and stored procedures associated with command objects) and work with recordsets.

Executing Database Commands

To execute a database command using the DE object, you first create an instance of the DE object on your Web page. You can then execute any data command object associated with the DE object. This means any data command that you have created and placed under your data connection within the Visual InterDev Data Environment.

Creating a DE object

If you have the Scripting Object Model enabled, use the following syntax to create a DE object for your Web page:

<% thisPage.createDE() %>

If you do not have the Scripting Object Model enabled, use the syntax shown here:

<% Set DE = Server.CreateObject("DERuntime.DERuntime") DE.Init(Application("DE")) %>

Executing a SQL query

To execute a SQL query associated with a data command object, use the following syntax:

<% DE.commandObjectName %>

If the command takes parameters, use the syntax shown here:

<% DE.commandObjectName (parameter1, parameter2, […]) %>

If the command returns a value, use the this syntax:

<% RetValue = DE.commandObjectName (parameter1, parameter2, […]) %>

Working with Recordsets

After you have executed a data command using the DE object, you can access the recordset and navigate through it with Recordset objects. To give you access to the recordset, the DE object creates a Recordset object named after the data command object but with an "rs" prefix. Here is the syntax for accessing the Recordset object:

<% DE.rscommandObjectName %>

For example, if your data command object is named AccountCode, the Recordset object is named rsAccountCode.

Extracting values within a recordset

Follow these steps to extract values within a recordset:

Create the recordset, and set a variable to point to the DE Recordset object, as shown here:
```
<% DE.AccountCode Set rs = DE.rsAccountCode %> 
```

Extract individual values from the Fields collection of the Recordset object, as shown here:

<% DE.AccountCode Set rs = DE.rsAccountCode Account_Id = rs.Fields("Account_Id") Account_Description = rs.Fields("Account_Description) %>

Navigating a recordset

Follow these steps to navigate a recordset:

Use the moveNext, movePrevious, moveFirst, or moveLast methods of the Recordset object.

Use the EOF or BOF properties to determine whether you are at the end or the beginning of the Recordset object.

DE Object Scripting Examples

Figure 13-2 shows an example of scripting the DE object to display a list of account descriptions on a Web page. This example is taken from the AccountCodeDE.asp page within the VI-Bank Web project on the companion CD-ROM.

NOTE
The VI-Bank Web project is a sample Internet Banking application that we'll be using throughout this chapter and many of the following chapters —especially chapters on three-tier transactional applications. VI-Bank is primarily a Microsoft SQL Server 6.5_based application. However, several of its Web pages—including the examples in this chapter—can be run against the Microsoft Access database that is also supplied with the application.

Figure 13-2. Source code for the AccountCodeDE.asp Web page showing how the DE object can be scripted to query and display data from a relational database.

<%@ Language=VBScript %> <% ' VI 6.0 Scripting Object Model Enabled %> <!--#include file="_ScriptLibrary/pm.asp"--> <% if StartPageProcessing() Then Response.End() %> <FORM name=thisForm METHOD=post> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <TITLE>Vi-Bank - Account Code Listing</TITLE> <LINK REL="stylesheet" TYPE="text/css"     HREF="_Themes/blueprnt/THEME.CSS"     VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/GRAPH0.CSS"     VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"     HREF="_Themes/blueprnt/COLOR0.CSS"     VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"     HREF="_Themes/blueprnt/CUSTOM.CSS"     VI6.0THEME="Blueprint"></HEAD> <BODY> <TABLE> <TR valign=top> <TD width=125> <!--#INCLUDE FILE="maintmenu.htm"--> </TD> <TD> <H2><FONT COLOR="navy"><I>VI-Bank - Account Code Listing</I></FONT> </H2> <HR style="COLOR: navy"> <P> <% thisPage.createDE() DE.AccountCode Set rs = DE.rsAccountCode Response.Write "<TABLE BORDER=1 CELLSPACING=2 CELLPADDING=2>" + _     "<TR><TH>Account Code</TH><TH>Account Description</TH></TR>" Do While Not rs.EOF     Response.Write "<TR><TD>" + CStr(rs.Fields("account_id")) + _         "</TD>" + "<TD>" + rs.Fields("account_description") + _         "</TD></TR>"     rs.MoveNext Loop Response.Write "</TABLE>" %> </TD> </TR> </TABLE> </BODY> <% ' VI 6.0 Scripting Object Model Enabled %> <% EndPageProcessing() %> </FORM> </HTML>

Figure 13-3 shows the resulting output from the AccountCodeDE.asp page. In just nine lines of code, the DE object has been used to query the Account_Code table and then present the results in an HTML table on screen.

click to view at full size.

Figure 13-3. Output from the AccountCodeDE.asp Web page, showing the contents of the Account_Code table.

The DE object accesses the AccountCode data command object within the VI-Bank Web project for the source of the query. The query is a simple SQL SELECT statement, as shown here:

SELECT account_id, account_description FROM account_code ORDER BY account_id

NOTE
When working with data command objects, it is often preferable to select SQL Statement as the source of the data. You then write the SQL code yourself within the Properties dialog box, as opposed to simply selecting a Database Object data source and selecting a table from the drop-down list box. By using hand-coded SQL, you can specify the sort order for your recordset and have more control over the result.

This example simply outputs the records for viewing within your browser. This type of multirow output is often combined with the ability to select a specific record and then edit that record on another Web page. This was achieved within Visual InterDev 1.0 by using the Data Form Wizard, which generated both form views and list views. To enable individual records within your output HTML table to be selected and launch another Web page, you might add some code, as in the following example:

<% thisPage.createDE() DE.AccountCode Set rs = DE.rsAccountCode Response.Write "<TABLE BORDER=1 CELLSPACING=2 CELLPADDING=2>" + _     "<TR><TH>Account Code</TH><TH>Account Description</TH></TR>" Do While Not rs.EOF     Response.Write _         "<TR><TD><A HREF=accountcodemaint.asp?account_id=" + _         CStr(rs.Fields("account_id")) + ">" + _         CStr(rs.Fields("account_id")) + "</A></TD>" + _         "<TD>" + rs.Fields("account_description") + "</TD></TR>"     rs.MoveNext Loop Response.Write "</TABLE>" %>

In this example, a hyperlink is added to the output of the first column within the HTML table. This way the accountcodemaint.asp page is called and is passed the value of the account_id for editing. The accountcodemaint.asp page can use this value as a record locator so that it knows which record to query.

From these examples, you can see how easy it is to script the Data Environment and to output resultsets to the Web page. In situations where you want to be able to navigate a lengthy recordset, perhaps ten rows at a time, it's more convenient to use some of Visual InterDev's DTCs to simplify the process, as we'll see in the following section.