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

MORE INFORMATION

Before You Begin

Create the Stored Procedure

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.

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")
   %>
   					

<%@ 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>