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


The information in this article applies to:

  • Microsoft Visual InterDev 6.0
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.

MORE INFORMATION

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.

  1. On the Data View tab, expand the Pubs connection. Right-click the Stored Procedures folder and select New Stored Procedure.
  2. 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
  3. 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

  1. 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.
  2. 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"))
%>
  3. 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"
%>
  4. 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
%>
  5. 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")
%>
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 "<table border=1 cellpadding=4 cellspacing=4>"
Response.Write "<tr>"

For Each fld In objRS.Fields
  Response.Write "<td><b>" & fld.Name & "</b></td>"
Next

Response.Write "</tr>"

Do While Not objRS.EOF
  Response.Write "<tr>"

  For Each fld In objRS.Fields
    Response.Write "<td>" & fld.Value & "</td>"
  Next
  
  Response.Write "</tr>"

  objRS.MoveNext
Loop

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

REFERENCES

For additional information on using the data command against a stored procedure that returns only a RETURN value but no recordset, click the article number below to view the article in the Microsoft Knowledge Base:

190762 PRB: Cannot Access a Stored Procedure's Return Value from DTC

Modification Type:MajorLast Reviewed:11/14/2001
Keywords:kbDatabase kbDSupport kbhowto kbStoredProc KB192667
©2002 Microsoft Corporation. All rights reserved.