Merging DataTables with different Columns

[SCass2010]

Hi again,

This is a problem I've been struggling with for a while...

I have an access 2007 Database where I store several different "Jobs" such as repair job, data recovery job etc.

At the min I have a form where the user can view a customer's related jobs either by type, or all jobs related to the customer. I'm trying to do this by adding each job type to it's own DataTable, then merging the DataTables into one.

Private Sub rdoShowAllJobs_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rdoShowAllJobs.CheckedChanged
        '####################### Procedure Level Constants and Variables ###########################
        ' Dim dtDataRecoveryJobs As New DataTable
        ' Dim dtRepairJobs As New DataTable
        ' Dim dtSEOJobs As New DataTable

        Dim dtDataRecoveryJobs As New DataTable
        Dim dtRepairJobs As New DataTable
        Dim dtSEOJobs As New DataTable
        '###########################################################################################

        Try

            'CreateAllJobsDataTable(dtDataRecoveryJobs)
            'CreateAllJobsDataTable(dtRepairJobs)

            ' Resets the DataSet to it's original state, i.e. empty
            Me.dtAllJobsTable.Reset()
            Me.dtAllJobsTable.GetErrors()

            ' Select required columns from tblJobsDataRecovery
            Me.daCustomerJobs = New OleDbDataAdapter("SELECT DataRecoveryJobID, CustomerID, StaffID, DateCreated, TotalCost, Quote, Completed, DateCompleted FROM tblJobsDataRecovery", connOle)
            Me.daCustomerJobs.Fill(dtDataRecoveryJobs)


            ' Select required columns from tblJobsRepair
            Me.daCustomerJobs = New OleDbDataAdapter("SELECT RepairJobID, CustomerID, StaffID, DateCreated, TotalCost, Quote, Completed, DateCompleted FROM tblJobsRepair", connOle)
            Me.daCustomerJobs.Fill(dtRepairJobs)

            ' Select required columns from tblJobsSEO
            Me.daCustomerJobs = New OleDbDataAdapter("SELECT SEOJobID, CustomerID, StaffID, DateCreated, TotalCost, Quote, Completed, DateCompleted FROM tblJobsSEO", connOle)
            Me.daCustomerJobs.Fill(dtSEOJobs)

            Me.dtAllJobsTable.Merge(dtDataRecoveryJobs, True, MissingSchemaAction.Add)
            Me.dtAllJobsTable.Merge(dtRepairJobs, True, MissingSchemaAction.Add)
            Me.dtAllJobsTable.Merge(dtSEOJobs, True, MissingSchemaAction.Add)

            ' Set up DataView
            Me.dvCustomerJob = New DataView(Me.dtAllJobsTable)
            Me.dvCustomerJob.RowFilter = "CustomerID = '" & Me.txtCustomerID.Text & "'"
            Me.dvCustomerJob.Sort = "DateCreated"

            ' Set DataGrid datasource to DataView
            Me.dgOrders.DataSource = Me.dvCustomerJob
            Me.dgOrders.Refresh()


        Catch ex As Exception
            MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
            Console.WriteLine(ex.ToString())
        Finally
            SetDataGridProperties()
        End Try
    End Sub

And this is the code I'm using to set the schema for the DataTable containing all the job details

Private Sub CreateAllJobsDataTable(ByVal aDataTable As DataTable)

        Dim dcID As DataColumn = New DataColumn("ID")
        dcID.DataType = System.Type.GetType("System.String")
        dcID.AllowDBNull = True
        dcID.Unique = False
        aDataTable.Columns.Add(dcID)

        Dim dcCustomerID As DataColumn = New DataColumn("CustID")
        dcCustomerID.DataType = System.Type.GetType("System.String")
        aDataTable.Columns.Add(dcCustomerID)

        Dim dcStaffID As DataColumn = New DataColumn("StfID")
        dcStaffID.DataType = System.Type.GetType("System.String")
        aDataTable.Columns.Add(dcStaffID)

        Dim dcDateCreated As DataColumn = New DataColumn("DateCreated")
        dcDateCreated.DataType = System.Type.GetType("System.DateTime")
        aDataTable.Columns.Add(dcDateCreated)

        Dim dcTotalCost As DataColumn = New DataColumn("TotalCost")
        dcTotalCost.DataType = System.Type.GetType("System.Double")
        aDataTable.Columns.Add(dcTotalCost)

        Dim dcQuote As DataColumn = New DataColumn("Quote")
        dcQuote.DataType = System.Type.GetType("System.Boolean")
        aDataTable.Columns.Add(dcQuote)

        Dim dcCompleted As DataColumn = New DataColumn("Completed")
        dcCompleted.DataType = System.Type.GetType("System.Boolean")
        aDataTable.Columns.Add(dcCompleted)

        Dim dcDateCompleted As DataColumn = New DataColumn("DateCompleted")
        dcDateCompleted.DataType = System.Type.GetType("System.DateTime")
        aDataTable.Columns.Add(dcDateCompleted)

    End Sub

What I want is for the first column, ID, to contain the primary key from each table, but when the code runs it adds each primary key column from the tables to the end of the AllJobs DataTable! Am I trying to do something impossible here or is there a way around this?

Again, any help is much appreciated and thank you in advance!!!

[kvprajapati]

>Merging DataTables with different Columns

Not a built-in feature. You have to use SQL-Joins that fetch data from diff. tables.