OracleDataReader returning DBNull for a column in Stored Procedure result

113 Views Asked by At

first of all I apologize that this will likely be a broad question but I have tried everything I can think of and feel like I am losing my mind.

I am calling a stored procedure using Oracle Managed Data Access and using OracleDataReader to read the results of the procedure. When I execute the stored procedure with the same exact parameters it returns 25 rows with the column in question having a value for one of those rows. When I call it via code I get the same exact result set except for that one column having all nulls. The column type is of type Double if that helps.

Any idea's how I can further troubleshoot this?

Code calling the procedure:

Using conn As New OracleConnection(GetConnString())
    Using cmd As New OracleCommand()
        cmd.Connection = conn
        conn.Open()

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = procName
        cmd.Parameters.Add("@i_qry_id", OracleDbType.Int32, 8).Value = qryId
        cmd.Parameters.Add("@i_main_row_var_id", OracleDbType.Int32, 8).Value = mrowvarid
        cmd.Parameters.Add("@i_row_var_id", OracleDbType.Int32, 8).Value = rowVarId
        cmd.Parameters.Add("@i_sel_type", OracleDbType.Int32, 8).Value = sType
        cmd.Parameters.Add("@v_client_id", OracleDbType.NVarchar2, 20).Value = clientId
        cmd.Parameters.Add("@v_user_id", OracleDbType.NVarchar2, 100).Value = userId
        cmd.Parameters.Add("@i_page_size", OracleDbType.Int32, 8).Value = psize
        cmd.Parameters.Add("@i_page_starting_idx", OracleDbType.Int32, 8).Value = psi
        cmd.Parameters.Add("@v_sort_type", OracleDbType.NVarchar2, 40).Value = sortType
        cmd.Parameters.Add("@v_prefix", OracleDbType.NVarchar2, 10).Value = prefix
        cmd.Parameters.Add("@v_text", OracleDbType.NVarchar2, 100).Value = ""
        cmd.Parameters.Add("@v_searchField", OracleDbType.NVarchar2, 100).Value = ""
        cmd.Parameters.Add("@i_search_selected", OracleDbType.Int32, 8).Value = 0
        cmd.Parameters.Add("@i_search_selected_direction", OracleDbType.Int32, 8).Value = 0
        cmd.Parameters.Add("@c_selections", OracleDbType.RefCursor).Direction = ParameterDirection.Output
        Using odr As OracleDataReader = cmd.ExecuteReader()
            While odr.Read()
                icd10 = New PDDAIcd10
                icd10.RowNum = odr("IdNum")
                icd10.ICDNum = FixDBNull(odr("REF_ICD" & icdNumber.ToString() & "_NUM"), StrDBNull.Number)
                icd10.ICD10_CODE = FixDBNull(odr("ICD" & icdNumber.ToString() & "_CODE"), StrDBNull.Str)
                icd10.ICD10_NAME = FixDBNull(odr("ICD" & icdNumber.ToString() & "_NAME"), StrDBNull.Str)
                icd10.CHAPTER_CODE = FixDBNull(odr("CHAPTER_CODE"), StrDBNull.Str)
                icd10.GROUP_CODE = FixDBNull(odr("GROUP_CODE"), StrDBNull.Str)
                icd10.Selected = IIf(FixDBNull(odr("STATUS"), StrDBNull.Number) > 0, 1, 0)
                icd10.ICDQtrCount = FixDBNull(odr("QTR_CNT"), StrDBNull.Number, 0)
                lst.Add(icd10)
            End While
        End Using
    End Using
End Using

odr("STATUS") is always DBNull for all of the records where as in Toad the first record has a value of 1.

Thanks

0

There are 0 best solutions below