VB Forum / Database Access / July 2007

Dear Group,

I would like to read the input and output parameters of a stored
procedure within the Connection.ExecuteComplete event handler.
Unfortunately, I have found that if I do this while using
Command.Execute, the Command.Execute returns nothing rather than the
expected recordset! I have found that if I comment out the reading of
the parameters collection, the correct recordset is returned.
Furthermore, if I leave the parameter reading lines commented out and
instead look at the parameters in the immediate window or using the
locals window, command.execute will return Nothing. Any help would be
appreciated.

EXAMPLE OUTPUT

Without Printing Dealer
recordset value ado is confusing

With Printing Dealer
Dealer 500
recordset is nothing

EXAMPLE STORED PROCEDURE (put in any sql server database, does not use
any tables)

create PROCEDURE [dbo].[pr_test4]
    @ListDate datetime,
    @DealerID int,
    @outputID int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    select @outputID = @DealerID + 1
    select ('ado is confusing')
END

EXAMPLE CLASS MODULE  that watches for events, must set server strings
to match:

Public WithEvents con As ADODB.Connection
Public PrintDealer As Boolean

Public Sub init()
   Set con = New ADODB.Connection
   con.Open "Provider=SQLOLEDB.1;Data Source=xxx;Initial
Catalog=xxx;Integrated Security=SSPI;"
   con.CursorLocation = adUseClient
End Sub

Private Sub con_ExecuteComplete(ByVal RecordsAffected As Long, ByVal
pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal
pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal
pConnection As ADODB.Connection)
   If PrintDealer Then
       Debug.Print "Dealer " & pCommand.Parameters("@DealerID").Value
   End If
End Sub

Public Sub Test(cmd As ADODB.Command)
   Dim rs As ADODB.Recordset
   Set rs = cmd.Execute
   If rs Is Nothing Then
       Debug.Print "recordset is nothing"
   Else
       rs.MoveFirst
       Debug.Print "recordset value " & rs.Fields(0).Value
   End If
End Sub

EXAMPLE CODE MODULE to call everything
Sub TestSub()
   Dim o As New SimpleTest
   o.init
   Dim cmd As ADODB.Command

Debug.Print "Without Printing Dealer"
   pr_test4 cmd, o.con, #4/1/2009#, 500
   o.Test cmd
   '   o.doExecuteRS cmd
Debug.Print ""
Debug.Print "With Printing Dealer"
   o.PrintDealer = True
   pr_test4 cmd, o.con, #4/1/2009#, 500
   o.Test cmd

End Sub

Public Function pr_test4(cmd As ADODB.Command, con As
ADODB.Connection, _
ByVal ListDate As Variant, ByVal DealerID) As Boolean
   Set cmd = New ADODB.Command
   Set cmd.ActiveConnection = con
   cmd.CommandText = "pr_test4"
   cmd.CommandType = adCmdStoredProc
   cmd.Parameters.Append cmd.CreateParameter("@ListDate",
adDBTimeStamp, adParamInput, 0, ListDate)
   cmd.Parameters.Append cmd.CreateParameter("@DealerID", adInteger,
adParamInput, 0, DealerID)
   cmd.Parameters.Append cmd.CreateParameter("@OutputID", adInteger,
adParamOutput)
   pr_test4 = True
End Function