none
Insert all rows using data relation and criteria RRS feed

  • Question

  • I have a Dataset with a lot of DataTables. There is no DataGrid. Four tables are parent tables and the rest have two common column names with the parent tables. I relate them as follows:

    Code Snippet

    Dim pCols As DataColumn() = {dtParent.Columns("Col1"), dtParent.Columns("Col2")}

    Dim cCols As DataColumn() = {dtChild.Columns("COL1"), dtChild.Columns("COL2")}

    Dim relation As DataRelation = New DataRelation("parent2Child", pCols, cCols)

    dsDataSet.Relations.Add(relation)

     

     

     All of the tables have multiple additional columns.  There is a tall-skinny output table in an Access database. I wish to write out one specified column plus the related Child columns and a datetime stamp from the source datatable. After that Append action I will move to the next specified column and on through the DataTable, eventually writing out all of thecolumns in all of the Child DataTables. Each Insert will carry with it some runtime variables that are available from an ADO recordset. The variables will only change for each ChildTable column. 

     

    the following are the values of the runtime variables: 'Table Name', 'Field Name', 'Label Text'

    The output table has matching data types for the Selected Columns.

     

    I can't seem to use joins in the OleDbDataAdapter I prepared for appending the data as I would with Access database tables. After reading out a few variables from my ADO recordset  and using the various DataTables got this SQL statement which fails on the Select statement at .ExecuteNonQuery():

     

    Code Snippet

    INSERT INTO tblTalSkny ( Col1, Col2, TableName, FieldName, FieldDescription, FieldValue )
    IN  'C:\_a_Testy\Test0Audit.mdb'
    SELECT ( dtChild.COL1, dtChild.COL2, 'Table Name', 'Field Name', 'Label Text', dtChild.fld1 )
    FROM dtChild

    INNER JOIN dtParent
    ON (dtChild.COL2  = dtParent.Col2)
    AND (dtChild.COL1  = dtParent.Col1)
    WHERE ((dtChild.DtTm  >#05/10/2008#));

     

     

    This is my first attempt at using data tables and data adapters, so I'm guessing I am not referring to the child DataTable columns correctly.  I marked them in red.

     

    If I can use the DataRelalation instead of the Join statement, I'd like to.

     

    Thanks in advance for any advice

    Tuesday, November 25, 2008 5:46 PM