ADO.Net Not Recognizing Column in Select Statement

I have a query that executes fine in TOAD, but when bringing back the results with either an ADO.Net DataReader or DataAdapter/DataSet, it's not recognizing a column in the select statement. See the query below... the column in question has its data coming from a dynamic table or sub-select or whatever you call it. I don't see why this is a problem in .Net:

Dim sql As String = String.Format("select bu.row_id, bu.name, selectedOrgs.bu_id as selectedOrg " _
                & " FROM SIEBEL.s_prod_int_bu pb " _
                & " INNER JOIN SIEBEL.s_bu bu on pb.bu_id = bu.row_id " _
                & " LEFT OUTER JOIN (select bu_id " _
                       & " FROM SIEBEL.s_lit_bu " _
                       & " WHERE lit_id = ?) selectedOrgs on selectedOrgs.bu_id = bu.row_id " _
                & " WHERE pb.prod_int_id in ('{0}') " _
                & " ORDER BY bu.name ", String.Join("','", ids.ToArray()))

            'We also need to add a parameter
            cmd.Parameters.AddWithValue("", resourceID)

I have an OdbcCommand object "cmd" and an OdbcDataReader "dr". I'm initializing the ObcDataReader as I always do: dr = cmd.ExecuteReader(). But in the loop through the records (see below), the code blows up on retrieving the "selectedOrg" column from the SQL above (Index out of range exception):

        dr = cmd.ExecuteReader
        If dr.HasRows Then
            While dr.Read
                Dim bu As New OrgResource
                'Dim bu As New Resource
                bu.ID = IIf(IsDBNull(dr.Item("row_id")), "", dr.Item("row_id"))
                bu.Name = IIf(IsDBNull(dr.Item("name")), "", dr.Item("name"))
                bu.Selected = False
                If Not String.IsNullOrEmpty(resourceID) Then
        'BOOM: Code blows up on next line
                    bu.Selected = Not IsDBNull(dr.Item("selectedOrg"))
                End If
                lRet.Add(bu)
            End While
        End If

I don't think that this should blow up as the SQL is returning correctly in TOAD, and .Net should see this column as a normal column, right? I tried swapping the OdbcDataReader out with an OdbcDataAdapter/DataSet combo, and it blows up in the same area. In fact, in Studio's Watch window, if I do: ds.Tables(0).Columns.Count, it shows that I only have 2 columns. (In Jerry Seinfeld voice: What's the deal with that?)

Exception Details with stack trace info:

System.IndexOutOfRangeException InnerException: Nothing Message: selectedOrg Source: System.Data StackTrace: at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.Odbc.OdbcDataReader.GetOrdinal(String value) at System.Data.Odbc.OdbcDataReader.get_Item(String value) at MyNameSpace1.MyProject.MyClass.MyMethod(List`1 ids, String resourceID) in C:\Documents and Settings\my_username\My Documents\My开发者_如何学GoProject\MyClass.vb:line 1088

Thanks for your help!!

Andy

---

Edit: After stack trace added

@Andy, sure enough ... that error means that your selectedOrg column wasn't recognized as a valid column. Nothing leaps out as an obvious error in your code. But here is something to try.

Change this line ...

bu.Selected = Not IsDBNull(dr.Item("selectedOrg"))

... to this ...

bu.Selected = Not IsDBNull(dr("selectedOrg"))

That has worked for me in the past.

---

Try dropping the 'as' designater for the column selectedOrgs.bu_id. I don't think Oracle uses it, and this may cause the ODBC translator to choke.

from
'selectedOrgs.bu_id as selectedOrg'

to
'selectedOrgs.bu_id selectedOrg'

---

I ended up removing the "LEFT OUTER JOIN (select ...)" portion of the SQL statement and just executing 2 statements one after the other. I guess that this is a bug in the System.Data.Odbc namespsace. Not sure if this happens in System.Data.SqlClient or System.Data.OracleClient.

Thanks for the replies.

Andy