Visual InterDev

Executing Database Commands Using the Data Environment

Scripting the DE Object

To script server-based database access, you work with a special object — the DE object — that exposes an object model for executing commands and managing their results. The DE object model is an easier-to-use version of the ActiveX® Data Objects (ADO) model. For details about that model, see the Microsoft ActiveX Data Objects (ADO) Overview.

Before scripting database access, you must add a data connection to your project's data environment. When you do, Microsoft^® Visual InterDev™ adds script to the Global.asa file that creates a DE object incorporating the connection information. When you write data access script in your pages, you can reference this DE object, and Visual InterDev will automatically know how to connect to the database.

After adding a data connection, you add one or more data commands to the data connection. Each data command provides you with access to a database object, which can include a table or view, an SQL command, or a stored procedure.

Executing a Command

You can execute database commands by referencing the corresponding command object in your script.

To execute a database command

Be sure that the command you want to execute is defined as a data command in the data environment. For details, see Getting Records.
In your Web page, use server script to reference the DE object. If you have enabled the scripting object model for the page, use this script:
```
<%
   thisPage.createDE()
%>
```
If you are not using the scripting object mode, use the following script:
```
<%
Set DE = Server.CreateObject("DERuntime.DERuntime")
DE.Init(Application("DE"))
%>
```
In server script, execute the command you want to use by referencing its name, using this syntax:
```
DE.commandObjectName.
```
For example, to execute the SQL query associated with a command object called Authors, use this script:
```
<%DE.Authors%>
```
If the command takes parameters (such as a command that references a stored procedure or parameterized query), you can pass the parameters when you call it, using this syntax:
```
DE.commandObjectName (parameter1, parameter2, [...]).
```
You can pass parameters as variables, literals (character strings should be passed using the correct quotation marks for the language you are scripting in), or any other valid expression.
For example, to execute a stored procedure that takes two parameters, reference its corresponding command object with script such as this:
```
<%DE.updateAuthors ("A101", txtAuthorLname, 100.00, "London")%>
```
If the command returns a value, you can assign it to a variable when you execute the command, using script like this:
```
<%iRetValue = DE.updateAuthors ("A101", txtLname, 100.00)%>
```

Working with Result Sets

All commands return a result set, although in some cases (as with update queries) the result set is empty. But if your command returns a useful result set, you can navigate in it and extract its contents to display on a page.

To provide access to the result set, the DE object dynamically creates a result set object based on the command object, named using this syntax:

DE.rscommandObjectName.

You can set a variable to this object, and then reference it in your scripts.

When a result set is generated, it includes a pointer indicating which record is the current record. Any operations that you perform, such as displaying data, navigating, or updating, are always performed with respect to the current record. To work with a different record, you must first navigate to that record.

The procedures outlined below allow you to work with a result set that is used entirely on one page. For example, you can use the procedures here to create a page that lists all the records from a result set in a table in one page.

To extract the contents of a result set

After creating the result set, set a variable to point to the DE result set object. The DE result set object is named after your command object, but has the "rs" prefix. For example, the following two statements create the result set, and then set a variable to point to it:
```
<%
DE.Authors
Set rs = DE.rsAuthors
%>
```
Extract individual values from the Fields collection of the result set object, using syntax such as the following:
```
<%
DE.Authors
Set rs = DE.rsAuthors
lname = rs.fields.getValue("au_lname")
%>
```

To move to a different record, you use navigation methods on the recordset.

To navigate in a result set

Call the moveNext, movePrevious, moveFirst, or moveLast methods of the result set object.
To determine whether you are at the end or beginning of the result set, test the EOF or BOF properties.

The following script shows a complete example of how to script the DE object to get information from an Authors table. The script opens a recordset based on a data command object called "Authors." It then navigates from the beginning to the end of the result set, displaying for each record the contents of the au_lname field.

<%
Set DE = Server.CreateObject("DERuntime.DERuntime")
DE.Init(Application("DE"))
DE.Authors
set rs = DE.rsAuthors
rs.moveFirst
Do While Not rs.EOF
%>
   Next name = <%=rs.fields.getValue("au_lname")%><br>
   <%
   rs.moveNext
Loop
%>

For more information about navigation methods, see the Microsoft ActiveX Data Objects (ADO) Overview.

Paging Records

If you want to display a single record from the result set on a page and then provide navigation controls to page between records, the situation is more complex, as described in Data Binding.

If that is your goal, you will find it more convenient to use a combination of a Recordset control, data-bound controls, and a RecordsetNavbar control.

For details, see Displaying Records and Design-Time Controls.

For additional information about designing paging, see Displaying Information in Sequential Pages with Visual InterDev.