ADO.net the out values are null

Question

User891724343 posted

We've upgraded to try to use ADO.net with Oracle 4.0 dll. I've had to change things around(make sure cursor is in last position) but one thing is the out values seem to return null even though cursor values come back. I get a return value when I try in TOAD accessing the proc. We are using an arraylist to pass the values to Oracle instead of cmd.AddParamenter so I hope we don't have to change our whole solution. Please any help would be appreciated:

Thanks...code below JT

Dim retDs As DataSet

        Dim arrParams As ArrayList

 arrParams = GetPopulatingParametersList(objRequestId, strYearStartup)

 

Private Function GetPopulatingParametersList(ByVal objRequestId As Object, ByVal strYearStartup As String) As ArrayList

        dpDataParamArr = New ArrayList

 

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.P_REQ_ID, "INT32", objRequestId, ParameterDirection.Input))

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.P_YEAR_STARTUP, "varchar", strYearStartup, ParameterDirection.Input))

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.P_NEW_REQ_ID, "INT32", -1, ParameterDirection.Output, 1000000)) <--Returns null

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.P_OUT, "INT32", 0, ParameterDirection.Output))

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.P_MSG, "varchar", "", ParameterDirection.Output))

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.CUR_REQUEST, "cursor", "", ParameterDirection.Output))

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.CUR_ELEMENTS, "cursor", "", ParameterDirection.Output))

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.CUR_ATTRIBUTES, "cursor", "", ParameterDirection.Output))

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.CUR_DOCUMENTS, "cursor", "", ParameterDirection.Output))

        dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.CUR_VIOLATIONS, "cursor", "", ParameterDirection.Output))

 

        Return dpDataParamArr

    End Function

 retDs = GetDataSet(arrParams, STP_Constants.PKG_MANAGE_CODES, STP_Constants.GET_REQUEST_OBJ)

 intRequest_Id = Get_DataParameterValue(STP_Constants.GET_REQUEST_OBJ, STP_Constants.P_NEW_REQ_ID, arrParams) <--null value

 

Protected Function Get_DataParameterValue(ByVal strProcedureName As String, ByVal strParamName As String, ByVal arrayListOfParams As ArrayList) As Object

        Dim curParam As DataParameter

        Dim retVal As Object

 

        If arrayListOfParams.Count > 0 Then

            For itemNumber As Integer = 0 To arrayListOfParams.Count - 1

                If TypeOf arrayListOfParams(itemNumber) Is DataParameter Then

                    curParam = arrayListOfParams(itemNumber)

                    If curParam.ParameterProcedureName = strProcedureName AndAlso curParam.ParameterName = strParamName Then

                        ' retVal = IIf(IsDBNull(curParam.ParameterValue), "", curParam.ParameterValue)

                        retVal = curParam.ParameterValue

                        Exit For

                    End If

                End If

            Next

        End If

        Return retVal

    End Function

Answer 1

User617292609 posted

But, I don't see within your code that you're even trying to read output parameter values after the db call??

Answer 2

User891724343 posted

Request.item(ct.UniqueID)

 

AttachNonCursorParameters

 

 Protected Function GetDataSet(ByRef ArrayListofParameters As ArrayList, ByVal PackageName As String, _

                                ByVal StoreProcName As String) As DataSet

        Dim helper As DbHelper

        Dim idbcmd As IDbCommand

        Dim retDs As DataSet

        Try

            If ArrayListofParameters.Count > 0 Then

                helper = New DbHelper

                idbcmd = GetIDbCommandFromParameters(ArrayListofParameters, PackageName, StoreProcName)

                'idbcmd.CommandTimeout = 600 'JT 4/2012

                retDs = helper.ExecuteDataset(idbcmd)

            End If

            SynchronizeArrayListAndIdataParameters(StoreProcName, ArrayListofParameters, idbcmd.Parameters)

            Return retDs

        Catch ex As Exception

            ExceptionManager.Publish(ex)

            Throw ex

        Finally

            idbcmd.Dispose()

            retDs = Nothing

            helper = Nothing

        End Try

    End Function

 

Protected Function GetIDbCommandFromParameters(ByVal ArrayListofParameters As ArrayList, ByVal PackageName As String, ByVal StoreProcName As String) As IDbCommand

        Dim Cmd As IDbCommand

        Dim helper As DbHelper

        Dim IdbParams As IDataParameter()

        Try

            If ArrayListofParameters.Count > 0 Then

                If Not (PackageName = "" And StoreProcName = "") Then

                    IdbParams = AttachNonCursorParameters(ArrayListofParameters, StoreProcName)

                    helper = New DbHelper

                    Cmd = helper.CreateCommand(PackageName & "." & StoreProcName, CommandType.StoredProcedure, IdbParams)

                    AttachCursorParameters(Cmd, ArrayListofParameters, StoreProcName)

                End If

            End If

            Return Cmd

        Catch ex As Exception

            ExceptionManager.Publish(ex)

            Throw ex

        Finally

            Cmd = Nothing

            helper = Nothing

            IdbParams = Nothing

        End Try

    End Function

JT

Answer 3

User1400794712 posted

Hi jtingres,

dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUEST_OBJ, STP_Constants.P_NEW_REQ_ID, "INT32", -1, ParameterDirection.Output, 1000000)) <--Returns null

What do you mean 'returns null'? Do you mean DataParameter method returns null? If so, I don't find this method in your code, please post it here. If not, please give more details about this problem.

 intRequest_Id = Get_DataParameterValue(STP_Constants.GET_REQUEST_OBJ, STP_Constants.P_NEW_REQ_ID, arrParams) <--null value

Does this code return null which is not as you except? If so, please try to use breakpoint to debug this project and use F11 to run the code step by step to check which code causes this problem.

Besides, the code contains a lot of undefined methods, it's difficult to figure them out. Please post them in details or give some comments in it.

Best Regards,

Daisy