How To Get Records and Return Value from Stored Procedure Using DTCs

MORE INFORMATION

NOTE: This article uses the SQL Server database of Pubs. It assumes that you have used the ODBC icon in the Control Panel to create a valid DSN to Pubs. It also assumes that a project has been created and is open in Visual InterDev.

Adding the Data Connection

1. Right-click the Global.asa and select Add Data Connection.
2. Find the name of the DSN created in Control Panel. Depending on what type, it may be under the File tab or Machine tab.
3. Highlight the DSN, and click OK.
4. When the next box comes up, rename the connection from Connection1 to Pubs.

Creating the Stored Procedure

NOTE: If you have the Professional version of Visual InterDev, you will not be able to complete this step within Visual InterDev. SQL Server Enterprise Manager can be used to create this stored procedure.

You will need a stored procedure that selects records and has the keyword Return coded at the end.

1. In the Data View tab, expand the Pubs table, expand the Stored Procedures section.
2. Double-click the byroyalty stored procedure. When it opens in the editor, Select All from the Edit menu to highlight all the code, then select Copy.
3. Close this stored procedure out of the editor.
4. Right-click the Stored Procedure heading in the Data View tab, and select New Stored Procedure.
5. Visual InterDev will create a new page with the following text: Create Procedure StoredProcedure2

         /*
            (
               @parameter1 datatype = default value,
             @parameter2 datatype OUTPUT
          )
         */ 
         As
           /* set nocount on */ 
          return
   
      Paste the recently copied text at the beginning this newly created file.
      Immediately following the pasted text add the command RETURN.
   						

6. Delete the text that Visual InterDev automatically created when you created the new stored procedure.
7. Change the first line of the stored procedure from "byroyalty" to "byroywret."
8. Close the new stored procedure. When prompted to save changes, click Yes.

You now have a new stored procedure that will find all authors that receive a certain royalty percentage on their books, and the stored procedure returns a return value.

Your stored procedure should appear as follows:

   CREATE PROCEDURE byroywret @percentage int
   AS
   select au_id from titleauthor
   where titleauthor.royaltyper = @percentage
				

   return
				

Calling the Stored Procedure

1. Right-click the project name in Project Explorer. From the Add menu, select Active Server Page. Click Open. A new ASP page will open in the editor.
2. Under the <BODY> tag in the page, drag the Recordset DTC from the Design-Time Controls toolbox onto the newly created ASP Page.
3. Right-click the Recordset DTC, and select Properties. a. Set the Connection to the name you gave your data connection to pubs, which you added to your Global.asa. b. In the Source of Data option group area, select Database Object. Select Stored Procedures in the list. c. In the Object Name list, select the name of the newly created stored procedure from the previous step. d. Select the Implementation tab. Deselect the Automatically open the recordset check box. NOTE: This sample will pass the recordset a parameter from a textbox. It should not open the recordset until the user enters a value in a textbox and selects submit. This is why the option to automatically open the recordset needs to be cleared.
4. Drag a Grid from the Design-Time Controls toolbox to the page under the Recordset.
5. Right-click the Grid, and select the Properties option. a. In the Data tab, use the list under the Recordset property to set it to the recordset on the page (Recordset1). b. In the Available Fields area, select the field au_id. Click OK.
6. Drag a Label DTC from the Design-Time Controls toolbox to the page, under the Grid DTC.
7. Right-click the Label, and select Properties. In the Label/Expressional property, fill in the following text: Please enter a valid royalty percentage (50 returns four records): Select OK when complete. NOTE: Do not bind a recordset to this label.
8. Drag a Textbox DTC from the Design-Time Controls toolbox to the page under the Label DTC.
9. Drag a Button DTC from the Design-Time Controls toolbox to the page under the Textbox DTC.
10. Before the </HEAD> tag at the top of the page, insert a blank lines between the <META> and </HEAD> tags.
11. With the cursor positioned on the newly inserted blank line between the <META> and </HEAD> tags select the Script Outline tab. If this tab is not present, from the View menu, select Other Windows and click Script Outline.
12. Expand the Server Objects & Events, and expand the Button1. Double- click the onclick event shown.
13. A sub or function stub will appear in your editor (depending on the default scripting language specified for your page). If your default scripting language is JavaScript (ECMAScript) inside the newly created sub or function, add the following code:

          function Button1_onclick() {
    
          // Hide the button, textbox, and label
    
             Button1.hide();
             Textbox1.hide();
             Label1.hide();
    
          // give the recordset the data from the textbox for use in
          // its parameter. Stored procs are numbered starting at 1 if they
          // have a RETURN value, as the RETURN value is in index 0.
    
             Recordset1.setParameter(1,Textbox1.value);
             Recordset1.open();
    
          // grab the return value out of index 0
    
             var intRetVal = Recordset1.getParameter(0)
    
             Response.Write("The return value is ");
             Response.Write(intRetVal);
          }
    
       If your default scripting language is VBScript inside the newly created
       sub or function, add the following code:
    
          Sub Button1_onclick()
    
          ' hide the button, textbox, and label
    
          Button1.hide()
          Textbox1.hide()
          Label1.hide()
    
          'Give the recordset the data from the textbox for use in
          'its parameter. Stored procs are numbered starting at 1 if they
          'have a RETURN value, as the RETURN value is in index 0.
    
          Recordset1.setParameter 1, Textbox1.value
          Recordset1.open()
    
          'grab the return value out of index 0
    
          intRetVal = Recordset1.getParameter(0)
    
          Response.Write("The return value is ")
          Response.Write(intRetVal)
          End Sub
    						

14. Right-click in the page and select View in Browser. Answer Yes to the prompt to save the changes.

The user will enter a percentage into the textbox and then select the button, the value from the textbox will be passed to the Recordset DTC, where it will populate the Grid with the resulting records. The return value will also appear.