Microsoft KB Archive/159657

= How To Capture the Parameters from an SQL Procedure with RDO =

Article ID: 159657

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q159657



SUMMARY
This article demonstrates how to capture the output parameters from an SQL procedure.

An rdoParameter object represents a parameter associated with an rdoPreparedStatement object. You use the Direction property of the rdoParameter object to determine whether the parameter is used for input, output, both, or as a return value from the stored procedure.



Step 1: Create the Stored Procedure
 Open the SQL Server 6.5 ISQL utility and change the default database to Pubs.  Paste the following code in the Query window: create procedure test @result1 integer output, @result2 integer output as     select @result1 = 100 select @result2 = 200

 Click Execute Query or press CTRL+E to execute the Transact-SQL code. The following confirmation message appears:

"This command did not return data, and it did not return any rows"

This indicates that the Transact-SQL code created the stored procedure correctly.

Step 2: Create the Visual Basic Code
 Start a new project in Visual Basic. Form1 is created by default. Add a CommandButton(Command1) to Form1.</li>  Paste the following code into the General Declarations section of Form1: Private Sub Command1_Click Dim rdoEnv As rdoEnvironment Dim rdoConn As rdoConnection Dim strConn As String Dim ps As rdoPreparedStatement

Set rdoEnv = rdoEngine.rdoEnvironments(0) rdoEnv.CursorDriver = rdUseOdbc '***You need to change the SERVER, UID, and PWD parameters here. strConn = "driver={SQL Server};server=myserver;" & _ "database=pubs;uid= ;pwd= " Set rdoConn = rdoEnv.OpenConnection( _          dsName:="", _           Prompt:=rdDriverNoPrompt, _           ReadOnly:=False, _           Connect:=strConn)

Set ps = rdoConn.CreatePreparedStatement _ ("", "{Call test(?,?)}") ps.Type = 2 ps.rdoParameters(0).Direction = rdParamOutput ps.rdoParameters(1).Direction = rdParamOutput ps.Execute Debug.Print ps.rdoParameters(0).Value, ps.rdoParameters(1).Value rdoConn.Close rdoEnv.Close End Sub

</li> Press the F5 key to start the program.</li> Click the Command1 button to execute the code and display the output parameter in the debug window.</li></ol>

Additional query words: kbVBp400 kbVBp600 kbdse kbDSupport kbVBp kbRDO

Keywords: kbhowto KB159657

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.