|
|
可以作为 vi6 hands-on 练习，做完后就会了：
RECORDSET DTC
向 SP 传参数
======================================================================
---------------------------------------------------------------------
The information in this article applies to:
- Microsoft Visual InterDev, version 6.0
---------------------------------------------------------------------
SUMMARY
=======
If you have a stored procedure that returns a recordset and has a RETURN
coded in it that you want to also capture, you can use the recordset Design-
time control (DTC) to connect to the procedure, and the Grid DTC to display
the data.
The sample described in this article will allow a user to enter a
percentage (integer) into a textbox and click a button. The value from the
textbox will be passed to the Recordset DTC, where it will populate a Grid
with the resulting records. The return value will also be displayed.
However, you would most likely use the value to determine a course of
action and not display it.
This sample can be done using either server-side Active Server Pages (ASP)
Javascript or VBScript.
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.
Additional query words:
======================================================================
Keywords : kbDSIASPGrp kbVisID600
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbhowto
=============================================================================
Copyright Microsoft Corporation 1998.
|
|
