Announcement

Collapse
No announcement yet.

ADO Command.Execute and Stored Procedures

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Knuth Konrad
    replied
    I have not used ADO with PB, but this ...

    Code:
    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.

    Leave a comment:


  • Trevor Lane
    replied
    What does the text of the stored procedure look like?

    What is the contents of the listbox after execution?

    Leave a comment:


  • Andy Flowers
    started a topic ADO Command.Execute and Stored Procedures

    ADO Command.Execute and Stored Procedures

    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?
Working...
X