HOW TO Avoid: "Cannot create a child list for field …." for SQL Re

From: Burton G. Wilkins (Wilkins_at_discussions.microsoft.com)
Date: 07/23/04 

Date: Thu, 22 Jul 2004 19:41:02 -0700

Dear ADO.Net community:

As an object, I am trying to develop some ADO.Net code which would process a relational SLQ query on a variety of SQL platforms. The code I have succeeds with Microsoft SQL and Microsoft Access, but then fails for Oracle and IBM DB2. My question is, what I am I missing that would ensure performance for all platforms, or what modificatioins would be necessary to get it running on Oracle? The error I am getting from Oracle and DB2 are the same “Cannot create a child list for field …..” , so I suspect if I can solve one, then both will work. Please advise what’s missing.
 
For example, the following two approaches fail in Oracle when I try and run it through ADO.Net:
 
SELECT
        A.EMPNO,
        A.ENAME,
        B.DNAME
FROM
        (SCOTT.EMP A
        INNER JOIN SCOTT.DEPT B ON
        B.DEPTNO = A.DEPTNO)
 
.. or
 
SELECT
        A.EMPNO,
        A.ENAME,
        B.DNAME
FROM
        (SCOTT.EMP A
        INNER JOIN SCOTT.DEPT B ON
        B.DEPTNO = A.DEPTNO)
 
Here in general is how I am trying to return the result.
 
(1) Creates a Connection:
 
Dim mconConnection As OracleClient.OracleConnection
Dim mstrConnecion = “Data Source=WORD;User ID=SCOTT;Password=TIGER;”
 
   mconConnection = New OracleClient.OracleConnection()
   mconConnection.ConnectionString = mstrConnection
 
(2) Create a Data Adapter:
 
Dim mdaDataAdapter As OracleClient.OracleDataAdapter
Dim mstrSelect As String
 
   mstrSelect = “strSelect = "SELECT A.EMPNO, A.ENAME, B.DNAME FROM (SCOTT.EMP A INNER JOIN SCOTT.DEPT B ON B.DEPTNO = A.DEPTNO)
   mdaDataAdapter = New OracleClient.OracleDataAdapter()
   mdaDataAdapter.SelectCommand = New
   OracleClient.OracleCommand(mstrSelect, mconConnection)
 
(3) Create a Data Table:
 
   mdtDataTable = New DataTable("(SCOTT.EMP")
 
   mdcA_EMPNO = New DataColumn()
   mdcA_EMPNO.ColumnName = "A.EMPNO"
   mdcA_EMPNO.DataType = System.Type.GetType("System.Decimal")
   mdcA_EMPNO.AutoIncrement = False
   mdcA_EMPNO.Caption = "A.EMPNO”
   mdcA_EMPNO.ReadOnly = False
   mdcA_EMPNO.Unique = False
   mdtDataTable.Columns.Add(mdcA_EMPNO)
 
   mdcA_ENAME = New DataColumn()
   mdcA_ENAME.ColumnName = "A.ENAME"
   mdcA_ENAME.DataType = System.Type.GetType("System.String")
   mdcA_ENAME.AutoIncrement = False
   mdcA_ENAME.Caption = "A.ENAME"
   mdcA_ENAME.ReadOnly = False
   mdcA_ENAME.Unique = False
   mdcA_ENAME.MaxLength = 10
   mdtDataTable.Columns.Add(mdcA_ENAME)
 
   mdcB_DNAME = New DataColumn()
   mdcB_DNAME.ColumnName = "B.DNAME"
   mdcB_DNAME.DataType = System.Type.GetType("System.String")
   mdcB_DNAME.AutoIncrement = False
   mdcB_DNAME.Caption = "B.DNAME"
   mdcB_DNAME.ReadOnly = False
   mdcB_DNAME.Unique = False
   mdcB_DNAME.MaxLength = 9
   mdtDataTable.Columns.Add(mdcB_DNAME)
 
(4) Establish Table Mapping and Set Command String:
  
   Dim mTableMap As DataTableMapping
 
   mTableMap = mdaDataAdapter.TableMappings.Add("Table", "(SCOTT.EMP")
   mTableMap.ColumnMappings.Add("A.EMPNO", "A.EMPNO")
   mTableMap.ColumnMappings.Add("A.ENAME", "A.ENAME")
   mTableMap.ColumnMappings.Add("B.DNAME", "B.DNAME")
   mdaDataAdapter.SelectCommand.Connection.ConnectionString= mstrConnection
 
(5) Define Command Object
 
   Dim cmdCommand As New OracleClient.OracleCommand()
 
   cmdCommand.Connection = mconConnection
   mconConnection.Open() 'Open the connection.
   cmdCommand.CommandType = CommandType.Text
   cmdCommand.CommandText = mstrSelect
 
(6) Create and fill the Data Adapter:
 
    Dim mdaDataAdapter As OracleClient.OracleDataAdapter
    Dim mdsDataSet As DataSet
 
   mdsDataSet = New DataSet()
   mdaDataAdapter.SelectCommand = cmdCommand
   mdaDataAdapter.Fill(mdsDataSet, "(SCOTT.EMP")
 
(7) Get Record Count: (This is where the failure occurs.)
 
   mintRecordCount = Form.BindingContext(mdsDataSet, "(SCOTT.EMP").Count
 
When this last instruction is exercised, both Oracle and DB2 exception out returning the “Cannot create a child list for field SCOTT” message. Microsoft Access and SQL do not error out.
 
So my question to all is this. What should I add to these approaches so that the ADO.Net code will not to exception out? Please offer a few lines of code.
 
I would very much appreciate your comments to this question. Thank you for reading this explanation and considering this request.
 
Sincerely,
 
Burton G. Wilkins.

Relevant Pages