ADO Command.Execute and Stored Procedures

[Andy Flowers]

In my program I am calling a SQL Stored Procedure passing it three parameters to get the result of the stored procedure. The Stored procedures runs with no problem, but I am not geting any result. Here is a portion of my code:

TRY
' Open the connection
AddToListBox "Opening Connection " & VARIANT$(ConStr)
pConnection.OPEN UCODE$(VARIANT$(ConStr))
AddToListBox "Connection Opened.."
' Set the active connection
pCommand.putref_ActiveConnection = pConnection
SqlStr = UCODE$("Get_FBSItems")
pCommand.CommandText = SqlStr
pCommand.CommandType = %adCmdStoredProc
pParams = pCommand.Parameters
pParam = pCommand.CreateParameter(UCODE$("@p_BatchID"), %adBigInt, %adParamInput)
pParam.Value = "15081210162240"
pParams.APPEND pParam
pParam = pCommand.CreateParameter(UCODE$("@p_Result"), %adVarChar, %adParamOutput)
pParam.SIZE = 25
pParams.APPEND pParam


pRecordSet = pCommand.Execute()
AddToListBox "Executing Command.."
pParams = pCommand.Parameters

DO
'WHILE NOT AT the END OF the recordset...
IF pRecordset.EOF THEN EXIT DO
vRes = pRecordset.Collect("ItemID")
AddToListBox VARIANT$(vRes)
pRecordset.MoveNext
LOOP
PCount = pParams.COUNT
AddToListBox "Param Count = " & FORMAT$(PCount)
IF pCount > 0 THEN
FOR i = 1 TO pCount
pParam = pParams.ITEM(i-1)
ParamName = "Param Name = " & ACODE$(pParam.NAME)
AddToListBox ParamName
AddToListBox "Data Type = " & FORMAT$(pParam.TYPE)
AddToListBox "Direction = " & FORMAT$(pParam.Direction)
vRes = pParam.Value()
AddToListBox "Value = " & FORMAT$(VARIANT#(vRes)) & " - " & VARIANT$(vRes)
NEXT
END IF

CATCH
AddToListBox "Error processing SQL Stored Procedure..."
FUNCTION = 0
ProgStat = "Failed..."
FINALLY

' Close and release the recordset
IF pRecordset.STATE = %adStateOpen THEN
pRecordset.CLOSE
END IF
pRecordset = NOTHING
PCount = pParams.COUNT
AddToListBox "Param Count = " & FORMAT$(PCount)
IF pCount > 0 THEN
FOR i = 1 TO pCount
pParam = pParams.ITEM(i-1)
ParamName = "Param Name = " & ACODE$(pParam.NAME)
AddToListBox ParamName
AddToListBox "Data Type = " & FORMAT$(pParam.TYPE)
AddToListBox "Direction = " & FORMAT$(pParam.Direction)
vRes = pParam.Value()
AddToListBox "Value = " & FORMAT$(VARIANT#(vRes)) & " - " & VARIANT$(vRes)
NEXT
END IF

' Close the connection
IF pConnection.State = %adStateOpen THEN
pConnection.Close
END IF
END TRY

Any suggestions as to why I am not getting any results returned?

[Trevor Lane]

What does the text of the stored procedure look like?

What is the contents of the listbox after execution?

[Knuth Konrad]

I have not used ADO with PB, but this ...

pParam = pCommand.CreateParameter(UCODE$("@p_BatchID"), %adBigInt, %adParamInput)
pParam.Value = "15081210162240"

... looks strange. Assigning a string to a parameter that's supposed to be a BIGINT.