Calling Stored Procedures from Active Server Pages | Programming Microsoft Visual InterDev 6.0

You can execute a stored procedure from within your Active Server Pages (ASP) code in your Visual InterDev web projects. The two main techniques for doing so are as follows:

Via data command objects and scripting the Data Environment

Via Recordset objects and literals, variables, object references, or expressions

We'll now take a look at both of these techniques. The first technique gives you more control over the stored procedure call since the code is written manually with the exception of the data command object that contains the stored procedure. The second technique provides a fast way to call stored procedures but is sometimes harder to code for more advanced stored procedures calls that involve a lot of input and output parameters and that might or might not return a resultset. The authors preference is for the first technique: scripting the Data Environment.

Calling Stored Procedures via Data Command Objects and Data Environment Scripting

In this section, we'll continue with the example stored procedure named spAddCompany and see how to call it from within ASP code. You'll remember that this stored procedure takes a number of input parameters and performs a SQL INSERT into the Company table.

First, we'll create an HTML file named AddCompanyScript.htm. This file, and the rest of this example, is included on the CD-ROM under the VI6Samples folder. The htm file is used to capture user input for the Company information. It consists of a simple HTML form, as shown in Figure 15-11.

Second, we need to create a data command object that can reference the spAddCompany stored procedure. You can create this object as follows:

In the Project Explorer, right-click the data connection and choose Add Data Command.

Enter AddCo for the Command Name, and set the connection to VI6Samplesconn (or whatever connection name you have created).

For the source of the data, choose Stored Procedure from the Database Object drop-down list and then select the dbo.spAddCompany stored procedure from the Object Name drop-down list.

click to view at full size.

Figure 15-11. An HTML form for capturing the user input for the spAddCompany stored procedure.

Figure 15-12 shows the Properties dialog box for the data command object we have created.

Figure 15-12. The Properties dialog for the data command object showing stored procedure named spAddCompany as the data source.

Within the Properties dialog box for the data command object that we've created, you'll also notice a Parameters tab. Here you can inspect the list of parameters that the stored procedure is expecting. Figure 15-13 below shows the Parameters tab. On this tab, you can inspect the names of the parameters and their associated direction (that is, input, output, or input/output), data type, and size.

Figure 15-13. The Properties dialog box for the data command object showing the parameters required by the stored procedure.

After having created the HTML file to capture user input and the data command object to reference the stored procedure, next you create an ASP Web page that can call the data command object and pass it the user-supplied input values. The code below does just this. As you can see, there are no visual controls on the page at all—just Data Environment scripting.

 <%@ 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"> </HEAD> <BODY> <% ' Initialize parameters a = Request.Form("company_name") b = Request.Form("address1") c = Request.Form("address2") d = Request.Form("city") e = Request.Form("state") f = Request.Form("zip") g = Request.Form("phone") h = Request.Form("fax") error_msg = "" ' Call the stored procedure thisPage.createDE() rtnVal = DE.AddCo (a, b, c, d, e, f, g, h, error_msg) ' Display the return status If rtnVal = 0 Then     Response.Write "Company information sucessfully captured." Else     Response.Write "Error: " + CStr(rtnVal) + "<BR>"     Response.Write "Description: " + error_msg + "<P>"     Response.Write "Please try again." End If %> </BODY> <% ' VI 6.0 Scripting Object Model Enabled %> <% EndPageProcessing() %> </FORM> </HTML>

There are basically three steps to follow in the AddCompanyScript.asp page. First, the parameters to be passed to the stored procedure are collected from the Request object and stored into variables. Second, the stored procedure is executed using the following syntax:

 thisPage.createDE() rtnVal = DE.AddCo (a, b, c, d, e, f, g, h, error_msg)

Finally, the results of the procedure call are displayed on the screen. If the return value is 0, the procedure executed successfully and the new record was inserted into the Company table. If the return value was -100 or any other value other than 0, an error message is printed on the screen. Notice that the error_msg variable is an input/output parameter, so it is assigned a new value should the stored procedure encounter an error condition.

