How To Capture the Parameters from an SQL Procedure with RDO

MORE INFORMATION

Sample Program

Step 1: Create the Stored Procedure

1. Open the SQL Server 6.5 ISQL utility and change the default database to Pubs.
2. Paste the following code in the Query window:

         create procedure test
         @result1 integer output, @result2 integer output
         as
         select @result1 = 100
         select @result2 = 200
   
   						

3. Click Execute Query or press CTRL+E to execute the Transact-SQL code.
4. 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

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add a CommandButton(Command1) to Form1.
3. 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=<username>;pwd=<strong password>"
            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
   
   						

4. Press the F5 key to start the program.
5. Click the Command1 button to execute the code and display the output parameter in the debug window.