none
ODBC Error Using InputOutput Parameter with Oracle Stored Procedure RRS feed

  • Question

  • I have an Oracle stored procedure that works when I run it from a VB6 application.  However, it fails to work when I run it from an VB.NET 3.5 windows service application.  Both of the projects reference ADODB 2.8.

    Experimenting with the stored procedure and code that calls it lead me to believe that something about the VB.NET application takes issue with the parameter direction of InputOutput.

        Set cmdProc = New ADODB.Command
        Set cmdProc.ActiveConnection = adoDashboard
        StatusBar = "Translating workflow " & lWorkflow
        With cmdProc
            .CommandText = "DBPKA.TestFunction"
            .CommandType = adCmdStoredProc
            .CommandTimeout = 5000
            .Parameters.Append .CreateParameter("l_WORKFLOW", adInteger, adParamInput, , lWorkflow)
            .Parameters.Append .CreateParameter("n_DATATYPE", adSmallInt, adParamInput, , 1)
            .Parameters.Append .CreateParameter("l_SOURCE", adInteger, adParamInput, , 0)
            .Parameters.Append .CreateParameter("l_MAX_INSTS", adInteger, adParamInput, , 12)
            .Parameters.Append .CreateParameter("str_RESULT", adVarChar, adParamInputOutput, 2000, vbNullString)
        End With
        cmdProc.Execute lTotalUpdates
        sResult = cmdProc.Parameters("str_RESULT").Value

    Did something change in the VB.NET interop libraries that disallows a parameter to output a value?
    Thursday, December 11, 2008 6:04 PM

All replies

  • Hi Paul,

    Do you get any errors when compiling the application? Do you get an runtime error? The syntax used in your code sample is not correct for VB.NET. I guess your already declared these import statements to use ADODB constants:

    Imports ADODB.DataTypeEnum
    Imports ADODB.CommandTypeEnum
    Imports ADODB.ParameterDirectionEnum


    You need additional parentheses in the lines appending the parameters and executing the procedure:


    .Parameters.Append(.CreateParameter("l_WORKFLOW", adInteger, adParamInput, , lWorkflow))
    ...
    cmdProc.Execute(lTotalUpdates)

    Finally specifying vbNullString for the last parameter value does not work, use an empty string instead (or make the parameter an output parameter if the string is always empty):

    .Parameters.Append(.CreateParameter("str_RESULT", adVarChar, adParamInputOutput, 2000, ""))

    Hope this helps!
    Ralf


     

    Friday, December 12, 2008 9:57 AM