Microsoft KB Archive/192667

= HOWTO: Using the Data Environment Object to Get Records and Return Value from a Stored Procedure =

Article ID: 192667

Article Last Modified on 11/14/2001

-

APPLIES TO


 * Microsoft Visual InterDev 6.0 Standard Edition

-



This article was previously published under Q192667



SUMMARY
The Visual InterDev 6.0 Data Environment can be used as a wrapper around Microsoft ActiveX(r) Data Objects (ADO) to access data. When you create a data command in a Visual InterDev 6.0 project, you can access that command by using the Recordset Design-Time Control, and also by script through the use of the DERuntime.DERuntime object.

This article provides a code sample that shows how you can use the Data Environment object to access recordsets and values that are returned by a stored procedure in script. The sample Active Server Page (ASP) code that is provided in this article is Microsoft Visual Basic Script (VBScript).

NOTE: This article uses the Microsoft SQL Server database Pubs. It assumes that you have access to a SQL Server Pubs database.



Before You Begin
Before you begin, you must create a data connection to your SQL Server Pubs database.

Create the Stored Procedure
NOTE: If you have the Professional version of Visual InterDev, you cannot complete this step within Visual InterDev. You can use SQL Server Enterprise Manager to create this stored procedure.

For this sample, you will create a stored procedure that returns both a return value and a resultset.

 On the Data View tab, expand the Pubs connection. Right-click the Stored Procedures folder and select New Stored Procedure.  Place the following code in the new stored procedure: Create PROCEDURE GetAuthors (       @lname varchar(40),        @state char(2)    ) AS

SELECT * FROM Authors WHERE au_lname LIKE '%' + @lname + '%' AND state = @state RETURN @@ROWCOUNT  Close the stored procedure and save your changes.

You now have a new stored procedure named GetAuthors that will return records from the Authors Table through a wildcard search on author last name and state. It will return the number of rows that are returned from the query as the RETURN_VALUE.

Create a Data Command

 * 1) In the Project Explorer, right-click the connection to the Pubs database, and then select Add Data Command.
 * 2) Set the Command Name to AuthorSearch.
 * 3) Set the Database Object to Stored Procedure and the Object Name to GetAuthors.
 * 4) Click OK. You have now created your data command.

Access the Data Environment from Script
 Add an ASP page to your project. You will be able to use the Data Environment object whether you enable the scripting object model or not.  Add the ASP page script to create the Data Environment object. If you have a scripting object model-enabled page, then you could use the following code: <% thisPage.createDE %> If your page does not have the scripting object model enabled, then you would use the following code (this would also work on a page with the scripting object model enabled): <% Set DE = Server.CreateObject("DERuntime.DERuntime") DE.Init(Application("DE")) %>                     Execute the AuthorSearch data command. Because your stored procedure takes two input parameters (last name wildcard and state), you will pass those two parameters when you execute the data command. For the sample, you will simply pass "gr" for the last name wildcard and "CA" for the state value. The stored procedure will return all authors from the state of California with "gr" in their last name. The code to do this is simple: <% DE.AuthorSearch "gr", "CA" %>                   </li>  Once the data command has been executed, you can access the returned resultset by using the Data Environment's Recordsets collection as shown here: <% Set objRS = DE.Recordsets("AuthorSearch") %> You could also append "rs" to the data command name to access the returned result set as in: <% Set objRS = DE.rsAuthorSearch %>                   </li>  To access the returned values, you must access the Parameters collection of the Data Command's ADO command object. To do this, use the data environment's Commands collection as shown here: <% Set objCMD = DE.Commands("AuthorSearch") RowCount = objCMD.Parameters("RETURN_VALUE") %>                   </li></ol>

Here is an ASP page that combines all of these steps: <%@ Language=VBScript %> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> </HEAD> <BODY> <%

'Create the Data Environment object Set DE = Server.CreateObject("DERuntime.DERuntime") DE.Init(Application("DE"))

'Run the command DE.AuthorSearch "gr", "CA"

'Access the returned value and display it Set objCMD = DE.Commands("AuthorSearch") RowCount = objCMD.Parameters("RETURN_VALUE")

Response.Write "<H3>" & RowCount & " records returned</H3>"

'Access the resultset and write the Recordset out in an HTML table Set objRS = DE.Recordsets("AuthorSearch")

Response.Write " " %> </BODY> </HTML>

<div class="references_section">