As you can see from this example, using scripting in the Data Environment makes it easy to work with stored procedures. Only a couple of lines of code are needed, and you have access to all the necessary output parameters and return values you need to track the completion status of the stored procedure execution.

Calling Stored Procedures Using Recordset Objects

In this section, we'll use the example stored procedure named spCompanybyCity and see how to call it from ASP code using a Recordset object. You'll remember that this stored procedure takes a single input parameter (the name of a city) and performs a SQL SELECT statement against the Company table.

First create an ASP file named CompanybyCity.asp. This file is included on the CD-ROM under the VI6Samples folder. The next step is to place a Recordset object onto the ASP Web page. You can do this by dragging a Recordset design-time control (DTC) from the Toolbox. Next, name the Recordset DTC rsCompanybyCity. Display the Recordset Properties dialog box, choose the General tab, and then choose Stored Procedures from the Database Object drop-down list. Choose spCompanybyCity from the Object Name drop-down list. This is the name of the stored procedure we want to call from the Recordset object.

Since a Recordset DTC executes its query when the ASP Web page is first loaded, we'll need to specify the value of any parameters early on. Typically, we cannot gather or evaluate the value of the parameter after the page has been displayed. You can therefore specify the value of the parameter in one of these two events prior to the opening of the Recordset object:

The onenter event for the page

The onbeforeopen event for the Recordset object

In this example, however, we'll use another technique. We'll uncheck the Automatically Open The Recordset check box. This check box is found on the Implementation tab of the Recordset Properties dialog box. By unchecking this option, you can now specify your parameters and then programmatically open the Recordset object after the parameters have been defined. The following code shows an example:

 <% rsCompanybyCity.setParameter 1, "Dallas" rsCompanybyCity.open %>

Note
You can pass parameters to Recordset objects in several ways. These include specifying the following types of values in the Parameters tab of the Recordset Properties dialog box:

Literals Character values in single quotes and numeric values without quotes.

Variables Names of variables defined in server code that contain the value you want to pass.

Object References References to objects such as the Request object or a Textbox DTC and their associated property values.

Expressions Any combination of literals, variables, and object references. The expression is evaluated as a Microsoft JScript expression, so be sure to use single quotes for character literals and the plus sign (+) for concatenation.

The code below shows the full code for the CompanybyCity.asp page minus the code that is generated by the Recordset DTC and the Grid DTC. You'll notice that there is very little code that needed to be manually scripted.

 <%@ 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=VI60_DTCScriptingPlatform content="Server (ASP)"> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> </HEAD> <BODY> <H2>Company List</H2> <HR> <!--METADATA TYPE="DesignerControl" startspan … <Recordset design-time control> … <!--METADATA TYPE="DesignerControl" endspan--> <% rsCompanybyCity.setParameter 1, "Dallas" rsCompanybyCity.open %> <!--METADATA TYPE="DesignerControl" startspan … <Grid design-time control> … <!--METADATA TYPE="DesignerControl" endspan--> </BODY> <% ' VI 6.0 Scripting Object Model Enabled %> <% EndPageProcessing() %> </FORM> </HTML>

Notice also that a Grid DTC has been used to display the output from the Recordset DTC. Figure 15-14 below shows the output as it appears in the browser window.

click to view at full size.

Figure 15-14. Output from the CompanybyCity.asp page. This page uses a Recordset DTC to call a stored procedure named spCompanybyCity. The results are displayed in a Grid DTC.

Using either the Data Environment scripting technique or the Recordset object technique allows you to work with stored procedures easily. By trying out both techniques, you'll be able to find out which one works best for you and gives you the right level of programmatic control versus speed of development that you need. As mentioned earlier, the authors' preference is for scripting the Data Environment. This technique seems to provide the most dependable results no matter the nature of the stored procedure. We found the Recordset object technique provided unusual results when we were attempting to work with stored procedures that did not return a recordset.

Overall, using stored procedures is an excellent way for both improving the performance of your Visual InterDev applications and for separating the business logic from the presentation layer within your applications. Performing this separation will allow you to write code that can be easily maintained in the future by other developers. One drawback to stored procedures, however, is that they are proprietary. A stored procedure written for a SQL Server database will require a few changes before you can place it into an Oracle database.